I have a query like below.
Table_A contains summary Bill details like Bill id, account number and account name.
Table_B contains detailed Bill break up, Bill_id, Product, Charge for the product.
Now when i am joining like below, i am getting No more spool space in my user.
There is nothing unusual in the plan of the query. I checked with the DBA, as per him this join is getting skewed due to 1:N nature.
But as can be seen below, both table data are relevant. So please guide what approach I should follow now.
SELECT A.BILL_ID, A.ACCT_ID, A.ACCT_NAME, B.PRODUCT_ID, B.PRODUCT_CHARGE_AMOUNT FROM TABLE_A A INNER JOIN TABLE_B B ON A.BILL_ID=B.BILL_ID;
The truest and easiest solution is for the DBA to increase your spool.
You could also try to see which Bills are causing the skew with a query such as:
select top 100 count(*), BILL_ID from TABLE_B group by 1 order by 1 desc
If the 100 busiest records don't show that much variation then it may not be a skew issue, but if the top 10 or so are large numbers, you could try excluding those from the original query and see if that works.
Are Table_A and Table_B real tables, or views ? (with joins ?)
What are the PI's of the tables ? (SHOW TABLE)
You have no filter conditions in your query, hence you are joining the whole tables. Typically an end user will not have enough spool to query whole detail production tables.