| 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_CR
-----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
Received on Fri Sep 17 2004 - 14:59:02 CDT
![]() |
![]() |