Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

multiple indexes Vs multi column index ?

userP5Mar 19 2010 — edited Mar 19 2010
I am using oracle 10g. I have a table with huge data. There are 4 indexes on this table with combination of columns. All the indexes are created using the script like -
CREATE INDEX INDX1 ON My_TABLE
(ID, emp_NUMBER, emp_sal, emp_div)
TABLESPACE my_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
NOLOGGING
NOPARALLEL;

CREATE INDEX INDX2 ON My_TABLE
(ID, emp_NUMBER, emp_name)
TABLESPACE my_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
NOLOGGING
NOPARALLEL;

and so on...
Few columns are always there in every index like Id, emp_number.

Most of the select queries are using the id and emp_number in the where clause criteria's. SQL "select" performance is not very good and need to find some kinda solution to this problem .

Shall I create one index using all the columns present in various indexes instead of various indexes with different combination of the columns ?

Comments

sb92075
Please realize that we don't have your tables & we don't have your data.
Therefore only YOU can run bechmark tests to see for yourself what produces the better results.
EdStevens
userPrasad wrote:
I am using oracle 10g. I have a table with huge data. There are 4 indexes on this table with combination of columns. All the indexes are created using the script like -
CREATE INDEX INDX1 ON My_TABLE
(ID, emp_NUMBER, emp_sal, emp_div)
TABLESPACE my_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
NOLOGGING
NOPARALLEL;

CREATE INDEX INDX2 ON My_TABLE
(ID, emp_NUMBER, emp_name)
TABLESPACE my_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
NOLOGGING
NOPARALLEL;

and so on...
Few columns are always there in every index like Id, emp_number.

Most of the select queries are using the id and emp_number in the where clause criteria's. SQL "select" performance is not very good and need to find some kinda solution to this problem .

Shall I create one index using all the columns present in various indexes instead of various indexes with different combination of the columns ?
CREATE INDEX INDX2 ON My_TABLE
(ID, 
 emp_NUMBER, 
 emp_name)
and

CREATE INDEX INDX2 ON My_TABLE
(emp_name, 
 emp_number,
 ID)
Both have the same collection of columns, but they most defianetly do not serve the same purpose. Once you understand that, you will have your answer.
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 16 2010
Added on Mar 19 2010
2 comments
869 views