multiple indexes Vs multi column index ?
userP5Mar 19 2010 — edited Mar 19 2010I 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 ?