QUERY TUNING
Date: Thu, 18 Dec 2008 02:10:24 -0800 (PST)
Message-ID: <45320feb-db12-4497-bb6b-9b2f60cdc84b@a12g2000pro.googlegroups.com>
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
Received on Thu Dec 18 2008 - 04:10:24 CST