Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

EVI Index question?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • EVI Index question?

    David, Its my understanding that using more indexes with fewer columns in each index is generally more effective overall. Though a particular SQL statement might be bennefitted by a more complex index. Other statements might not be able to use that index and require more indexes to be built adding to the database overhead. Also remember EVI indexes are meant more for record selection than sorting. As for performance information, use the visual explain inside of Operations Navigator. It has GREAT information on what indexes are being used, how much CPU time the statement requires, and more... Good luck! Kevin

  • #2
    EVI Index question?

    You typically don't want more than one field per EVI. The iSeries can combine different EVI's when selecting records, so it doesn't make sense to have a part master file with one EVI by part number and another EVI by part type/part number. EVI's are effective when selectivity is high (not a "high" amount of distinct values). For example, you wouldn't want to build an EVI over a full native timestamp but you might over a date field. Chris Ringer ringerContracting@yahooo.com

    Comment


    • #3
      EVI Index question?

      Chris, you are right, the only exception may be fields that always used in search conditions together, like "year" and "month".

      Comment


      • #4
        EVI Index question?

        I have been testing some of our files using an EVI scheme on the iSeries DB2 v5r2m0. I do have a few questions for someone that might may know the answer: 1. In a particular EVI index, is it better to have fewer (or one field) fields in a particular index and have many EVI indexes? Or is it better to stack as many fields in one index as you can to reap the benefits of EVIs? 2. Is there some performance information to be gleaned from the DSPFD for "Number of unique key values" and/or Number of overflow values? Thanks in advance for you assistance. dd ddaugherty@teamair.com

        Comment


        • #5
          EVI Index question?

          If you're talking about scalar functions like max, avg, year, etc. then, yes, the query optimizer excludes EVI in those cases. So, if you wanted records for a certain year, don't use the year scalar, instead use MyYear between '01/01'03' and '12/31/03' Chris Ringer RingerContracting@yahooo.com

          Comment

          Working...
          X