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