Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Using Reverse-Key Indexes


Oracle Tips by Burleson
 

A reverse-key index prevents unbalancing of the B-tree and the resulting hot blocking, which will happen if the B-tree becomes unbalanced. Generally, unbalanced B-trees are caused by high-volume insert activity in a parallel server where the key value is only slowly changing, such as with an integer generated from a sequence or a data value. A reverse key index works by reversing the order of the bytes in the key value; of course, the rowid value is not altered, just the key value. The only way to create a reverse-key index is to use the CREATE INDEX command. An index that is not reverse-key cannot be altered or rebuilt into a reverse-key index; however, a reverse-key index can be rebuilt as a normal index.

One of the major limitations of reverse-key indexes is that they cannot be used in an index range scan, since reversing the index key value randomly distributes the blocks across the index leaf nodes. A reverse-key index can only use the fetch-by-key or full-index(table)scans methods of access. Let’s look at an example:

CREATE INDEX rpk_po ON tele_dba.po(po_num) REVERSE

TABLESPACE tele_index

STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);

The above command would reverse the values for the po_num column  as  it creates the index. This would assure random distribution of the values across the index leaf nodes. But what if we then determine that the benefits of the reverse key do not outweigh the drawbacks? We can use the ALTER command to rebuild the index as a NOREVERSE index:

ALTER INDEX rpk_po REBUILD NOREVERSE;

ALTER INDEX rpk_po RENAME TO pk_po;

Oracle manuals only discuss the benefits of the reverse-key index in the realm of Oracle Parallel Server, so note that if you experience performance problems after a bulk load of data, it may help to drop and re-create the indexes involved as reverse-key indexes, if the table will continue to be loaded in a bulk fashion.

The major drawback to a reverse-key index is that you are not able to perform range scans against them, so if your table requires the use of index range scans do not use a reverse-key index.

Using DOMAIN Type Indexes

The DOMAIN index is another feature new to Oracle 8i.. A DOMAIN index is actually an extension to the Oracle index system that allows developers to create their own index type (this is usually done by companies developing commercial applications or cartridges). Index types are created using the CREATE INDEXTYPE command. A DOMAIN index can only be placed on a single column in Oracle8i. Multiple DOMAIN indexes can be created on the same column if their index types are different. A DOMAIN index is stored in an index organized table (IOT) or in an external file.

DOMAIN indexes are built against an index type. An index type encapsulates a set of routines that manage access to a domain. This encapsulation allows efficient search and retrieval functions for complex domains such as text, spatial, image, and OLAP data. All of this encapsulation is specified using the Oracle Data Cartridge Interface for indexes (ODCIIndex). These routines can be implemented by type methods. (For a more complete description, refer to the Oracle Data Cartridge Developer’s Guide (Release 1, (9.0.1), PART  A88896-01, Oracle Corporation, June, 2001) as it is beyond the scope of this book to cover the complete usage of DOMAIN indexes.)

Using Bitmap Join Indexes

In Oracle, the bitmap join index becomes part of the arsenal of indexes available in Oracle. The bitmap join index allows prejoining of two tables through a bitmap index. The bitmap join index requires a unique constraint be present. For example, notice the bold faced code in the execution plan below:

SQL> CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP;
Table created.
SQL>
CREATE TABLE DEPT1 AS SELECT * FROM SCOTT.DEPT;
Table created.
SQL>
ALTER TABLE DEPT1 ADD CONSTRAINT DEPT_CONSTR1 UNIQUE (DEPTNO);
Table altered.
SQL> CREATE BITMAP INDEX EMPDEPT_IDX
  1  ON EMP1(DEPT1.DEPTNO)
  2  FROM EMP1, DEPT1
  3* WHERE EMP1.DEPTNO = DEPT1.DEPTNO;
Index created.
SQL> SELECT /*+ INDEX(EMP1 EMPDEPT_IDX) */ COUNT(*)
  1  FROM EMP1, DEPT1
  2* WHERE EMP1.DEPTNO = DEPT1.DEPTNO;
COUNT(*)
--------
     14
Elapsed: 00:00:00.67
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP INDEX (FULL SCAN) OF 'EMPDEPT_IDX'

Now let’s play with some real numbers. We’ll create emp5/emp6 to have 2 million rows each, with indexes on the empno column:

SQL> alter table emp5
  1* add constraint emp5_constr unique (empno);
SQL> create bitmap index emp5_j6
  1  on emp6(emp5.empno)
  2  from emp5,emp6
  3* where emp5.empno=emp6.empno;
Index created.
Elapsed: 00:02:29.91
SQL> select count(*)
  2  from emp5, emp6
  3* where emp5.empno=emp6.empno
  COUNT(*)
----------
   2005007
Elapsed: 00:01:07.18
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'EMP6'
   4    2       INDEX (RANGE SCAN) OF 'EMP5I_EMPNO' (NON-UNIQUE)
Statistics
----------------------------------------------------------
6026820  consistent gets
7760  physical reads
       FORCE THE USE OF THE BITMAP JOIN INDEX:
SQL> select /*+ index(emp6 emp5_j6) */ count(*)
  2  from emp5, emp6
  3* where emp5.empno=emp6.empno
  COUNT(*)
----------
   2005007
Elapsed: 00:00:00.87! Same as with small tables!
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE 
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP INDEX (FULL SCAN) OF 'EMP5_J6'
Statistics
----------------------------------------------------------
970  consistent gets
967  physical reads

What you should take from the above example is that proper use of this new bitmap join index technology can lead to improvement in query speeds by up to 10,000 times.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
   
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter