Thats a bit broad as a topic.
Can you narrow it down a little?
What type of 'spool issues' are you facing (skewed processing or large spool files)?
Having said that you could try the following:
- for queries that are giving you skewed spool, check the data value distribution of column(s) named in "redistributed by hash code of **bleep**"
- for queries that are building large spool files, is the SQL sensible? Does it have appropriate selection criteria?
- for all queries that give you any kind of spool issue, check that the estimated row counts are accurate (within an order of magnitude). If they are not then the optimiser may well be producing a 'sub optimal' plan
I have two tables(T1 with 270 million rows and T2 with 3 million rows and I want to join these two tables. Primary index for both the tables is same column(transaction_id).
Thanks for that. Is the PI of each table also included in the join condition(s) of your query?
If it is not then this is possibly going to be a horrible join involving a large spool file.
Can you please provide the SQL, DDL of T1 and T2 and the explain of the query?
Also, what is the spool limit of the user running the query?