Re: Monster sql statement need optimization

On 23 May 2002, gwhubert_at_hotmail.com wrote:
> Well, I thought I could handle anything on optimizing sql statements
> but have been proven wrong with this one. Any help would be much
> appreciated. This is Oracle8i Enterprise Edition Release 8.1.7.2.0 -
> Production on SunOS 5.6.
>
> I've tried a bunch of stuff with indexes and hints but nothing seems
> to help. Partitioning is not an option due to business restrictions.
> I think the full table scan on fr_evnt_samp is killing me but haven't
> been able to eliminate it. There is no unique key between fr_evnt and
> fr_evnt_samp.
>
> I know this is a lot to slog through but would much appreciate any
> help offered.
>
> Thanks Much,
> Gene Hubert
> Durham, NC
>
> Here's the sql:
> (run by a proC program with a 6 month date range returning 6 million
> records)
> SELECT
> to_char(a.evnt_date,'YYYYMM'),
> to_char(a.evnt_date,'YYYYMMDD'),
> b.brnd_fm_strn_pk_id,
> nvl(rtrim(a.me_num,' '),'UNKNOWNPRSC'),
> nvl(rtrim(a.zip,' '),'NOZIP'),
> nvl(g.employee_id,'NOWORKERSID'),
> nvl(rtrim(d.salesrep_num,' '),'UNKNOWNREPNO'),
> nvl(rtrim(b.pass_prod_id,' '),'NOID'),
> nvl(rtrim(f.selling_face,' '),'UNKNOWN'),
> nvl(rtrim(e.ndc_cd,' '),'UNKNOWNNDCD'),
> nvl(rtrim(c.degree,' '),'XXXX'),
> nvl(a.terr_num,'UNKNOWNTERR'),
> nvl(b.qty,0)
> from fr_Evnt a,fr_Evnt_Samp b,fr_Hcp c,
> fr_Salesrep d, fr_prod_xref e, fr_rtic_terr_hist_v1 f,
> fr_salesrep_s006 g
> where
> a.evnt_id = b.evnt_id
> and a.hcp_id = c.hcp_id(+)
> and a.user_id = d.user_id
> and a.terr_num = f.territory_number (+)
> and b.pass_prod_id = e.pass_prod_id
> and d.salesrep_num = g.rep_number
> and a.evnt_date between f.start_date and f.end_date
> and a.evnt_date between to_date(:startDate,'YYYYMMDD')
> and to_date(:endDate,'YYYYMMDD');

> Record counts: by table alias:
> a=45 million,b=45 million,c=5 million,d=16000,e=1000,f=147000,g=16000

How restrictive can the "a.event_date between :startDate and :endDate" be? If this could wittle down the number of "a" rows from 45 million to, say, .3 or .4 million, then it might make alot more sense to try and get the join to "b" to use a nested loops on the FR_EVNT_SAMP index instead of scanning b entirely and hash joining to a. You could then use the same logic against the rest of the tables. Maybe the actual "a" definitely seems to be the main table here. It seems sooo important that they created an index to include all of the selection clauses to allow a FFS (looks like for this query alone, but maybe its just coincidence)

I also agree with Herman. The outer join of a to f seems fishy when you aren't outer joining a to f on the start and end dates.

> Here're the indexes:
> select substr(a.index_name,1,12),substr(a.table_name,1,12)
> ,substr(column_name,1,12),column_position,b.uniqueness
> from user_ind_columns a,user_indexes b
> where a.index_name=b.index_name
> order by 1,4;
>
> SUBSTR(A.IND SUBSTR(A.TAB SUBSTR(COLUM COLUMN_POSITION UNIQUENES
> ------------ ------------ ------------ --------------- ---------
> FR_EVNT_I1 FR_EVNT EVNT_ID 1 UNIQUE
> FR_EVNT_I2 FR_EVNT EVNT_ID 1 UNIQUE
> FR_EVNT_I2 FR_EVNT EVNT_DATE 2 UNIQUE
> FR_EVNT_I2 FR_EVNT LOC_ID 3 UNIQUE
> FR_EVNT_I2 FR_EVNT HCP_ID 4 UNIQUE
> FR_EVNT_I2 FR_EVNT USER_ID 5 UNIQUE
> FR_EVNT_I2 FR_EVNT TERR_NUM 6 UNIQUE
> FR_EVNT_I2 FR_EVNT ME_NUM 7 UNIQUE
> FR_EVNT_I2 FR_EVNT ZIP 8 UNIQUE
> FR_EVNT_SAMP_I1 FR_EVNT_SAMP EVNT_ID 1 NONUNIQUE
> FR_HCP_I1 FR_HCP HCP_ID 1 UNIQUE
> FR_LOC_I1 FR_LOC LOC_ID 1 UNIQUE
> FR_PROD_XREF_I1 FR_PROD_XREF PASS_PROD_ID 1 UNIQUE
> FR_RTIC_TERR_HIST_I1 FR_RTIC_TERR_HIS TERRITORY_NUMBER 1 NONUNIQUE
> FR_SALESREP_I1 FR_SALESREP USER_ID 1 UNIQUE
> FR_SALESREP_S006_I1 FR_SALESREP_S006 REP_NUMBER 1 UNIQUE
>
> Here's the plan:
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 HASH JOIN*
> 2 1 TABLE ACCESS* (FULL) OF 'FR_SALESREP_S006'
> 3 1 HASH JOIN*
> 4 3 TABLE ACCESS* (FULL) OF 'FR_SALESREP'
> 5 3 HASH JOIN*
> 6 5 TABLE ACCESS* (FULL) OF 'FR_PROD_XREF'
> 7 5 FILTER*
> 8 7 HASH JOIN* (OUTER)
> 9 8 HASH JOIN* (OUTER)
> 10 9 HASH JOIN*
> 11 10 INDEX* (FAST FULL SCAN) OF 'FR_EVNT_I2'
> 12 10 TABLE ACCESS* (FULL) OF 'FR_EVNT_SAMP'
> 13 9 TABLE ACCESS* (FULL) OF 'FR_HCP'
> 14 8 VIEW* OF 'FR_RTIC_TERR_HIST_V1'
> 15 14 SORT (GROUP BY)
> 16 15 TABLE ACCESS (FULL) OF 'FR_RTIC_TERR_HIST'

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.