Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: Indexes causes queries to slowdown!?!?

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Indexes causes queries to slowdown!?!?

    Have you analyzed the tables so that the optimizer has good statistics to work with?

    It is quite possible and correct for the optimizer to choose not to use and index, if it believes the query will be quicker without.

    I don't think bitmap indexes are suitable for use on dimension tables. These should have a primary key using a normal index. On fact tables, yes you might want bitmap indexes.

  3. #3
    Join Date
    Jan 2003
    Posts
    3
    We have analized the tables after creating the indexes.

    As of using bitmaps on the dimensions, we're not using them on the pk's but on some atributes like gender, marital_status, prod_category, and others with low cardinality (between 1-3%)

  4. #4
    Join Date
    Jan 2003
    Posts
    15
    do these columns you've bitmap index contain less that 50 distinct values? Probably a stupid question, but one worth asking.

  5. #5
    Join Date
    Jan 2003
    Posts
    3
    all bitmap indexes are created on collums of low cardinality (<3%) such as gender, marital_status, prod_category, ect... all of very low cardinality.

  6. #6
    Join Date
    Jan 2003
    Posts
    15
    Originally posted by BM_NV
    all bitmap indexes are created on collums of low cardinality (<3%) such as gender, marital_status, prod_category, ect... all of very low cardinality.
    Bitmap Indexes are only worth using when the number of distinct values for that column is less that 50, despite the cardinality. You may want to re-check the use of bitmap index and decide on b-tree indexes instead.

Posting Permissions

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