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!

Indexing the correct ways? in oracle...

729412Oct 29 2009 — edited Oct 29 2009
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

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 26 2009
Added on Oct 29 2009
4 comments
292 views