10 one-column indexes

[jodie]

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

[slimdave]

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.

[gbrabham]

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

[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.

[jodie]

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

[KenEwald]

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.

[jmodic]

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!

[adewri]

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

[slimdave]

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.

[grjohnson]

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