Re: Db Sequential Read
- From: Phil Singer <psinger1@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 21 Jan 2007 16:13:03 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
