Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    19

    Unanswered: Index not picked up

    Hi all,

    I have in this example a simple query:

    SELECT
    SHIPMENTTYPE,
    SUM(QTY)
    FROM
    CS_DIM_ORDER,
    CS_FACTS_ITEM
    WHERE
    CS_DIM_ORDER.ORDER_ID=CS_FACTS_ITEM.ORDER_ID
    GROUP BY
    SHIPMENTTYPE;

    This returns the following:
    SHIPMENTTYPE,SUM(QTY)
    0,110234
    1,4323
    11,1798

    The explain plan is included.

    I have a primary key index on the CS_DIM_ORDER table on the ORDER_ID field and I have a bitmap index on the ORDER_ID column in the FACT table.

    I've noticed that for all my queries on my star schema, it does a full scan on the fact table.

    Can anyone shed some light as to why this is?

    Thanks
    Attached Thumbnails Attached Thumbnails eplan.JPG  

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chesl73 View Post
    I've noticed that for all my queries on my star schema, it does a full scan on the fact table.

    Can anyone shed some light as to why this is?
    Why shouldn't it? You request to read the entire table, a bitmap index cannot be used for the join, and GROUP BY does not need that index either.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2008
    Posts
    19
    Thank for the reply.

    I would have thought it would use the index for the join on ORDER_ID?
    As you can tell, I'm not expert in this area!

    So, if you were me, would this be fine then to use bitmap indexes on the fact table or should I use something else?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chesl73 View Post
    So, if you were me, would this be fine then to use bitmap indexes on the fact table or should I use something else?
    For this particular query it is useless, but for other queries - only you can tell. Take a look at these articles: DBAzine.com: Understanding Bitmap Indexes
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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