Re: QUERY TUNING

Hi Ganesh,

Rewrite your select as join of 4 tables without exists clause. Make PRODSNAP table to be first table in explain plan. It has most selective condition. Remove DEID duplicates using distinct clause.

              Andrew Protasov

Ganesh Kini wrote in message <35338A64.3B8E_at_epnet.com>...
>Can anyone suggest an approache to tune the following query which takes
>days to complete. Delc has 2.5M records with about 1.9 having not like
>'4%'. Artlc has 10M records, Article has 7M records, ProdSnap has 100K
>records of which 8K meets the condition.
>The query selects the subject headings in a product.
>
>Thanks
>
> Select Delc.Deid from MFS.DELC
> where exists
> (select 1 from MFS.ARTLC, MFS.ARTICLE, MFS.PRODSNAP
> WHERE PRODSNAP.PRODCODE = 'A0C'
> AND PRODSNAP.CHANNELID = 'CDROM'
> AND PRODSNAP.CYCLE = TO_DATE('199701'||'01', 'YYYYMMDD')
> AND PRODSNAP.CONTENTTYPE = 'A'
> AND ARTICLE.MID = PRODSNAP.MID
> AND ARTICLE.DTFORMAT BETWEEN PRODSNAP.DTACTBEG AND
>PRODSNAP.DTACTEND
> AND ARTLC.AN = ARTICLE.AN
> AND (article.UPDATETYPE IS NULL OR article.updatetype != 'D')
> AND ARTLC.DEID = DELC.DEID
> )
> and Delc.TagNo NOT like '4%';
Received on Wed Apr 15 1998 - 20:12:29 CDT