Forum Stats

  • 3,781,257 Users
  • 2,254,496 Discussions
  • 7,879,625 Comments

Discussions

Indexing the correct ways? in oracle...

729412
729412 Member Posts: 1
edited Oct 29, 2009 9:47AM in General Database Discussions
hi guys,

I need a suggestion on indexing the correct ways...

I have a table : PERSONAL_TASK.
The unique field for that table is STAFFID,MONTH and TASKID --- PARAMETER FOR A SINGLE ROW UPDATE
All the FIELD type is VARCHAR2

The data is about 20 million row.

My question is on indexing method.

should i index the table like this?

1. creating 3 INDEX
a. CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID)
b. CREATE INDEX ix_emp_02 ON PERSONAL_TASK (MONTH)
c. CREATE INDEX ix_emp_03 ON PERSONAL_TASK (TASKID)

OR

2. just creating a concatenated index like this ?
CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID,MONTH , TASKID)


My query usually select the table using the 3 field name
such as
1. select * PERSONAL_TASK where STAFFID = '111'
2. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN'
3. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN' AND TASKID = 'TASKID' - OR for update single row

so, which one is better in the performance issues when selecting and updating in a 20 million row of data?

thank you for the comment and suggestion.

Haire Kahfi
http://haire-family.blogspot.com/

Edited by: user12096581 on Oct 29, 2009 2:02 PM
Tagged:

Answers

  • Deepak_DBA
    Deepak_DBA Member Posts: 1,039
    hi,


    instead of creating concadinating indexes go for the first one.. it will be better..

    you are going to use AND,OR operators in your where condition..
    so the jobs are seperated and oracle will retrive data faster than concadinate index;.


    regards,
    Deepak
    Deepak_DBA
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    If the three columns together define uniqueness, then you must create a concatenated UNIQUE Index.

    (This is better defined as a Primary Key if the columns are NOT NULL).


    If your queries always specify STAFFID and optionallly additionally specify MONTH and TASKID the index might as well be in that order.

    It doesn't seem as if MONTH and TASKID would have many distinct values, so single column indexes on these two may not be helpful.

    Hemant K Chitale
  • EdStevens
    EdStevens Member Posts: 28,600 Gold Crown
    hairekahfi wrote:
    hi guys,

    I need a suggestion on indexing the correct ways...

    I have a table : PERSONAL_TASK.
    The unique field for that table is STAFFID,MONTH and TASKID --- PARAMETER FOR A SINGLE ROW UPDATE
    All the FIELD type is VARCHAR2

    The data is about 20 million row.

    My question is on indexing method.

    should i index the table like this?

    1. creating 3 INDEX
    a. CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID)
    b. CREATE INDEX ix_emp_02 ON PERSONAL_TASK (MONTH)
    c. CREATE INDEX ix_emp_03 ON PERSONAL_TASK (TASKID)

    OR

    2. just creating a concatenated index like this ?
    CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID,MONTH , TASKID)


    My query usually select the table using the 3 field name
    such as
    1. select * PERSONAL_TASK where STAFFID = '111'
    2. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN'
    3. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN' AND TASKID = 'TASKID' - OR for update single row

    so, which one is better in the performance issues when selecting and updating in a 20 million row of data?

    thank you for the comment and suggestion.

    Haire Kahfi
    http://haire-family.blogspot.com/

    Edited by: user12096581 on Oct 29, 2009 2:02 PM
    I would first suggest you make sure you review the basic rules of data normalization and your table design to make sure you have appropriately identified and defined your primary key.
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    Deepak_DBA wrote:
    hi,


    instead of creating concadinating indexes go for the first one.. it will be better..

    you are going to use AND,OR operators in your where condition..
    so the jobs are seperated and oracle will retrive data faster than concadinate index;.

    This is interesting. Can you demonstrate it that using a single index over a composite where all three columns will be used together is going to get more benefit from individual indexes?

    Aman....
This discussion has been closed.