| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: QUERY TUNING
In article <35338A64.3B8E_at_epnet.com>,
Ganesh Kini <gkini_at_epnet.com> wrote:
>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%';
How do the indexes look like (which columns). Replace TO_DATE () bij a bind variable without the function call function in where clause -> full table scan. Try to prevent using 'or' in the where clause -> full table scan.
use explain plan.
hope it helps.
Ronald
 |
 |