In my query, I am using set operator(minus) between two tables say A & B.
Select ...... 33 columns..... from A
select ...... 33 columns..... from B
To enhance the performance of the query, is it recommended to collect statistics on all 33 columns of A & B tables since collecting statistics is resource consuming activity, i want to make sure that collecting statistics should not become dis-advantage here..
no statistics will change the plan for this query, so you don't need any statistic at all.
When performance is bad it might be due large spool usage.
You might try MINUS ALL instead (if there are no duplicate rows in A).
I am taking a join on a index column & its taking time to execute the following query
A.ACCOUNT_NUM AS ACCOUNT_ID,
FROM UT_TAB.ACCOUNT_CREDIT_LIMIT AS A
INNER JOIN UT_TAB.LIMIT_DETAILS AS B
A.ACCT_LIMIT_REF_ID = B.LIMIT_REF_ID
INNER JOIN UT_TAB.COLLATERAL_ITEM AS C
B.Limit_Linked_CollateraL = C.Collateral_Item_Categ_Cd
C.Collateral_Item_Categ_Cd is the Secondary index column is there any solution to get the result faster, explain is giving me
Are the estimated numbers close to the actual ones?
How many nodes does your system have?
What statistics exist? -> HELP STATS tablename
What are the PIs of your tables?