query performance

From: Ollie <ollie2308_at_yahoo.com>
Date: 3 Jun 2004 13:23:32 -0700
Message-ID: <83677fa7.0406031223.7ad94ebe@posting.google.com>

I need some help in determing why oracle is doing full table scans on the following query. The tables in this query form a star. All tables have been analyzed and indexes are in place for RI.

Here is the query

select t1.col1, t2.col3, t3.col1, etc........ from

     ERM_Location_Dim t1, 
     ERM_Field_Sample_Dim t2,
     ERM_Sample_Method_Dim t3, 
	 ERM_Analysis_Dim t4, 
	 ERM_Analyte_Dim	t5,
	 ERM_Qualifier_dim t6,
	 ERM_Date_Dim t7,
	 ERM_Sample_Results_Fact t0 

where t1.Installation_Name = 'Installation B'

and t7.year_number  between 1995 and 1998 
and t0.erm_location_dim_key 	    = t1.erm_location_dim_key
and t0.erm_field_sample_dim_key     = t2.erm_field_sample_dim_key
and t0.erm_sample_method_dim_key    = t3.erm_sample_method_dim_key
and t0.erm_analysis_dim_key		    = t4.erm_analysis_dim_key
and t0.erm_analyte_dim_key		   	= t5.erm_analyte_dim_key
and t0.erm_qualifier_dim_key 	   	= t6.erm_qualifier_dim_key
and t0.sample_collection_begin_date = t7.erm_date_dim_key

order by upper(t5.Analyte_Name);

the explain plan that I get is as follows

Operation                               Object Name             Rows  
 Bytes   Cost    Object Node     In/Out  PStart  PStop

SELECT STATEMENT Optimizer Mode=CHOOSE                          139 K 
         46580
  SORT ORDER BY                                                 139 K 
 34 M    46580
    MERGE JOIN                                                  139 K 
 34 M    38196
      SORT JOIN                                                 139 K 
 26 M    35785
        HASH JOIN                                               139 K 
 26 M    29262
          TABLE ACCESS FULL             ERM_ANALYSIS_DIM        2 K   
 31 K    259
          HASH JOIN                                             139 K 
 24 M    26417
            TABLE ACCESS FULL           ERM_ANALYTE_DIM         5 K   
 146 K   6
            MERGE JOIN                                          139 K 
 20 M    21316
              SORT JOIN                                         139 K 
 17 M    21222
                HASH JOIN                                       139 K 
 17 M    16697
                  TABLE ACCESS FULL     ERM_LOCATION_DIM        2 K   
 147 K   65
                  HASH JOIN                                     556 K 
 33 M    7497
                    TABLE ACCESS FULL   ERM_DATE_DIM            524   
 4 K     23
                    HASH JOIN                                   1 M   
 84 M    4531
                      TABLE ACCESS FULL ERM_QUALIFIER_DIM       417   
 4 K     2
                      TABLE ACCESS FULL ERM_SAMPLE_RESULTS_FACT 1 M   
 65 M    2113
              SORT JOIN                                         16 K  
 333 K   95
                TABLE ACCESS FULL       ERM_FIELD_SAMPLE_DIM    16 K  
 333 K   19
      SORT JOIN                                                 188 K 
 10 M    2411
        TABLE ACCESS FULL       ERM_SAMPLE_METHOD_DIM           188 K 

 10 M 524

thanks for your help Received on Thu Jun 03 2004 - 15:23:32 CDT