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

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

  1. #1
    Join Date
    Jan 2003
    Posts
    3

    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
    Location
    Brazil
    Posts
    282
    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.


    F.

  3. #3
    Join Date
    Jan 2003
    Posts
    3
    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.
    www.cnoug.org

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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    Posts
    534
    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.

    Mike

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