Fun with IN-lists and GENROW

You may also like...

4 Responses

  1. David Fuente says:

    Hello, Great Post !. I’m facing the same situation. I have 2 equal databases in 2 different servers, but when I execute the explain plan.. one is using the same index several times and the other is using genrow. What’s the difference? Could be statistics or anything else? Thanks. Regards.

    • Ember Crooks says:

      Do they have the same statistics and exactly the same data? My guess would be that they have different data or different statistics. I would look also at if one is much faster than the other.

  2. Anon says:

    Thanks, for the information on genrow. I tried using “or” clause instead of in-list for example “where (pred1=”value1″ or pred1=”value2″)” but it still produces genrow section.

  3. Prashant says:

    may be by now, you would have found the way to avoid multiple index scan with in-list.

    I have tried something like this and was able to avoid multiple index scan.

    Original Query :
    select pm.id_value,pm.token
    from FIN_DA_PAYMENT_MEANS pm
    where pm.ENTITY_STATUS_CREATING_DATE between ‘01.03.2017 00:00:00.000000’ and ‘01.03.2017 23:59:59.999999’
    and pm.type in (‘virtualCreditCardToken’, ‘creditCardToken’)
    for read only with ur

    Modified query :

    select pm.id_value,pm.token
    from HRSDB.FIN_DA_PAYMENT_MEANS pm join
    (values(‘virtualCreditCardToken’) union values(‘creditCardToken’) ) as v1(c1)
    on v1.c1=pm.type
    where pm.ENTITY_STATUS_CREATING_DATE between ‘2017-03-01-00.00.00.000000’ and ‘2017-03-01-23.59.59.999999’
    for read only with ur
    ;

    create temp table in the query. So far modified query didn’t give incorrect results :).

Leave a Reply

Your email address will not be published. Required fields are marked *