Re: QUERY TUNING

From: Helma <helma.vinke_at_hotmail.com>
Date: Thu, 18 Dec 2008 03:29:33 -0800 (PST)
Message-ID: <1c6201f7-9941-43f9-b247-44dc0aa9b275@r10g2000prf.googlegroups.com>


On Dec 18, 12:04 pm, sybrandb <sybra..._at_gmail.com> wrote:
> On 18 dec, 11:10, mehraj hussain <mhdmeh..._at_gmail.com> wrote:
>
>
>
>
>
> >  MY QUERY:
>
> > select bran_code,sum(ps_tran_qty) cls_qty
> > from bs_brnd_smr where substr(bran_code,3,1) !='D' group by bran_code;
>
> > -----------------------------------
> > IN THE ABOVE QUERY I HAVE A NORMAL INDEX IN THE BRAN_CODE COLUMN..
> > THIS IS THE EXPLAIN PLAN OF THAT QUERY..
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------­­-----
> > ---------------------------------------------------------------------------­­--
> > | Id  | Operation                    |  Name        | Rows  | Bytes |
> > Cost  |
> > ---------------------------------------------------------------------------­­--
> > |   0 | SELECT STATEMENT             |              |    45 |   405
> > |    66 |
> > |   1 |  SORT GROUP BY NOSORT        |              |    45 |   405
> > |    66 |
> > |   2 |   TABLE ACCESS BY INDEX ROWID| BS_BRND_SMR  |   299K|
> > 2630K|    66 |
> > |*  3 |    INDEX FULL SCAN           | BSL7         |   299K|
> > |    26 |
> > ---------------------------------------------------------------------------­­--
>
> > Predicate Information (identified by operation id):
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------­­-----
>
> > ---------------------------------------------------
>
> >    3 - filter(SUBSTR("BS_BRND_SMR"."BRAN_CODE",3,1)<>'D')
>
> > Note: cpu costing is off
>
> > I GOT A COMPLAIN THAT ITS TAKING VERY LONG TIME ..
> > THEN AFTER CHECKING IT , I PLANNED TO CREATE A FUNTION BASED INDEX (MY
> > bran_code COLUMN DOSNT HAVE ANY NULL VALUES) .
>
> > AFTER THAT I CHECK THE PLAN TABLE :
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------­­-----
> > ---------------------------------------------------------------------------­­--
> > | Id  | Operation                    |  Name        | Rows  | Bytes |
> > Cost  |
> > ---------------------------------------------------------------------------­­--
> > |   0 | SELECT STATEMENT             |              |    45 |   405
> > |   760 |
> > |   1 |  SORT GROUP BY NOSORT        |              |    45 |   405
> > |   760 |
> > |   2 |   TABLE ACCESS BY INDEX ROWID| BS_BRND_SMR  |  5489K|
> > 47M|   760 |
> > |*  3 |    INDEX FULL SCAN           | BSL7         |  5489K|
> > |    26 |
> > ---------------------------------------------------------------------------­­--
>
> > Predicate Information (identified by operation id):
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------­­-----
>
> > ---------------------------------------------------
>
> >    3 - filter(SUBSTR("BS_BRND_SMR"."BRAN_CODE",3,1)<>'D')
>
> > Note: cpu costing is off
>
> > ---------------------------------------------------------------------------­­------------------------------------
>
> > PLZ SUGGEST ME FOR THE IMPROVEMENT..
>
> > REGARDS,
> > Mohd Mehraj Hussain
>
> First of all, I suggest you stop typing your text in upper case.
> It is SHOUTING.
> Secondly, all posts should include a 4 digit version number.
> Most questions have version specific answers.
> While you can not be bothered to consult the documentation for your
> version, I can not be bothered to consult the documentation for 9iR2,
> 10gR1, 10gR2, or 11gR1.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

It seems a oracle 9 database to me, because there the system statistics are not ran by default. ( hence the Note: cpu costing is off)
So my first suggestion is to run those. And, are the statistics accurate? Otherwise the explain plan can't be used. First update your table and index statistics if needed. What is your tested runtime? Received on Thu Dec 18 2008 - 05:29:33 CST

Original text of this message