Forum Stats

  • 3,751,465 Users
  • 2,250,366 Discussions
  • 7,867,433 Comments

Discussions

Multi-column Index vs One index for each column

Flag
Flag Member Posts: 15
edited Sep 10, 2012 2:34PM in General Database Discussions
Hello everyone,
i have one table about 20 000 000 rows, some developers have to generate reports on it and i want to create indexes on this table.
The table has 34 columns, no primary key, no unique keys.
The "where..." clause of the reports usually use 8 columns but some reports uses 8 + some other columns.
can any one help me on what kind of indexes do i have to create?
1. one index for each column used in "where clause"
2. one index for 8 columns and some other indexes for other used columns
3. one index for all columns
or something else etc...

br flag
Tagged:

Answers

  • Marco V.
    Marco V. Member Posts: 547
    I will start with one index for column.
  • Marco V.
    Marco V. Member Posts: 547
    Next thing is to get information about selectivity on those columns, the running query scripts and the expected response time of the reports...
  • 713555
    713555 Member Posts: 824
    marcopb wrote:
    I will start with one index for column.
    agreed. Start with one column.

    you may want to revisit database design if you dont have a PK. however, working with what you have, you have a query that uses a column. index just that column. if you have another query that uses 10 columns, and each column needs an index, index the 10 columns.

    Its very much milage may vary depending on the query.
  • Flag
    Flag Member Posts: 15
    you mean to create about 14 indxes(Number of columns used in where clause). i thought it might be a better way, because first 8 columns are used almost in every query.
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,210 Bronze Trophy
    edited Sep 10, 2012 7:17AM
    i have one table about 20 000 000 rows, some developers have to generate reports on it and i want to create indexes on this table. 
    The table has 34 columns, no primary key, no unique keys.
    The "where..." clause of the reports usually use 8 columns but some reports uses 8 + some other columns.
    can any one help me on what kind of indexes do i have to create?
    1. one index for each column used in "where clause"
    2. one index for 8 columns and some other indexes for other used columns
    3. one index for all columns
    or something else etc...
    What's the version of your data base? what kind of database you have, DWH or OTLP? The answer might depend on the type of database as far as bitmap indexes might suit or might not depending if you are runing DWH or OLTP kind of database

    Let me suppose that you are runing OLTP database and you have a where clause with 8 columns.

    1) are all those where clause equalities (where col1 = and col2 =) or there are inequalities?
    2) could you evaluate the most repetitive columns?
    3) could you know the column that could have the best clustering factor (the column which most follow a certain order in the table)

    Based on that I would suggest to create one b-tree index having 8 columns (even though that it seems for me to high) this index should follow the following points:

    1) put the most repetitive column at the leading edge (and compress the index if necessary)
    2) put the columns that are used in equalitity predicate first
    3) put the column having the best clustering factor first

    The most precise index you have the best access you could gain.

    Of course that you have to know that an index access is not always good and a FULL table scan is not always bad.

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 713555
    713555 Member Posts: 824
    Flag wrote:
    you mean to create about 14 indxes(Number of columns used in where clause). i thought it might be a better way, because first 8 columns are used almost in every query.
    No, just because you use a column in an query doesnt mean you have to index. Milage may vary. You create indexes where you need them.

    If youre at this stage than you need to read a lot more on index creation. run a query, get a plan, be happy with the execution of it. If performance is bad, an index may help. Only you can tell this as your SQL evolves.
  • Flag
    Flag Member Posts: 15
    DB version oracle 10gR2 (10.2.0)
    OLTP

    types of where clause :
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8<
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8< ..and col9=
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8< ..and col10= and col11=
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8< ..and col 14=
  • 713555
    713555 Member Posts: 824
    Flag wrote:
    DB version oracle 10gR2 (10.2.0)
    OLTP

    types of where clause :
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8<
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8< ..and col9=
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8< ..and col10= and col11=
    where col1= and col2= and col3= and col4= and col5= and col6 =and col7= and col8< ..and col 14=
    Youre really not going to get much help on here posting that. It seems to me that you need help with a tuning strategy, not a query in particular. If you create some indexes and have bad performance and are stuck, then show us the query and the plan and we can help. Other than that, my earlier point remains. look at the plan, see where does an index need to be created. add that index. start there.
  • Hi,
    You need to understand your data to understand what you should index - for instance if 'col8' is very distinct, is always included in the where clause then you may well just need a single column index on this column - anything else is probably just wasting space.

    Regards,
    Harry
  • >
    i have one table about 20 000 000 rows, some developers have to generate reports on it and i want to create indexes on this table.
    The table has 34 columns, no primary key, no unique keys.
    The "where..." clause of the reports usually use 8 columns but some reports uses 8 + some other columns.
    can any one help me on what kind of indexes do i have to create?
    1. one index for each column used in "where clause"
    2. one index for 8 columns and some other indexes for other used columns
    3. one index for all columns
    or something else etc...
    >
    Something else - along the lines of what harry76 said.

    Start by getting rid of your 'want to create indexes'. There is no 'one size fits all' set of indexes.

    Indexes are used to make getting data out of the DB more efficient (aside from their use for primary key and constraint purposes).

    Only create an index if it serves that purpose. That means you need to prove that: 1) the index will actually be used and 2) when it is used data extraction is more efficient than when it is not used.

    If you can't prove both of those then don't create the index.

    If all queries return 5 million rows why would you want to have ANY indexes?

    How many distinct values do each of those 8 columns have?
This discussion has been closed.