Monster sql statement need optimization

From: Gene Hubert <gwhubert_at_hotmail.com>
Date: 23 May 2002 19:41:51 -0700
Message-ID: <7e3fa619.0205231841.680b4e03@posting.google.com>

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

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 (Cost=43124 Card=50793 Byt     es=7618950)

   1 0 HASH JOIN* (Cost=43124 Card=50793 Bytes=7618950)
:Q229201

                                   2

   2    1     TABLE ACCESS* (FULL) OF 'FR_SALESREP_S006' (Cost=40 Card


:Q229200

    =18566 Bytes=334188)                      2

   3    1     HASH JOIN* (Cost=43081 Card=50793 Bytes=6704676)        


:Q229201

                                   1

   4 3 TABLE ACCESS* (FULL) OF 'FR_SALESREP' (Cost=14 Card=15
:Q229200

    914 Bytes=206882)                  9

   5    3  HASH JOIN* (Cost=43065 Card=50793 Bytes=6044367)      


:Q229201

                                   0

   6 5 TABLE ACCESS* (FULL) OF 'FR_PROD_XREF' (Cost=1 Card=
:Q229200

    1022 Bytes=10220)                  7

   7    5    FILTER*                   :Q229201
                                   0

   8    7      HASH JOIN* (OUTER)                   :Q229200
                                   8

   9    8        HASH JOIN* (OUTER) (Cost=42162 Card=16207 Bytes=


:Q229200

    1280353)                           6

  10    9       HASH JOIN* (Cost=40869 Card=16207 Bytes=110207


:Q229200

    6)                             4

  11   10         INDEX* (FAST FULL SCAN) OF 'FR_EVNT_I2' (UNI


:Q229200

    QUE) (Cost=25252 Card=6229 Bytes=305221)         0

  12   10         TABLE ACCESS* (FULL) OF 'FR_EVNT_SAMP' (Cost


:Q229200

    =15563 Card=46347264 Bytes=880598016)            3

  13    9       TABLE ACCESS* (FULL) OF 'FR_HCP' (Cost=1285 Ca


:Q229200

    rd=2161125 Bytes=23772375)                 5

  14    8        VIEW* OF 'FR_RTIC_TERR_HIST_V1' (Cost=895 Card=1


:Q229200

    47272 Bytes=4418160)                      1

  15   14       SORT (GROUP BY) (Cost=895 Card=147272 Bytes=30

    92712)

  16 15 TABLE ACCESS (FULL) OF 'FR_RTIC_TERR_HIST' (     Cost=214 Card=147272 Bytes=3092712) Received on Thu May 23 2002 - 21:41:51 CDT