Forum Stats

  • 3,781,146 Users
  • 2,254,484 Discussions
  • 7,879,595 Comments

Discussions

index coalesce

668822
668822 Member Posts: 901
edited Dec 19, 2008 12:33AM in General Database Discussions
hi masters.,

this is egarding index management. i am facing the problem regarding one sql query with union and order by clause.


when i remove order by clause, the query runs faster,than with order by clause.

is this the problem regarding index????

i know that indexes rarely need rebuild,thats why i am thinking of coalesce.is this the right decision??

what may be the problem else????

i am using oracle 9i. and i dont have statpack report coz the client is offshore.\

thanks and regards
VD
Tagged:

Best Answer

  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    Accepted Answer
    Vikrant,
    Index is not picked by order by clause. Index entries are already sorted. See here a demo where I have used the index in a column.If I give order by clause, I explicitly get a Sort Order By in the plan.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options
    
    SQL> create table sampletable as select * from scott.emp;
    
    Table created.
    
    SQL> create index idx on sampletable(empno);
    
    Index created.
    
    SQL> exec dbms_stats.gaher_table_stats(
    BEGIN dbms_stats.gaher_table_stats(; END;
    
                                       *
    ERROR at line 1:
    ORA-06550: line 1, column 36:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    ( ) - + case mod new not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable>
    table continue avg count current exists max min prior sql
    stddev sum variance execute multiset the both leading
    trailing forall merge year month day hour minute second
    timezone_hour timezone_minute timezone_region timezone_abbr
    time timestamp interval date
    <a string literal with character set specification>
    
    
    SQL> exec dbms_stats.gaher_table_stats('AMAN','SAMPLETABLE');
    BEGIN dbms_stats.gaher_table_stats('AMAN','SAMPLETABLE'); END;
    
                     *
    ERROR at line 1:
    ORA-06550: line 1, column 18:
    PLS-00302: component 'GAHER_TABLE_STATS' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    
    SQL> exec dbms_stats.gather_table_stats('AMAN','SAMPLETABLE');
    
    PL/SQL procedure successfully completed.
    
    SQL> set autot trace exp
    SQL> select * from sampletable order by empno;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 5644182
    
    --------------------------------------------------------------------------------
    --
    
    | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
     |
    
    --------------------------------------------------------------------------------
    --
    
    |   0 | SELECT STATEMENT   |             |    14 |   504 |     4  (25)| 00:00:01
     |
    
    |   1 |  SORT ORDER BY     |             |    14 |   504 |     4  (25)| 00:00:01
     |
    
    |   2 |   TABLE ACCESS FULL| SAMPLETABLE |    14 |   504 |     3   (0)| 00:00:01
     |
    
    --------------------------------------------------------------------------------
    --
    
    
    SQL> select * from sampletable order by empno where empno in (7369,7422);
    select * from sampletable order by empno where empno in (7369,7422)
                                             *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    
    
    SQL> select * from sampletable  where empno in (7369,7422);
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3966318501
    
    --------------------------------------------------------------------------------
    ------------
    
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)
    | Time     |
    
    --------------------------------------------------------------------------------
    ------------
    
    |   0 | SELECT STATEMENT             |             |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |   1 |  INLIST ITERATOR             |             |       |       |
    |          |
    
    |   2 |   TABLE ACCESS BY INDEX ROWID| SAMPLETABLE |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |*  3 |    INDEX RANGE SCAN          | IDX         |     2 |       |     0   (0)
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("EMPNO"=7369 OR "EMPNO"=7422)
    
    SQL> select * from sampletable  where empno in (7369,7422) order by empno;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3966318501
    
    --------------------------------------------------------------------------------
    ------------
    
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)
    | Time     |
    
    --------------------------------------------------------------------------------
    ------------
    
    |   0 | SELECT STATEMENT             |             |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |   1 |  INLIST ITERATOR             |             |       |       |
    |          |
    
    |   2 |   TABLE ACCESS BY INDEX ROWID| SAMPLETABLE |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |*  3 |    INDEX RANGE SCAN          | IDX         |     2 |       |     0   (0)
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("EMPNO"=7369 OR "EMPNO"=7422)
    
    SQL>
    The point that I was making is that it may be happening that you are doing an over sort sort of thing which is causing the query to be slow. Using index for returning rows in the ordered manner is not a good thing as Oracle always say that for a guaranteed ordered result, one must use an order by clause. So either you can choose to remove the order by clause or can choose to rewrite the query without using the Union operator.
    But these all are just guess works. Its not possible to say what's happening specifically in your case as long as you don't post the plan for your query both,with and without the use of index.
    HTH
    Aman....

Answers

  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    Vikrant,
    Union and other set operators, except Union All, do an implicit sort. So that may be the reason that when you remove an extra order by , query is working better. I don't get it that how did you link index with this scene? It would be better if you can post the explain plan of the query.
    HTH
    Aman....
  • 668822
    668822 Member Posts: 901
    but aman sir,

    index is used by an order by clause!!!! am i right. actually i want to run my query with order by clause.

    i just checked that by removing order by clause make any difference to fast query retrivel.


    does index is not in picture anywhere??? how the sorts are performed then???i have index on that column of table..

    thanks and regards
    VD
  • Jaffy
    Jaffy Member Posts: 1,234
    Hi,

    Explain plan for the query with and without index will give a clear picture.

    regards

    Jafar
  • Kok Aik
    Kok Aik Member Posts: 105
    edited Dec 19, 2008 12:33AM
    Hi

    You may create a new table, without any index and you can still do an order by. So, the "index is used by an order clause" is incorrect. In this case, oracle will do a full table scan and then sort your data.

    An order by is an additional sorting action which you need extra time for doing the work, and it will be slower.

    Rgds
    Ung

    Edited by: Kok Aik on Dec 19, 2008 1:31 PM
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    Accepted Answer
    Vikrant,
    Index is not picked by order by clause. Index entries are already sorted. See here a demo where I have used the index in a column.If I give order by clause, I explicitly get a Sort Order By in the plan.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options
    
    SQL> create table sampletable as select * from scott.emp;
    
    Table created.
    
    SQL> create index idx on sampletable(empno);
    
    Index created.
    
    SQL> exec dbms_stats.gaher_table_stats(
    BEGIN dbms_stats.gaher_table_stats(; END;
    
                                       *
    ERROR at line 1:
    ORA-06550: line 1, column 36:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    ( ) - + case mod new not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable>
    table continue avg count current exists max min prior sql
    stddev sum variance execute multiset the both leading
    trailing forall merge year month day hour minute second
    timezone_hour timezone_minute timezone_region timezone_abbr
    time timestamp interval date
    <a string literal with character set specification>
    
    
    SQL> exec dbms_stats.gaher_table_stats('AMAN','SAMPLETABLE');
    BEGIN dbms_stats.gaher_table_stats('AMAN','SAMPLETABLE'); END;
    
                     *
    ERROR at line 1:
    ORA-06550: line 1, column 18:
    PLS-00302: component 'GAHER_TABLE_STATS' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    
    SQL> exec dbms_stats.gather_table_stats('AMAN','SAMPLETABLE');
    
    PL/SQL procedure successfully completed.
    
    SQL> set autot trace exp
    SQL> select * from sampletable order by empno;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 5644182
    
    --------------------------------------------------------------------------------
    --
    
    | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
     |
    
    --------------------------------------------------------------------------------
    --
    
    |   0 | SELECT STATEMENT   |             |    14 |   504 |     4  (25)| 00:00:01
     |
    
    |   1 |  SORT ORDER BY     |             |    14 |   504 |     4  (25)| 00:00:01
     |
    
    |   2 |   TABLE ACCESS FULL| SAMPLETABLE |    14 |   504 |     3   (0)| 00:00:01
     |
    
    --------------------------------------------------------------------------------
    --
    
    
    SQL> select * from sampletable order by empno where empno in (7369,7422);
    select * from sampletable order by empno where empno in (7369,7422)
                                             *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    
    
    SQL> select * from sampletable  where empno in (7369,7422);
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3966318501
    
    --------------------------------------------------------------------------------
    ------------
    
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)
    | Time     |
    
    --------------------------------------------------------------------------------
    ------------
    
    |   0 | SELECT STATEMENT             |             |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |   1 |  INLIST ITERATOR             |             |       |       |
    |          |
    
    |   2 |   TABLE ACCESS BY INDEX ROWID| SAMPLETABLE |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |*  3 |    INDEX RANGE SCAN          | IDX         |     2 |       |     0   (0)
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("EMPNO"=7369 OR "EMPNO"=7422)
    
    SQL> select * from sampletable  where empno in (7369,7422) order by empno;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3966318501
    
    --------------------------------------------------------------------------------
    ------------
    
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)
    | Time     |
    
    --------------------------------------------------------------------------------
    ------------
    
    |   0 | SELECT STATEMENT             |             |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |   1 |  INLIST ITERATOR             |             |       |       |
    |          |
    
    |   2 |   TABLE ACCESS BY INDEX ROWID| SAMPLETABLE |     2 |    72 |     1   (0)
    | 00:00:01 |
    
    |*  3 |    INDEX RANGE SCAN          | IDX         |     2 |       |     0   (0)
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("EMPNO"=7369 OR "EMPNO"=7422)
    
    SQL>
    The point that I was making is that it may be happening that you are doing an over sort sort of thing which is causing the query to be slow. Using index for returning rows in the ordered manner is not a good thing as Oracle always say that for a guaranteed ordered result, one must use an order by clause. So either you can choose to remove the order by clause or can choose to rewrite the query without using the Union operator.
    But these all are just guess works. Its not possible to say what's happening specifically in your case as long as you don't post the plan for your query both,with and without the use of index.
    HTH
    Aman....
This discussion has been closed.