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

Thread: Perfomance Index help

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    One of our customer wrote when I asked him why he had created a combination index on the columns that already had individual indexes and he wrote...
    index 1 - res_entity_type
    index 2 - res_level
    index 3 - res_entity_type, res_level
    index4 - res_status, res_int_ext8, res_entity_type

    What I am concerned about is index 3 !

    Part1:
    ------
    In our mwebres table, the res_entity_type column values are very skewed. We have 72 rows where
    res_entity_type = 1 and 32,456 rows where res_entity_type = 3. I added a histogram on res_entity_type so that when the query asks for rows where res_entity_type = 1, at least the optimizer will use the index for that query.
    If the query asks for rows where res_entity_type = 3, it should never use an index if the query is just based on that column. It would be more efficient to do the full table scan.

    --I know that the 1st part is correct.
    Part2:
    ------
    That is why I added indexes that include that column
    along with others. For example, for queries that request rows where res_entity_type = 3 and where res_level = 5 (which has 18 rows in our database), the optimizer will go ahead and use an index that contains those two columns.

    What I am confused is the 2nd Part. Is what he saying true/correct ?

    thanks
    Sonali
    Sonali

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Sonali,

    I believe that you can drop index #1. When queries are issued that need to use the indexed column RES_ENTITY_TYPE, they can just as easily use index#3 as they can use index#1.

    -John

  3. #3
    Join Date
    Feb 2001
    Posts
    389
    It is true but it depends on the selectivity of res_level columns , does combined index increase selectivity, clustering factor.

    If cardinality is low, think bitmap , u can have multiple indexes with not much space degradation and enough performace improvement

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    Sonali,

    Actually a better idea might be to rebuild your indexes like this :

    index 1 - res_entity_type
    index 2 - res_level
    index4 - res_status, res_int_ext8

    I did not look closesly enough the first time.

    Oracle can use 2 indexes at once, and since you have index 3 being essentially index1 + index2, yes I agree that you don't need it. Also, I think you can drop the last column off index 4 since it is already indexed in index1.

    I am assuming that your tables are analyzed and that you are using the cost based optimizer.

    -John


  5. #5
    Join Date
    Nov 2000
    Posts
    344
    be careful with bitmap indexes - you don't want to use them if the underlying table is changing very much. bitmap indexes are best in static tables (and yes with low cardinality)

  6. #6
    Join Date
    Jan 2001
    Posts
    318
    Thanks, but I still don't understand that what he wrote in Part2 is correct or not...
    Any one please help..
    I forgot to mention the index positions in my 1st post..
    index 1 - res_entity_type
    index 2 - res_level
    index 3 - res_level(1), res_entity_type(2)
    index4 - res_int_ext8(1), res_entity_type(2) , res_status(3)

    Thanks a lot
    Sonali
    Sonali

  7. #7
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    As I see it, your developer wants to be sure he gets a full table scan when he selects a value that returns many rows.

    I did a little test. I hope the results can help you.
    btw, i'm on 7.3.4

    Create table gdn ( nummer number(4), nummer number(4),tekst varchar2(20));

    create index gdn_i2 on gdn ( nummer,nummer2)

    I inserted records in the table

    nummer = 1 , nummer2 = 1, tekst = 'ONE'
    about 16000 records
    nummer = 1 , nummer2 = 2, tekst = '12'
    about 7 records
    nummer = 2 , nummer2 = 2, tekst = '2'
    about 4 records

    analyze table gdn compute statistics;
    analyze index gdn_i2 compute statistics;
    analyze table gdn compute statistics for columns nummer,nummer2;

    select count(1) from gdn where nummer=1;
    -- results in full table scan
    select count(1) from gdn where nummer=1 and nummer2=1;
    -- results in full table scan
    select count(1) from gdn where nummer=2;
    -- results in index scan using GDN_I2
    select count(1) from gdn where nummer=2 and nummer2=2
    -- results in index scan using GDN_I2

    If this is the result you developer wants to achieve,
    the index 2 is not necessary.

    Hope this helps
    Gert

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