Re: QUERY TUNING

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