Re: QUERY TUNING

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?
.

Relevant Pages

  • Re: Leftmost column in an index
    ... That's one example where the optimizer creates these _WA% statistics to ... which is the cost of a table scan. ... But this query: ... >> would likely change to a table scan or clustered index scan. ...
    (microsoft.public.sqlserver.programming)
  • Re: How ">=" Work on Varchar field?
    ... country, olddonorid, sourceid ... This Gives the Below Execution Plan: ... > increasing the cost for the total plan to above the cost for simply ... depending on the rest of the query. ...
    (microsoft.public.sqlserver.programming)
  • Re: QUERY TUNING
    ... IN THE ABOVE QUERY I HAVE A NORMAL INDEX IN THE BRAN_CODE COLUMN.. ... THIS IS THE EXPLAIN PLAN OF THAT QUERY.. ... Cost  | ... Predicate Information: ...
    (comp.databases.oracle.server)
  • Re: QUERY TUNING
    ... IN THE ABOVE QUERY I HAVE A NORMAL INDEX IN THE BRAN_CODE COLUMN.. ... Cost  | ... Otherwise the explain plan can't be used. ...
    (comp.databases.oracle.server)
  • Re: index bloat?
    ... Did you also update statistics (WITH FULLSCAN) on loan_history? ... > Was that a typo and you meant to say it sounds like a good plan? ... how can I get the query optimizer to generate it - ...
    (comp.databases.ms-sqlserver)
Loading