0

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)?

  • Index(studentid,exid,extype) on example and index(studentid,exid) on otherTable1 might help. – Mihai Feb 13 '14 at 13:24
3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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