4 Replies Latest reply: Jan 29, 2010 6:42 PM by Rajesh Chamarthi RSS

    Create index in ASC or DESC order

    626620
      I recently learnt that create index idx on eprson(name desc) will create a function based index. I think a DESC index can be used in ORDER BY DESC. I am wondering what other use can a DESC index be?
        • 1. Re: Create index in ASC or DESC order
          Rajesh Chamarthi
          From the Oracle Documentation :

          >
          Oracle Database treats descending indexes as if they were function-based indexes. As with other function-based indexes, the database does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement.
          >

          Descending indexes can be used if your sort order in your SQL specifies mixed sort of the columns.

          For a query which is like...
          select object_name, object_type
            from all_objects
            where object_name between 'T' and 'Z'
            order by object_name desc, object_type asc
          If you have an index on (object_name,object_type), even if it is used, there will be an additional sort after the first column is index range scanned.

          However, if the index itself is created on (object_name desc,object_type asc), the entire sort can be done in one single index range scan and does not need any additional sorting.

          So whether to create a ascending or descending index would depend on the kind of queries that you want to answer using the index.
          • 2. Re: Create index in ASC or DESC order
            626620
            Thank you Rajesh. You reply is just what I was looking for. I noted the paragraph you cited from Oracle Doc, but the setense "the database does not use descending indexes until you first analyze the index and the table on which the index is defined" does not explain whether the index will be used in indes seek or sorting.

            A further question, giving index (object_name desc,object_type asc), would it get used in the query
             select *
              from all_objects
              where object_name between 'T' and 'Z'
              order by object_name desc, object_type asc
            • 3. Re: Create index in ASC or DESC order
              Anurag Tibrewal
              user623617 wrote:
              Thank you Rajesh. You reply is just what I was looking for. I noted the paragraph you cited from Oracle Doc, but the setense "the database does not use descending indexes until you first analyze the index and the table on which the index is defined" does not explain whether the index will be used in indes seek or sorting.
              It would be used only if you analyze the index and table.
              A further question, giving index (object_name desc,object_type asc), would it get used in the query
               select *
              from all_objects
              where object_name between 'T' and 'Z'
              order by object_name desc, object_type asc
              Why dont you test it using explain plan.
              There are many factors that is taken into account by optimizer to choose index or not.

              1)
              Do a full table scan(As selecting from index and table is costly because number of data between T and Z is too high)
              and sort

              2) use index and then select from table. Because number of row is less.

              And many more factors.

              Regards
              Anurag.
              • 4. Re: Create index in ASC or DESC order
                Rajesh Chamarthi
                >
                the database does not use descending indexes until you first analyze the index and the table on which the index is defined
                >

                Whether the index will be used or not would depend primarily on the cost Oracle calculates for the query.

                But for the cost calculations to be accurate , you need to give the optimizer the correct statistics of the table and the index.

                Without them, all the plans generated will be incorrect as the opttimizer would make incorrect predictions about the data. This is true for all indexes -- not just for desc indexes.

                The performance benifit of creating the index as I mentioned in the above post is that an additional sort would be avoided after the index range scan is done to get the rows;

                Check this example.
                sql> create table t_objects as
                  2  select object_name, object_id, created, owner
                  3    from all_objects;
                
                Table created.
                
                sql> select count(*) from t_objects;
                
                  COUNT(*)
                ----------
                     40932
                
                sql> create index t_idx_1 on t_objects (object_name, owner);   ---the usual index.
                
                Index created.
                
                sql> analyze table t_objects
                  2    compute statistics
                  3    for all columns
                  4    for all indexes
                  5  /
                
                Table analyzed.
                
                --- If you use either asc.. or desc for both the columns..
                
                sql> set autotrace traceonly explain;
                
                sql> select * from t_objects
                  2     where object_name between 'Y' and 'Z'
                  3      order by object_name asc, owner asc;
                
                Execution Plan
                ----------------------------------------------------------
                   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=57 Card=140 Bytes=
                          5600)
                
                   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_OBJECTS' (TABLE) (Cost
                          =57 Card=140 Bytes=5600)
                
                   2    1     INDEX (RANGE SCAN) OF 'T_IDX_1' (INDEX) (Cost=2 Card=140
                          )
                
                sql> select * from t_objects
                  2     where object_name between 'Y' and 'Z'
                  3      order by object_name desc, owner desc;
                
                Execution Plan
                ----------------------------------------------------------
                   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=57 Card=140 Bytes=
                          5600)
                
                   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_OBJECTS' (TABLE) (Cost
                          =57 Card=140 Bytes=5600)
                
                   2    1     INDEX (RANGE SCAN DESCENDING) OF 'T_IDX_1' (INDEX) (Cost
                          =2 Card=140)
                
                The same index is used and it is range scanned... we dont need the desc caluse.
                
                However, if the sort order involves some columns in asc and some in desc,
                
                sql> select * from t_objects
                  2     where object_name between 'Y' and 'Z'
                  3        order by object_name asc, owner desc;
                
                Execution Plan
                ----------------------------------------------------------
                   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=58 Card=140 Bytes=
                          5600)
                
                   1    0   SORT (ORDER BY) (Cost=58 Card=140 Bytes=5600)
                   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_OBJECTS' (TABLE) (Co
                          st=57 Card=140 Bytes=5600)
                
                   3    2       INDEX (RANGE SCAN) OF 'T_IDX_1' (INDEX) (Cost=2 Card=1
                          40)
                As you can see from the plan above,though the index is range scanned and we get the values sorted by one of the columns (let's say obejct_name asc), Oracle will need to sort the values again to get the "owner" column in descending order.

                If we build an index as below, the adiitional sort is avoidable as the rows are sorted in the required way already in the index.
                sql> select * from t_objects 
                  2    where object_name between 'Y' and 'Z'
                  3     order by OBJECT_NAME asc, owner desc;
                
                Execution Plan
                ----------------------------------------------------------
                   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=5 Bytes=200
                          )
                
                   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_OBJECTS' (TABLE) (Cost
                          =4 Card=5 Bytes=200)
                
                   2    1     INDEX (RANGE SCAN) OF 'T_IDX_2' (INDEX) (Cost=2 Card=5)
                So they are useful in a very particular scenario for a particular type of queries.

                It is better to try out different cases and see what is the best approach for your scenario based on the queries that you are trying to answer.

                Please note that the above examples use "between 'Y' and 'Z' "as that query returns a very small number of rows in my database and hence the index is being used. You might have to alter the condition based on the data in your database to get the optimizer to use the index.