Question on Indexing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Question on Indexing

  1. #1
    Join Date
    Jul 2003
    Posts
    134

    Question on Indexing

    Could anypne pls answer this Q for me?

    Regards,
    Vipassana


    The credit controller for your organization has complained that the report she runs to show customers with bad credit ratings takes too long to run. You look at the query that the report runs and determine that the report would run faster if there were an index on the CREDIT_RATING column of the CUSTOMERS table.
    The CUSTOMERS table has about 5 million rows and around 100 new rows are added every month. Old records are not deleted from the table.
    The CREDIT_RATING column is defined as a VARCHAR2(5) field. There are only 10 possible credit ratings and a customer's credit rating changes infrequently. Customers with bad credit ratings have a value in the CREDIT_RATINGS column of 'BAD' or 'F'.
    Which type of index would be best for this column?
    A. B-Tree
    B. bitmap
    C. reverse key
    D. function-based

  2. #2
    Join Date
    Jan 2004
    Posts
    25
    Hi,,

    Bitmap would be good for this.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    This is some dumb academic question, right? Coursework?

    You can't just decide that bitmap isbetter without knowing how the 100 new rows a month are inserted, nor how frequently the values change. Knowing that each credit rating changes "infrequently" tells you nothing, when there's a million rows in the table.

    In fact, an index might not even be beneficial in this case. What % of the rows are "BAD" or "F", and what % of blocks do they represent.?

    I hate these stupid questions. Anyway, I'd guess that a function-based index might also be best. Or a b-tree.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I would go for bitmap index.

    The reasons:

    1 Since the table gets 100 rows only every month, it tells that it is a DSS/DW system, definitely not a OLTP system.
    2 The CREDIT_RATING column takes only 10 values. Assume they are all distributed evenly, then 'BAD' and 'F' may have 1 Million rows. And you are going to read 20 % of the rows from the table.
    3 The column credit_rating is not "updated frequently". So no issue of locking.
    4 The bitmap index is going to consume less space than the b-tree index.
    5 Obviously the where clause will be like "CREDIT_RATINGS = 'BAD' or CREDIT_RATINGS = 'F'. Bitmap index works very well with LOGICAL OR conditions compared to b-tree index. For this reason alone, I would like to opt for bitmap index.

    Tamil
    Last edited by tamilselvan; 06-04-2004 at 04:22 PM.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    I would go for bitmap index.

    The reasons:

    1 Since the table gets 100 rows only every month, it tells that it is a DSS/DW system, definitely not a OLTP system.
    2 The CREDIT_RATING column takes only 10 values. Assume they are all distributed evenly, then 'BAD' and 'F' may have 1 Million rows. And you are going to read 20 % of the rows from the table.
    3 The column credit_rating is not "updated frequently". So no issue of locking.
    4 The bitmap index is going to consume less space than the b-tree index.
    5 Obviously the where clause will be like "CREDIT_RATINGS = 'BAD' or CREDIT_RATINGS = 'F'. Bitmap index works very well with LOGICAL OR conditions compared to b-tree index. You have to add combined_index hint the query. For this reason alone, I would like to opt for bitmap index.

    Tamil
    Well, I'm going to have to go ahead and disagree with you there.

    1. It tells you that it's an academic question not a real life one. 5 million rows, but only 100 new rows per month? Gimme a break.

    2. Rows-shmows -- it's blocks that count. If the rows are distributed evenly then that's probably 100% block retrieval, so a full-scan would be faster.

    3. Update it once per year per row, which would meet a definition of infrequent, and you've still got 5 million changes per year. Assume they only change during working hours and that gives you 40 changes per minute.

    4. Yes, but that's not the primary concern, just a nice feature.

    5. Moot point, since the index would probably never be used, but b-tree's aren't that inefficient.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    2. Rows-shmows -- it's blocks that count. If the rows are distributed evenly then that's probably 100% block retrieval, so a full-scan would be faster.
    Well i would like to add to dave.. Even lets assume 20% rows take 20% of blocks.. then also FTS will out perform Bit map conversion to ROWIDs and Table Acces by ROWID.. !

    And if done FTS with parallel option then bingo ..

    Only case when i would prefer index & also usage of index should make sense is "When 1+ out of 10 values occupy to less blocks compared to rest 9- (may be of ratio 1:1000) and are frequently used"

    (PS I assume dmbrc 32 or more).

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Originally posted by slimdave
    Well, I'm going to have to go ahead and disagree with you there.

    1. It tells you that it's an academic question not a real life one. 5 million rows, but only 100 new rows per month? Gimme a break.

    2. Rows-shmows -- it's blocks that count. If the rows are distributed evenly then that's probably 100% block retrieval, so a full-scan would be faster.

    3. Update it once per year per row, which would meet a definition of infrequent, and you've still got 5 million changes per year. Assume they only change during working hours and that gives you 40 changes per minute.

    4. Yes, but that's not the primary concern, just a nice feature.

    5. Moot point, since the index would probably never be used, but b-tree's aren't that inefficient.
    good answer..:-) is this the reason why you dont want to do ur
    OCP ;-)dave you might even flunk :-D

    regards
    Hrishy

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by hrishy
    good answer..:-) is this the reason why you dont want to do ur
    OCP ;-)dave you might even flunk :-D

    regards
    Hrishy
    You're probably right -- born to flunk. Stupid academic crap -- you just have to double-guess what set of incorrect beliefs the questioners have.
    Last edited by slimdave; 06-07-2004 at 12:19 AM.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    you just have to double-guess what set of incorrect beliefs the questioners have.
    Most of the questioners than not think Index Scans are always good... Dave I think you would flunk for this matter
    Last edited by abhaysk; 06-07-2004 at 03:32 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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