Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> After table analyzed query runs 8 hours longer
All of our database tables are analyzed with the exception of 1. The
queries run in Choose mode and when we analyze that one table a query
goes from 1.5 hours to 9.5 hours. All of the other queries run faster
but this one. I have tried creating all kinds of indexes with no
success.
----the table in question is analyzed here (MF_AJ - 40 millon
rows)-----
SELECT STATEMENT Cost=258592081 SORT UNIQUE UNION-ALL SORT GROUP BY HASH JOIN TABLE ACCESS FULL MF_SYS_STGS NESTED LOOPS HASH JOIN INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN TABLE ACCESS FULL MF_AJ TABLE ACCESS BY INDEX ROWID MF_TJ INDEX UNIQUE SCAN PK_TJ SORT GROUP BY HASH JOIN HASH JOIN TABLE ACCESS FULL MF_SYS_STGS NESTED LOOPS HASH JOIN INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN TABLE ACCESS FULL MF_AJ TABLE ACCESS BY INDEX ROWID MF_TJ INDEX UNIQUE SCAN PK_TJ VIEW VW_NSO_1 SORT UNIQUE HASH JOIN TABLE ACCESS FULL MF_CR_LN TABLE ACCESS FULL MF_CRReceived on Fri Sep 17 2004 - 14:59:02 CDT
-----table not analyzed------------
SELECT STATEMENT Cost=335577 SORT UNIQUE UNION-ALL SORT GROUP BY NESTED LOOPS HASH JOIN INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN NESTED LOOPS TABLE ACCESS FULL MF_AJ TABLE ACCESS BY INDEX ROWID MF_TJ INDEX UNIQUE SCAN PK_TJ TABLE ACCESS FULL MF_SYS_STGS SORT GROUP BY HASH JOIN TABLE ACCESS FULL MF_SYS_STGS NESTED LOOPS HASH JOIN INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN HASH JOIN TABLE ACCESS FULL MF_AJ TABLE ACCESS FULL MF_TJ VIEW VW_NSO_1 SORT UNIQUE HASH JOIN TABLE ACCESS FULL MF_CR_LN TABLE ACCESS FULL MF_CR