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.

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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.

1
2
3
4
5
6
7
8
9
10
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.

1
2
3
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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
where object_id between 30000 and 60000
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
msutic@TEST11> select /*+ index_desc(t i_objectid) */ * from big_table t
where object_id between 30000 and 60000
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.

 

Advertisements

Table Fragmentation on Oracle

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

  • How to find table fragmentation?
SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

  • Table size (with fragmentation):
SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME       size
-------------- ---------
BIG1             72952kb
  • Actual data in table: 
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME        size
-------------- -------------
BIG1              30604.2kb

Note = 72952 – 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) – so, the table has 50% extra space which is wasted because there is no data.

  • How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

  • Option: 1 “alter table … move + rebuild indexes”
SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS     INDEX_NAME
--------  ------------
UNUSABLE   BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS    INDEX_NAME
-------- ------------
VALID      BIGIDX

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME   size
----------- -----------
BIG1         38224kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME   size
----------- ------------
BIG1         30727.37kb


  • Option: 2 “Create table as select”
SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME    size
------------ --------- 
BIG1          85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME   size
----------- ----------
BIG1         68986.97kb

SQL> select status from user_indexes
2 where table_name = 'BIG1';

no rows selected

SQL> --Note we need to create all indexes.

  • Option: 3 “export / truncate / import”
SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME   size
----------- ---------
BIG1         85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME    size
----------- -------------
BIG1          42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1

Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table BIG1 468904 rows exported
Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> truncate table big1;

Table truncated.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y

Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "BIG1" 468904 rows imported
Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME    size
----------- -----------
BIG1          85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME    size
----------  --------------
BIG1          42535.54kb

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME   size
----------  ---------
BIG1         51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME    size
----------- ------------
BIG1         42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-
> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.


  • Option: 4 “dbms_redefinition”
SQL> create table TABLE1 (
2 no number,
3 name varchar2(20) default 'NONE',
4 ddate date default SYSDATE);

Table created.

SQL> alter table table1 add constraint pk_no primary key(no);

Table altered.

SQL> begin
2 for x in 1..100000 loop
3 insert into table1 ( no , name, ddate)
4 values ( x , default, default);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_table1
2 after insert on table1
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> select count(*) from table1;

COUNT(*)
----------
100000

SQL> delete table1 where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME   size
---------- ---------
TABLE1       2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME    size
----------- ------------
TABLE1        822.69kb

SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg
SQL> --First check table is condidate for redefinition.
SQL>
SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-
> 'TABLE1',-
> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually crea
te an empty interim table (in the same schema as the table to be redefined)
SQL>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original tab
le.
SQL>
SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2
2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_table2
2 after insert on table2
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> --Disable foreign key on original table if exists before finish this proces
s.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME    size
----------- ---------
TABLE1        1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME    size
----------- ----------
TABLE1        841.4kb

SQL> select status,constraint_name
2 from user_constraints
3 where table_name = 'TABLE1';

STATUS     CONSTRAINT_NAME
-------- ------------------
ENABLED    PK_NO1

SQL> select status ,trigger_name
2 from user_triggers
3 where table_name = 'TABLE1';

STATUS    TRIGGER_NAME
-------- --------------
ENABLED   TRI_TABLE2

SQL> drop table TABLE2 PURGE;

Table dropped.

 

Reverse key Index on Oracle

Oracle introduced reverse key index to reduce the block contention(buffer busy waits) in index segment on 2009. Normal index stores the index column value and row-id in index segment. But reverse key index stores the reverse index column value and row-id. Of course, oracle will not reverse the row-id. So we call this as reverse key index.

Let us say, the index column value is 1234. It will be converted as 4321 and stores in index segment. 1235 will be converted as 5321 and stored in index segment. In reverse key index, oracle will not store the actual column values in sequentail order. Instead, it reverse the index column value and will be spreaded across many index blocks. This would avoid index block contention.

Reverse key index would be useful when index column is populating from sequence and concurrent session inserting the data on the table.

What is ideal place to use Reverse key index?

1. In a single instance system, multiple sessions are trying to insert/update the index column at the same time, the index column value is extracting from sequence. Also we are deleting the rows and we are not doing any range scan on reverse key index column. This would be ideal place for reverse key index.

2. In RAC environment, if we have column populated by an increasing numbers, concurrent session inserting the rows from different RAC instance, the index block will have contention between nodes. The data are deleted time to time according to some rules which leave some old data undeleted in the table. Also there is not much range scan on this table. This would be ideal place for reverse key index.

If we use regular index for above two scenario, during the concurrent insert, oracle stores the index values in the sequence order and it would end up storing multiple sequential index values in the same block. This would lead into block contention when multiple insert happens at the same time with in the same block. Another thing, when we delete the old rows, the block will not be moved to free list until all rows are deleted in that block.

Reverse key index will resolve the above said issues.

When we use reverse key index, the empty space(empty space would happen when we delete old rows in the table) in the block will be refilled. Because, reverse key index stores the column value in reverse order. So column value will not be stored in sequential order.You will be able to use the empty space for different values with reverse key indexes.

When we use reverse key index, index block contention will be reduced. The reverse key index will scatter the entries accross different blocks during inserting and your many concurrent sessions will not have index block contention issues.

How do we create reverse key index?

scott@orcl> create index idx_rev
2 on employee(empno) reverse;

Index created.

scott@orcl>

How do we covert regular index to reverse key index?

scott@orcl> create index idx_rev on employee(empno);

Index created.

scott@orcl> alter index idx_rev rebuild reverse;

Index altered.

scott@orcl>

How do we covert reverse key index to regular index?

scott@ordb> alter index idx_rev rebuild noreverse;

Index altered.

scott@ordb>

Restriction on Reverse key index?

Reverse key index does not support range scan. Since the index column values are not stored in the sequential order.