var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: What indexes to create?
I am creating several reports using a table called Employee_School which as approx 2000000 rows and I am wondering what indexes the table should have in order to speed up my selects.
My 'where' clause is using the following columns in my select statement:
where the user selects a value from a drop down for every column, and 'ALL' can be selected for every column besides the first.
Indexes are kind of new to me so I am not sure what would be needed in this case and with similar situations. Would a separate index be needed for every column, or would I need several multiple column indexes, or would the one multiple column index be enough.
The information you gave really doesn't help, could we see the actual query?
You do not need a parachute to skydive. You only need a parachute to skydive twice.
What does the SQL used by the application look like? If it looks like this:
WHERE (:selected_nbr_district = 'ALL' OR NBR_DISTRICT = :selected_nbr_district)
AND (:selected_date_pay_period = 'ALL' OR DATE_PAY_PERIOD = :selected_date_pay_period)
AND ... etc.
then no index will help you. Ditto if it looks like this:
WHERE (NBR_DISTRICT = DECODE(:selected_nbr_district,'ALL',NBR_DISTRICT,: selected_nbr_district))
AND ... etc.
The only way an index may be useful is if your application uses dynamic SQL and generates the WHERE clause based on the selections so that the SQL is like this:
-- If user selected 'ALL' for everything except NBR_DISTRICT:
WHERE NBR_DISTRICT = :selected_NBR_DISTRICT;
-- If user selected 'ALL' for everything except DATE_PAY_PERIOD:
WHERE DATE_PAY_PERIOD = :selected_date_pay_period;
-- If user selected 'ALL' for everything except NBR_DISTRICT and DATE_PAY_PERIOD:
WHERE NBR_DISTRICT = :selected_NBR_DISTRICT
AND DATE_PAY_PERIOD = :selected_date_pay_period;
Now it would be valid to create an index on each column individually - provided the column is selective enough to be worthwhile indexing at all.
My query does look like what you wrote first.