Indexes causes queries to slowdown!?!?!

[bmnv]

Greeting everybody,

We are students running a small datawarehouse (a copy Oracle's 9i example - SH - with some changes

at conceptual level) on Oracle 9i for a University work.
We are also using Oracle 9i Discoverer Suite (Administrator & Desktop) to execute ad-hoc queries.

We've created some bitmap indexes on all fk's of the sales table (facts table), and some other

bitmap indexes on the dimension tables.

After creating those indexes we executed the same queries we we're using before, to test the DW

speed, and queries execution got slower (about 2x more) on Discoverer even though the execution

plan is using the indexes. On some queries the indexes aren't used at all.
How can that be possible? What are we doing wrong, and how can we correct it?

Thanks in advance

 

[blom0344]

Problem could be that you believe to firmly in the principle that 'the more indexes, the faster results' holds true. Using an index actually always takes additional time which is compensated when using the index to fetch SPECIFIC values is much faster than doing a scan. However, if the query dictatates that a table has to be addressed to a large extent anyway, a full table scan can be FASTER than using an index.............
Just throwing in an index at every field you THINK will be worthwile is not a good idea. You create a lot of overhead, with requirement to keep everything up to date (running statistics and such). And if you do not manage the overhead as such there will possibly be no gain at all.....

The fact that an existing index is not used , means that that the database manager has a mind of it's own (to a certain degree) Check the use of optimizer hints for ORACLE... T. Blom
Information analyst
tbl@shimano-eu.com

 

[monsky]

I suggest you start indexing with the basics then slowly test other fields. Since you are using Oracle you may want to perform ANALYZE and COMPUTE on your tables and indexes to take advantage of the Cost Based Analyzer. Everytime you rebuild your index or load your tables the statistics of each one changes. The CBO plans and creates the most efficient way to execute a SQL statements depending on the statistics of table and index objects. I suggest you try this.

 

[Bastien]

Bitmap indexes are better suited to items of high cardinality (items for which there are lots of records but few values (like gender (either M or F)). Bitmap indexes on records of low cardinality (where there are many possible values) will not help at all. A regular B-tree index would be better suited here.

As mentioned above, if the optimizer determines that the query will require a full table scan to meet the requirements of the query, indexes will not help you. As DSS situations usually require more data for the query, FTS may happen more frequently, rendering the index useless.

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[entaroadun]

What is the cardinality of your bitmaps? Bitmaps are stored in the header of the table and the entire thing has to be loaded into memory before the bitmaps are scanned.

If you have more than 5 or 6 keys, use regular indexes.