Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: What indexes to create?

    Hey,

    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:

    NBR_DISTRICT,
    DATE_PAY_PERIOD,
    SCHOOL_YEAR,
    CALENDAR_YEAR,
    FISCAL_YEAR,
    POSITION,
    PAY_TYPE,
    CODE

    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.

    Any suggestions?

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The information you gave really doesn't help, could we see the actual query?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  4. #4
    Join Date
    Dec 2004
    Posts
    74
    Thanks andrewst,

    My query does look like what you wrote first.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •