Topic
  • 4 replies
  • Latest Post - ‏2014-03-20T13:55:09Z by charleswilt
charleswilt
charleswilt
23 Posts

Pinned topic EVI not being used

‏2014-03-19T14:32:31Z |

I built a query:

select 
         sum(case when kittyp > cast(1 as numeric(1,0)) then 1 else 0 end) as nbrCtld
         , sum(case when kittyp <= cast(1 as numeric(1,0)) then 1 else 0 end) as nbrNonCtld
from olsdta.koritdor_t
where kordno = 2277883 and kvoidc = ' ';

and an EVI that I'd hope would be useful.

CREATE ENCODED VECTOR INDEX DVPCMW.KORITDOR_XB 
ON OLSDTA.KORITDOR_ORDER_DETAILS ( KORDNO ASC , KITTYP ASC ) 
                    where KVOIDC = ' '  
WITH 0 DISTINCT VALUES ;
 

Sure enough, the EVI was used to satisfy the query.  However, in VE, I notice the following:

Aggregation Section, COUNT columns showed:

 Count(Case When KORITDOR_T_1.KITTYP>Cast(1 AS Numeric(1,0)) Then 1 Else 0 End), Count(Case When KORITDOR_T_1.KITTYP<=Cast(1 AS Numeric(1,0)) Then 1 Else 0 End)

So I though, let's try using COUNT directly...

select 
         count(case when kittyp > cast(1 as numeric(1,0)) then 1 end) as nbrCtld
         , count(case when kittyp <= cast(1 as numeric(1,0)) then 1 end) as nbrNonCtld
from olsdta.koritdor_t
where kordno = 2277883 and kvoidc = ' ';
 

To my surprise, the EVI wasn't used.  Instead the system built a temporary index.

I'm perfectly happy using the first form, but for my own understanding I'm wondering if somebody could explain why the EVI wasn't used for the second form.

Note: I'm also playing with including aggregates in the EVI itself.

Thanks!

Charles

  • NickLawrence
    NickLawrence
    69 Posts

    Re: EVI not being used

    ‏2014-03-19T19:30:35Z  

    Is there really a significant performance difference between the two plans?

    I'm not an optimizer expert, but I could easily see the optimizer deciding that kordno = 2277883 and kvoidc = ' ' is expected to eliminate a large number of rows - and an radix index over the columns would be beneficial...maybe better than a selective EVI.

    Selective indexes are (in general) less likely to be used to implement a query, given that they are less likely to be useful, given they don't have all of the rows included in the index. In addition, I believe that the optimizer does not normally re-optimize a query to use a selective index when an ODP is reused, even if host variables or parameter marker values change to allow the use of the index.

     

  • charleswilt
    charleswilt
    23 Posts

    Re: EVI not being used

    ‏2014-03-19T20:02:30Z  

    Is there really a significant performance difference between the two plans?

    I'm not an optimizer expert, but I could easily see the optimizer deciding that kordno = 2277883 and kvoidc = ' ' is expected to eliminate a large number of rows - and an radix index over the columns would be beneficial...maybe better than a selective EVI.

    Selective indexes are (in general) less likely to be used to implement a query, given that they are less likely to be useful, given they don't have all of the rows included in the index. In addition, I believe that the optimizer does not normally re-optimize a query to use a selective index when an ODP is reused, even if host variables or parameter marker values change to allow the use of the index.

     

    Nick, 

    Using the EVI is about 4x or 5x faster...but we're talking less than a 1s for either. :)

    The question is more trying to understand why my seemingly perfect EVI isn't used for COUNT but is for SUM.  When I know EVI's are great for counts. The WHERE kordno = 2277883 and kvoidc = ' ' is used in both places.  and even the EVI was built with WHERE KVOIDC = ' '.

    Also, here's the actual query that uses the EVI (note the ELSE 0)

    select 
             sum(case when kittyp > cast(1 as numeric(1,0)) then 1 else 0 end) as nbrCtld
             , sum(case when kittyp <= cast(1 as numeric(1,0)) then 1 else 0 end) as nbrNonCtld
    from olsdta.koritdor_t
    where kordno = 2277883 and kvoidc = ' ';
     

     

     

  • B.Hauser
    B.Hauser
    282 Posts

    Re: EVI not being used

    ‏2014-03-20T06:39:32Z  

    Nick, 

    Using the EVI is about 4x or 5x faster...but we're talking less than a 1s for either. :)

    The question is more trying to understand why my seemingly perfect EVI isn't used for COUNT but is for SUM.  When I know EVI's are great for counts. The WHERE kordno = 2277883 and kvoidc = ' ' is used in both places.  and even the EVI was built with WHERE KVOIDC = ' '.

    Also, here's the actual query that uses the EVI (note the ELSE 0)

    select 
             sum(case when kittyp > cast(1 as numeric(1,0)) then 1 else 0 end) as nbrCtld
             , sum(case when kittyp <= cast(1 as numeric(1,0)) then 1 else 0 end) as nbrNonCtld
    from olsdta.koritdor_t
    where kordno = 2277883 and kvoidc = ' ';
     

     

     

    Whether a binary radix tree index or an EVI is used depends quite often from the number of rows being returned.

    For a small amount (up to 15-20%) of the rows a binary radix tree index may be preferred.

    For around 20-80% of the rows an EVIs (instead of a table scan/probe) may be preferred.

    I assume with your query you'll only select a small number of rows, why a (temporary) binary index is used.

    May be an EVI access is used if you add include clauses for the aggregate functions.

    Birgitta

     

    Updated on 2014-03-20T06:41:31Z at 2014-03-20T06:41:31Z by B.Hauser
  • charleswilt
    charleswilt
    23 Posts

    Re: EVI not being used

    ‏2014-03-20T13:55:09Z  
    • B.Hauser
    • ‏2014-03-20T06:39:32Z

    Whether a binary radix tree index or an EVI is used depends quite often from the number of rows being returned.

    For a small amount (up to 15-20%) of the rows a binary radix tree index may be preferred.

    For around 20-80% of the rows an EVIs (instead of a table scan/probe) may be preferred.

    I assume with your query you'll only select a small number of rows, why a (temporary) binary index is used.

    May be an EVI access is used if you add include clauses for the aggregate functions.

    Birgitta

     

    Birgitta,

    This uses the EVI

    select 
             sum(case when kittyp > cast(1 as numeric(1,0)) then 1 else 0 end) as nbrCtld
             , sum(case when kittyp <= cast(1 as numeric(1,0)) then 1 else 0 end) as nbrNonCtld
    from olsdta.koritdor_t
    where kordno = 2277883 and kvoidc = ' ';

    This doesn't

    select 
             count(case when kittyp > cast(1 as numeric(1,0)) then 1 end) as nbrCtld
             , count(case when kittyp <= cast(1 as numeric(1,0)) then 1 end) as nbrNonCtld
    from olsdta.koritdor_t
    where kordno = 2277883 and kvoidc = ' ';

    Both give the same results.

    I'm trying to understand the different.  Especial given the the SQE replaces SUM with COUNT anyway.

    Charles