Oracle Database Discussions

Hi there,

(Oracle 11.2.0.4 on Linux 7.2)

I'm looking for a scenario at which using reverse indexes are useful. As I searched there are two main facts regarding reverse indexes:

1. They speed up insert specially if the indexed column is feeding by an increasing sequence.
2. Index range scan is not possible on reverse indexes.

So it seems that the only rational scenario to use reverse indexes is one with high insert operations on a sequential indexed column and no future range scan requirement. To evaluate the performance I ran the following test scenario:

First I created a sequence to generate sequential numbers:

create sequence test_seq minvalue 1 maxvalue 9999999999999999 start with 1 increment by 1;

Then I create a test table, T using CTAS:

create table t as select * from dba_objects where 1=2;

I created a normal index on OBJECT_ID column then after:

create index ti on t(object_id);

Now I ran 4 tests all inserting data in the table with different methods and methods. to be completely isolated, I recreated the sequence, table and the index for each test:

TEST 1:

insert into t
  select OWNER,
         OBJECT_NAME,
         SUBOBJECT_NAME,
         test_seq.nextval,
         DATA_OBJECT_ID,
         OBJECT_TYPE,
         CREATED,
         LAST_DDL_TIME,
         TIMESTAMP,
         STATUS,
         TEMPORARY,
         GENERATED,
         SECONDARY,
         NAMESPACE,
         EDITION_NAME
    from dba_objects;

Test 2:

insert into t
  select OWNER,
         OBJECT_NAME,
         SUBOBJECT_NAME,
         test_seq.nextval,
         DATA_OBJECT_ID,
         OBJECT_TYPE,
         CREATED,
         LAST_DDL_TIME,
         TIMESTAMP,
         STATUS,
         TEMPORARY,
         GENERATED,
         SECONDARY,
         NAMESPACE,
         EDITION_NAME
    from dba_objects, (select 0 from dual connect by level <= 10);

Test 3: just like test 2 but this time crossing join with 100 rows dummy table to generate 10 times more rows than previous test.

Test 4:

begin
  for s in (select OWNER,
                   OBJECT_NAME,
                   SUBOBJECT_NAME,
                   test_seq.nextval,
                   DATA_OBJECT_ID,
                   OBJECT_TYPE,
                   CREATED,
                   LAST_DDL_TIME,
                   TIMESTAMP,
                   STATUS,
                   TEMPORARY,
                   GENERATED,
                   SECONDARY,
                   NAMESPACE,
                   EDITION_NAME
              from dba_objects, (select 0 from dual connect by level <= 10)) loop
    insert into t values s;
  end loop;
end;

Then I ran the same tests while table had a reverse index on the same column:

create index ti on t(object_id) reverse;

surprisingly in all of the above-mentioned tests normal index performed better than reverse one:

Please let me know at which scenario a reverse index will speed up insert operation comparing with Normal index one?

Regards

Message was edited by: f9smsk , missing comparison table header