Professional Web Applications Themes

Optimizer using MDC block index before accessing RID index - IBM DB2

Hello, I have some large tables (over 100M rows on a non-partitioned database) that each have at least one good candidate for an MDC dimension. Am I right in assuming that if a query referencing an MDC table contains two predicates, one on the MDC dimension, and the other referencing a column in a RID index, the optimizer will only use the RID index to examine pages within qualifying MDC extents/blocks? Without MDC, I've often needed to include a commonly searched column in multiple indexes in order to flexible searches. My plan for MDC is to reduce the cost of ...

  1. #1

    Default Optimizer using MDC block index before accessing RID index

    Hello,

    I have some large tables (over 100M rows on a non-partitioned
    database) that each have at least one good candidate for an MDC
    dimension. Am I right in assuming that if a query referencing an MDC
    table contains two predicates, one on the MDC dimension, and the other
    referencing a column in a RID index, the optimizer will only use the
    RID index to examine pages within qualifying MDC extents/blocks?

    Without MDC, I've often needed to include a commonly searched column
    in multiple indexes in order to flexible searches. My plan for MDC is
    to reduce the cost of many searches and simplify the indexes by
    organizing the table on at least one MDC dimension, if only to serve
    as an intermediate step that will refine the list of pages accessed by
    the RID index. Of course, I'd be willing to add extra MDC dimensions
    when appropriate, but most of the time the cardinality is too high for
    additional dimensions to be practical.

    This is all based on my understanding (which is possibly flawed) that
    there is no need to include an MDC dimension in any RID index, since
    the optimizer will always eliminate entire extents that don't match
    the MDC column value specified in the predicate.

    Does this sound like a reasonable approach?

    Thanks,

    Fred
    Fred Guest

  2. #2

    Default Re: Optimizer using MDC block index before accessing RID index

    Fred wrote: 

    This is likely the case. Also note that the optimizer can perform
    IXAND and IXOR operations between block and RID indexes to further
    narrow down the actual pages that need to be scanned.
     

    I don't think that you can say "always", because the optimizer's choice
    of access plan depends on the selectivity of the predicates. Thus if
    the selectivity of your predicate is low, perhaps the optimizer may
    choose to scan RID indexes and/or the table itself.








    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
    Ian Guest

  3. #3

    Default Re: Optimizer using MDC block index before accessing RID index

    Please refer to following doent published in DB2 Magazine.
    It examines MDC feature in detail and helps you plan your table, MDC
    attribute selection.

    It also compares MDC v/s non-MDC query plans with some real queries, so
    those could give you an idea about how optimizer works!

    http://www.db2mag.com/db_area/archives/2003/q2/welgan.shtml

    nospam@nospam.com Guest

  4. #4

    Default Re: Optimizer using MDC block index before accessing RID index

    <com> wrote in message
    news:blf6rp$kkk$austin.ibm.com... 
    The problem I see with this article is that they did not compare the use of
    an MDC to the use of a multi-column clustered or non-clustered index. To
    quote:

    "The MDC feature created an additional composite block index for the
    two-dimensional LINEITEM table on the columns L_SHIPMODE , L_SHIPDATE .
    There was no composite index on the corresponding columns on the non-MDC
    X_LINEITEM table."

    Maybe I am missing something but this does note seem to be a fair test of
    MDC vs. RID Index performance.

    Mark Farnsworth
    InfoStar Solutions, LLC
    info_star at domain qw_est.net
    (remove "domain" and all underscores)


    Mark Guest

Similar Threads

  1. ViewStack index change block other events?
    By drkshih in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: July 19th, 04:43 PM
  2. Replies: 4
    Last Post: August 3rd, 03:11 PM
  3. Index Topics and Index References
    By Tom_Cusick@adobeforums.com in forum Adobe Indesign Windows
    Replies: 2
    Last Post: July 8th, 07:20 PM
  4. How to avoid accessing row values with hard coded index
    By Microsoft in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: October 3rd, 10:04 PM
  5. Newb query: index.htm & index.php & the server default
    By Lab309 in forum PHP Development
    Replies: 7
    Last Post: September 22nd, 02:08 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139