Re: QUERY TUNING
- From: mehraj hussain <mhdmehraj@xxxxxxxxx>
- Date: Thu, 18 Dec 2008 04:05:57 -0800 (PST)
On Dec 18, 4:29 pm, Helma <helma.vi...@xxxxxxxxxxx> wrote:
On Dec 18, 12:04 pm, sybrandb <sybra...@xxxxxxxxx> wrote:
On 18 dec, 11:10, mehraj hussain <mhdmeh...@xxxxxxxxx> 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
.
- Follow-Ups:
- Re: QUERY TUNING
- From: Helma
- Re: QUERY TUNING
- References:
- Re: QUERY TUNING
- From: sybrandb
- Re: QUERY TUNING
- From: Helma
- Re: QUERY TUNING
- Prev by Date: Re: asm on san
- Next by Date: Re: QUERY TUNING
- Previous by thread: Re: QUERY TUNING
- Next by thread: Re: QUERY TUNING
- Index(es):
Relevant Pages
|
