Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table which has the following columns:

CREATE TABLE example (
   exid INT,
   extype INT,
   studentid INT,
   (more columns)
)

The table will have hundreds of thousands rows, and each record will have distinct value of exid, extype and studentid. While studentid and exid will have plenty of distinct values, extype will have max. 6 values. The table will be joined in the following way:

SELECT ... 
FROM example ex 

LEFT JOIN otherTable1 ot1 ON ex.studentid = ot1.studentid
    AND ex.exid = ot1.exid 
    AND ex.extype = 1

LEFT JOIN otherTable2 ot2 ON ex.studentid = ot2.studentid
    AND ex.exid = ot2.exid 
    AND ex.extype = 2

Can you suggest me what index(es) on example table will be the most suitable in regards of performance here (I should also add other tables used for joins will go into millions of rows)?

share|improve this question
    
Index(studentid,exid,extype) on example and index(studentid,exid) on otherTable1 might help. –  Mihai Feb 13 at 13:24
add comment

1 Answer 1

up vote 3 down vote accepted

You should benchmark it using autotrace (http://betteratoracle.com/posts/10-using-autotrace) for example.

If you expect millions of rows to be returned, then I do not think indexing will help at all. Simple full table scan and hash joining is the most efficient way of executing this kind of query (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968).

Putting the least selective column first and using key compression can decrease the index size and reduce IO and CPU consumption (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806017524595).

The following index should be quite sufficient if you fetch only few rows:

create unique index test1 on example(extype,studentid,exid) compress 1;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.