Forum Stats

  • 3,814,524 Users
  • 2,258,880 Discussions
  • 7,892,765 Comments

Discussions

multiple indexes Vs multi column index ?

userP5
userP5 Member Posts: 63 Blue Ribbon
edited Mar 19, 2010 11:06AM in General Database Discussions
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 ?

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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
    EdStevens Member Posts: 28,778 Gold Crown
    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.
This discussion has been closed.