I have a table where I am having around 40 columns and Unique Primary index (UPI) is a combination of 5 columns. Please let me know how I should use these columns in where clause to get advantage of UPI i.e to get faster retrieval of data. The table is having around 5 million rows and size is around 200 GB.
Primary index is used for row distribution when records are inserted into the table and aids in faster retreival of rows when the where clause of select statement uses a explicit condition on PI columns of the table. As far as distribution mechanism is considered, rows will be evenly distributed across all amps depending upon the uniqueness of your index.Using composite primary index would increase the uniqueness of your Index while inserting the records.However, for such tables if you want to retreive rows fastly using a single-amp step and avoiding full-table scan/all amp steps, it is better you include all the columns of the composite(multi-column) PI in the where condition.
You will find all the details regarding the PI in DDL manuals and database design manuals.
To make the explanation simple, consider the student record table shown below.
dept_id roll first name last name ------- ----------- -------------------- -------------- COMP 1 AYES Williams MATH 2 LEO ISSAC MATH 1 Andrew Williams COMP 2 LEO John
In the above table each student can be identified easily by the combination of Dept_Id and Roll_number. To fetch the record of "Leo" from the above table, you need to query the student_record table which is distributed based on primary Index (dept_Id,roll_number). Querying the table just by roll number would not fetch you exact record.it would cost you an all amp search. To get an exact record,say,Leo's record, you need to query the table using Dept_Id and roll_number which identifies LEO uniquely.This only costs you a single amp operation.
OLTP queires take advantage of using all columns in composite index to fetch the exact records.
To add up to the explanation , in case you are not able to use all the 5 columns in where clause you have to collect statistics on the columns that you use in where clause (though it is a part of the primary index or not). This will boost the performance.