Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> After table analyzed query runs 8 hours longer

After table analyzed query runs 8 hours longer

From: Patrick Burns <pburns13_at_hotmail.com>
Date: 17 Sep 2004 12:59:02 -0700
Message-ID: <61a26a0d.0409171159.23c4a80@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US