Re: Creating several indexes on same tables - with one FTS only ?

What I know is that if you create an index, and the column(s) being indexed already exist(s) in another index, then Oracle uses this index to scan the values instead of doing a full table scan. So if you have many indexes, try to create them in an order which meets this rule if possible. Maybe you can create a "dummy" index first with all indexed columns and then create the others but I don't know if this will save you time. I did a simple test with a table of 100K rows and it was faster but of course this can vary a lot depending on table size, column size, I/O, cache, etc. Take a look at the sample below, it was more than 2x faster.

SQL> describe temp

Name Type

---------------- -------------
COMP_ID          VARCHAR2(10)
ITEM             VARCHAR2(50)

• Create individual indexes.

SQL> create index index_1 on temp (comp_id);

Elapsed: 00:00:01.00

SQL> create index index_2 on temp (item);

Elapsed: 00:00:01.02

SQL> create index index_3 on temp (global_cust_name);

Elapsed: 00:00:01.02

• Total time: 3.04s
• Create a dummy index with all columns then the individual indexes.

SQL> create index index_0 on temp (comp_id,item,global_cust_name);

Elapsed: 00:00:01.08

SQL> create index index_1 on temp (comp_id);

Elapsed: 00:00:00.07

SQL> create index index_2 on temp (item);

Elapsed: 00:00:00.09

SQL> create index index_3 on temp (global_cust_name);

Elapsed: 00:00:00.08

SQL> drop index index_0;

Elapsed: 00:00:00.00

• Total time: 1.32s