10 one-column indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: 10 one-column indexes

  1. #1
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334

    Question 10 one-column indexes

    Hi!
    We are trying to duplicate the functionality of one of our soon-to-be-out-of-business vendors, and I just got the DDL today.

    It consists of ONE table with 110 columns. It has 10 indexes, each on ONE column only.

    My question is: What is the benefit (if any) of having 10 one-column indexes? Would it me more efficient (for updates) to have more than one column in each index? Although not all 10 in one.

    I'm interested to hear your opinions.

    Thanks!
    Jodie

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Tricky to answer without knowing more about the application and the type of querying. Can you give more details?

    Multiple single-column indexes are a common practice on DW/DSS fact tables, less common on OLTP.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    To me, 10 - 1 column indexes is alot, BUT ... It's all in how the table is used... If the table is more of a "SELECT FROM" table and
    not many updates, then ... 10 is certainly reasonable. If there are quite a few updates, then 10 maybe toooo many ...

    Depending on how the code was written to access this table, you may
    not have a choice in the matter.

    It almost sounds to me like a developer designed the table, and added indexes as he needed (after the code was already generated).

    Gregg

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Pardon my ignorance, but i would like to know why would any one have 10 indexes on one single column. Doesn't make any sense to me, neither from the design point of view nor from coding point of view.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Thanks all for your responses. Glad to hear I'm not the only one that thought 10 one-column indexes wasn't "normal".

    The app is pretty close to a DW app. My company sends them data weekly, and they combine it with trucking/shipping data. They do "some manipulation" and send it back to us in reports. I can't give you too many more details than that. They won't really tell us what they do, so we are trying to figure it out using the DDL. Which, I just discovered, wasn't ALL sent to me. FUN!

    Thanks again!

    Jodie

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    10 indexes on the same column within the same table is bad any way you slice it!

    10 indexes on different columns within the same table makes sense.

    I'd second slimdave's comment to look at the code. You might be able to drop an index or two or concatenate some.

    10 indexes on a DW table is not too uncommon, especially if ad-hoc queries are being thrown at it.
    Last edited by KenEwald; 06-12-2003 at 04:56 PM.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by adewri
    Pardon my ignorance, but i would like to know why would any one have 10 indexes on one single column. Doesn't make any sense to me, neither from the design point of view nor from coding point of view.
    Amar, he is talking about 10 different indexes on the same table, but each covering only one separate column. So 10 one-column indexes on 10 different columns.

    Otherwise, not only it wouldn't "make any sense, neither from the design point of view nor from coding point of view", it would even not be physically possible - Oracle would not allow you to have more than one single-column index on the same column!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Hey Jurij nice to see you...

    If thats the case then i don't think 10 indexes on 10 seperate columns of a 110 column table is a big deal. We have 25 indexes on a 80 column table.

    now call that a bad design or business requirement
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    For a DW table this is fine.

    I have a 115 column DM table with 24 single column indexes, all but 3 of them being bitmap-type, and the overhead in maintaing them is very small because data is loaded through partition exchange, followed by use of DBMS_JOB to rebuild multiple index partitions in parallel and nologging at the same time. Takes but a few moments.

    Lots of single column indexes are particularly preferred in DW/DSS apps because you have no pre-defined list of queries - any dimension column is available for a predicate (for predication? can you say that?) and combining multiple bitmap indexes is a fast operation.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by adewri
    Pardon my ignorance, but i would like to know why would any one have 10 indexes on one single column. Doesn't make any sense to me, neither from the design point of view nor from coding point of view.
    adewri, never mind making sense, it's impossible to do multiple indexes on a single column.

    SQL> create table test (a number, b number);

    Table created.

    SQL> create index test_1 on test(a);

    Index created.

    SQL> create index test_2 on test(a);
    create index test_2 on test(a)
    *
    ERROR at line 1:
    ORA-01408: such column list already indexed
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

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



Click Here to Expand Forum to Full Width