Re: query performance

"Ollie" <ollie2308_at_yahoo.com> wrote in message news:83677fa7.0406031223.7ad94ebe_at_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

and which columns would the index be on - and how do you analyze your tables? Received on Thu Jun 03 2004 - 16:02:47 CDT