Re: QUERY TUNING

On Dec 18, 4:29 pm, Helma <helma.vi..._at_hotmail.com> wrote:
> 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?- Hide quoted text -
>
> - Show quoted text -

with normal index=30minutes

with function based=more than 1 hr

stats are up to date.. and its a 9i db Received on Thu Dec 18 2008 - 06:05:57 CST