Re: Db Sequential Read

walid alkaakati wrote:
> Hi ,
>
> I need your help on tunning this query :
>
> SELECT a.ARTICLE_CODE, b.BRAND, DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(a.INVOICE_DATE,'YY'),'RR')||'01','RRMM')), SUM(FG_SALES_VD.PC_AMT)
> FROM FG_ARTICLE_VD b, FG_SALES_VD a
> WHERE ( ( b.ARTICLE_CODE = a.ARTICLE_CODE AND b.COMP_CODE = a.COMP_CODE ) )
> GROUP BY a.ARTICLE_CODE,a.BRAND, DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(a.INVOICE_DATE,'YY'),'RR')||'01','RRMM'));
>
> Explain plan:
>
> SELECT STATEMENT
> SORT GROUP BY
> HASH JOIN
> VIEW FG_SALES_VD
> SORT GROUP BY
> HASH JOIN
> TABLE ACCESS FULL FGINV
> TABLE ACCESS FULL FGLIINV
> INDEX FAST FULL SCAN FGART_IDX_SEC
>
> The query is taking about 3 minutes when I join article view to the sales view, i did a trace and found that the following lines :

It may just be that I've had some bad luck, but with 8i and 9i, whenever I've had a really slow query like this, it has turned out that the CBO has chosen a hash join where a nested loops join would have been the better choice. Which brings up the question: what do you have OPTIMIZER_INDEX_CACHING set to? If it is left at the default level of 0, that may explain everything.

-- 
Phil Singer                         |   psinger1 at chartermi dot net
PhD, OCP, and All Around Good Guy   |   Do the Obvious to Reply
--
http://www.freelists.org/webpage/oracle-l