DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Indexes causes queries to slowdown!?!?!

  1. #1
    Join Date
    Jan 2003

    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
    Dec 2001
    if the cardinality of values in your bitmapped indexed column is very high, in other words, if you have a lof of distinct values, you'll face a worse performance.


  3. #3
    Join Date
    Jan 2003
    we are using bitmap indexes on columns with low cardinality (1-3%), that should not be the problem.

  4. #4
    The answer of why my index is not get used depends on so much factors, and as the beginning, try to use dbms_stats to analyze those tables and indexes.

  5. #5
    Join Date
    Jan 2003
    all tables and indexes have been analized but the problem still remains :(

  6. #6
    Join Date
    Aug 2002
    Colorado Springs
    do you have star_transformation_enabled? Have you declared the FK's? Are the columns constrained to not null? Are the columns themselves analyzed? What data volumes do you have? Have you mplemented any physical row ordering? What about parallelism?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Mar 2002
    Which amout (%) of data get accesed at the fact table when you do your queries?

    Could you show the explain plan of a same query using once the old design and once you new design.


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