Slightly improve performance using index_asc or index_desc hints

You can use index_asc and index_desc hints if you want to instruct optimizer to explicitly choose index scan for specified table. If you use index_asc hint Oracle scans index entries in ascending order. If you use index_desc Oracle will scan index entries in descending order.

I'll show you in my simple example how to slightly improve performance using one of this hints.

For my example I will create "big_table" using Tom Kyte's script as usual.




Creating big_table:

create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;

declare
l_cnt number;
l_rows number := 1000000;
begin
insert /*+ append*/
into big_table
select rownum, a.*
from all_objects a
where rownum <= 1000000;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)
loop
insert /*+APPEND */ into big_table
select rownum+l_cnt,
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID, DATA_OBJECT_ID,OBJECT_TYPE,
CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
from big_table
where rownum <= l_rows - l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

I will create index on object_id column and gather statistics.

create index i_objectid on big_table(object_id);

begin
dbms_stats.gather_table_stats
( ownname    => user,
tabname    => 'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade    => TRUE );
end;
/

It important to mention that Oracle uses index_asc by default so I'll concentrate my example on index_desc hint. Suppose I have a query that scans table for specified range and prints result in descending order.

select  * from big_table t
where object_id between 30000 and 60000
order by object_id desc;

First I will use index_asc hint to gther data. I've flushed buffer for testing purpose.

msutic@TEST11> set timing on
msutic@TEST11> set autot traceonly
msutic@TEST11>
msutic@TEST11>
msutic@TEST11> select /*+ index_asc(t i_objectid) */ * from big_table t
2  where object_id between 30000 and 60000
3  order by object_id desc
4  /

429740 rows selected.

Elapsed: 00:01:35.59

Execution Plan
----------------------------------------------------------
Plan hash value: 4044193925

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   431K|    43M|       |   441K  (1)| 00:26:34 |
|   1 |  SORT ORDER BY               |            |   431K|    43M|    60M|   441K  (1)| 00:26:34 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE  |   431K|    43M|       |   432K  (1)| 00:26:01 |
|*  3 |    INDEX RANGE SCAN          | I_OBJECTID |   431K|       |       |   972   (2)| 00:00:04 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID">=30000 AND "OBJECT_ID"<=60000)


Statistics
----------------------------------------------------------
763  recursive calls
11  db block gets
430823  consistent gets
19018  physical reads
0  redo size
5740226  bytes sent via SQL*Net to client
9826  bytes received via SQL*Net from client
861  SQL*Net roundtrips to/from client
10  sorts (memory)
1  sorts (disk)
429740  rows processed

It lasted 1min and 35.59 secs. Notice "SORT ORDER BY" - as resultset was not already sored Oracle used explicit sort to sort output by descending order and that took some time. But what will happen when I use index_desc hint to get resultset sorted in descending order.

msutic@TEST11> select /*+ index_desc(t i_objectid) */ * from big_table t
2  where object_id between 30000 and 60000
3  order by object_id desc
4  /

429740 rows selected.

Elapsed: 00:01:12.21

Execution Plan
----------------------------------------------------------
Plan hash value: 2071862395

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   431K|    43M|   432K  (1)| 00:26:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | BIG_TABLE  |   431K|    43M|   432K  (1)| 00:26:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| I_OBJECTID |   431K|       |   972   (2)| 00:00:04 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=30000 AND "OBJECT_ID"<=60000)


Statistics
----------------------------------------------------------
707  recursive calls
0  db block gets
431681  consistent gets
11957  physical reads
0  redo size
5740226  bytes sent via SQL*Net to client
9826  bytes received via SQL*Net from client
861  SQL*Net roundtrips to/from client
10  sorts (memory)
0  sorts (disk)
429740  rows processed

This execution lasted 1 min and 12.21 secs whitch is slightly faster then execution before. As you can see, in this example I don't have SORT ORDER BY explicit sort because the resultset was already sorted. Query took advantage of descending index scan to get resultset in descending order. This was just simple example to illustrate how to gain performance benefit using index hints. In many cases it is useless to use this hints but still i made demo case just to try out simple scenario. To clean up:

msutic@TEST11> drop table big_table purge;

Table dropped.