Query doing table scan when added simple predicates

[Arun Srinivasan]

Hi
I was using a query previously, that was efficient
select * from table where pred1 and pred2 and pred3;

Later I was asked to introduce new ones, but they were not based on
table columns but variables declared in SP.

select * from table where pred1 and pred2 and pred3 and variable1
='number1 and variable2 =number2;

so in turn this translates to pure mathematical comparison, sometimes
simple like 2=2,
I;ve added only 2 of them in the ex above, but actually there are 13
(does this influence the path, duh!!). Anyways, what happened was,
after they were added, it started doing a table scan, and was very
very inefficient. Later, I converted the additional predicates into
simple if loops, and executed the original query, and SP was very
fast.
Is there any database logic connected to doing a table scan? or any
registry/variable setting I can do so db2 doesnt do the table scan?
Please advise, since my team has done this mistake in many places,
and rolling back is going to be a pain in backside.

[Serge Rielau]

Re: Query doing table scan when added simple predicates

Arun Srinivasan wrote:

Hi
I was using a query previously, that was efficient
select * from table where pred1 and pred2 and pred3;
>
Later I was asked to introduce new ones, but they were not based on
table columns but variables declared in SP.
>
select * from table where pred1 and pred2 and pred3 and variable1
='number1 and variable2 =number2;
>
so in turn this translates to pure mathematical comparison, sometimes
simple like 2=2,
I;ve added only 2 of them in the ex above, but actually there are 13
(does this influence the path, duh!!). Anyways, what happened was,
after they were added, it started doing a table scan, and was very
very inefficient. Later, I converted the additional predicates into
simple if loops, and executed the original query, and SP was very
fast.
Is there any database logic connected to doing a table scan? or any
registry/variable setting I can do so db2 doesnt do the table scan?
Please advise, since my team has done this mistake in many places,
and rolling back is going to be a pain in backside.

Which version and platform are you on?
New versions of Db2 for LUW typically "push out" "constant predicates".
That is:
SELECT * FROM T WHERE pred(T) AND pred(context)
is turned into:
SELECT * FROM (SELECT 1 FROM VALUES(1) WHERE pred(context)), (SELECT *
FROM T WHERE pred(T))
with the constant pred being the outer of a nested loop join.
You should be able to see this in a db2exfmt output

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Arun Srinivasan]

Re: Query doing table scan when added simple predicates

I am really surprised at the following explains.

1. The correct one, with just one constant predicate check.

select ri_indv_id

from elg.artelgf

where client_cd ='300' and segment_type_cd ='003' and carrier_cd =

'60054' and ri_indv_id =11233 and covpf_rf ='MED' and
--- below is the only constant predicate check
value(

'01-01-2009' , CURRENT DATE)CURRENT DATE - 30 days

fetch first 1 rows only





Section Code Page = 1252



Estimated Cost = 38.574055

Estimated Cardinality = 0.047454



( 3) Table Constructor

| 1-Row(s)

( 3) Residual Predicate(s)

| #Predicates = 1

( 2) Nested Loop Join

( 2) | Access Table Name = ELG.ARTELGF ID = 47,4

| | Index Scan: Name = ELG.PK_ARTELGF2 ID = 6

| | | Regular Index (Not Clustered)

| | | Index Columns:

| | | | 1: CLIENT_CD (Ascending)

| | | | 2: SEGMENT_TYPE_CD (Ascending)

| | | | 3: CARRIER_CD (Ascending)

| | | | 4: RI_INDV_ID (Ascending)

| | | | 5: COVPF_RF (Ascending)

| | | | 6: GROUP_NUM (Ascending)

| | | | 7: POLICY_NUM (Ascending)

| | | | 8: POLICY_START_DT (Ascending)

| | | | 9: CARRIER_OFFICE_ CD (Ascending)

| | | | 10: SGSRC_RF (Ascending)

| | | | 11: QHIT_IND (Include Column)

| | #Columns = 0

| | Compressed Table

| | Skip Inserted Rows

| | Skip Deleted Keys

| | Skip Deleted Rows

| | #Key Columns = 5

| | | Start Key: Inclusive Value

| | | | | 1: '300 '

| | | | | 2: '003'

| | | | | 3: '60054'

| | | | | 4: 000000011233

| | | | | 5: 'MED'

| | | Stop Key: Inclusive Value

| | | | | 1: '300 '

| | | | | 2: '003'

| | | | | 3: '60054'

| | | | | 4: 000000011233

| | | | | 5: 'MED'

| | Index-Only Access

| | Index Prefetch: None

| | Lock Intents

| | | Table: Intent Share

| | | Row : Next Key Share

( 1) Return Data to Application

| #Columns = 1



End of section





Optimizer Plan:



RETURN

( 1)

|

NLJOIN

( 2)

/ \

TBSCAN IXSCAN

( 3) ( 2)

| / \

TFunc: Index: Table:

SYSIBM ELG ELG

GENROW PK_ARTELGF2 ARTELGF

2. Here we have the same query but havin 2 const predicate checks
instead of one and see what our db2 engine comes up with.
select ri_indv_id

from elg.artelgf

where client_cd ='300' and segment_type_cd ='003' and carrier_cd =

'60054' and ri_indv_id =11233 and covpf_rf ='MED' and
--below are two const predicate checks instead of 1
value(

'01-01-2009' , CURRENT DATE)CURRENT DATE - 30 days and ( (

'01-01-1999' between '01-01-2000' and '01-01-2009' AND days(

value('01-01-2009' , current date))- days('01-01-1999' )>=

30 ))

fetch first 1 rows only





Section Code Page = 1252



Estimated Cost = 0.000101

Estimated Cardinality = 0.000000



( 2) Table Constructor

| N-Rows

( 2) Residual Predicate(s)

| #Predicates = 1

( 1) Return Data to Application

| #Columns = 1



End of section





Optimizer Plan:



RETURN

( 1)

|

TBSCAN

( 2)

|

TFunc:

SYSIBM

GENROW

[Arun Srinivasan]

Re: Query doing table scan when added simple predicates

On Nov 14, 2:17 pm, Arun Srinivasan <arunro...@gmai l.comwrote:

I am really surprised at the following explains.
>
1. The correct one, with just one constant predicate check.
>
select ri_indv_id
>
  from elg.artelgf
>
  where client_cd ='300' and segment_type_cd ='003' and carrier_cd =
>
          '60054' and ri_indv_id =11233 and covpf_rf ='MED'and
--- below is the only constant predicate check
          value(
>
          '01-01-2009' , CURRENT DATE)CURRENT DATE - 30 days
>
  fetch first 1 rows only
>
Section Code Page = 1252
>
Estimated Cost = 38.574055
>
Estimated Cardinality = 0.047454
>
(    3) Table Constructor
>
        |  1-Row(s)
>
(    3) Residual Predicate(s)
>
        |  #Predicates = 1
>
(    2) Nested Loop Join
>
(    2) |  Access Table Name = ELG.ARTELGF  ID = 47,4
>
        |  |  Index Scan:  Name = ELG.PK_ARTELGF2  ID = 6
>
        |  |  |  Regular Index (Not Clustered)
>
        |  |  |  Index Columns:
>
        |  |  |  |  1: CLIENT_CD (Ascending)
>
        |  |  |  |  2: SEGMENT_TYPE_CD (Ascending)
>
        |  |  |  |  3: CARRIER_CD (Ascending)
>
        |  |  |  |  4: RI_INDV_ID (Ascending)
>
        |  |  |  |  5: COVPF_RF (Ascending)
>
        |  |  |  |  6: GROUP_NUM (Ascending)
>
        |  |  |  |  7: POLICY_NUM (Ascending)
>
        |  |  |  |  8: POLICY_START_DT (Ascending)
>
        |  |  |  |  9: CARRIER_OFFICE_ CD (Ascending)
>
        |  |  |  |  10: SGSRC_RF (Ascending)
>
        |  |  |  |  11: QHIT_IND (Include Column)
>
        |  |  #Columns = 0
>
        |  |  Compressed Table
>
        |  |  Skip Inserted Rows
>
        |  |  Skip Deleted Keys
>
        |  |  Skip Deleted Rows
>
        |  |  #Key Columns = 5
>
        |  |  |  Start Key: Inclusive Value
>
        |  |  |  |  |  1: '300  '
>
        |  |  |  |  |  2: '003'
>
        |  |  |  |  |  3: '60054'
>
        |  |  |  |  |  4: 000000011233
>
        |  |  |  |  |  5: 'MED'
>
        |  |  |  Stop Key: Inclusive Value
>
        |  |  |  |  |  1: '300  '
>
        |  |  |  |  |  2: '003'
>
        |  |  |  |  |  3: '60054'
>
        |  |  |  |  |  4: 000000011233
>
        |  |  |  |  |  5: 'MED'
>
        |  |  Index-Only Access
>
        |  |  Index Prefetch: None
>
        |  |  Lock Intents
>
        |  |  |  Table: Intent Share
>
        |  |  |  Row  : Next Key Share
>
(    1) Return Data to Application
>
        |  #Columns = 1
>
End of section
>
Optimizer Plan:
>
         RETURN
>
         (   1)
>
           |
>
         NLJOIN
>
         (   2)
>
        /      \
>
 TBSCAN         IXSCAN
>
 (   3)         (   2)
>
   |           /      \
>
 TFunc:  Index:       Table:
>
 SYSIBM  ELG          ELG
>
 GENROW  PK_ARTELGF2  ARTELGF
>
2. Here we have the same query but havin 2 const predicate checks
instead of one and see what our db2 engine comes up with.
 select ri_indv_id
>
  from elg.artelgf
>
  where client_cd ='300' and segment_type_cd ='003' and carrier_cd =
>
          '60054' and ri_indv_id =11233 and covpf_rf ='MED'and
--below are two const predicate checks instead of 1
 value(
>
          '01-01-2009' , CURRENT DATE)CURRENT DATE - 30 days and ( (
>
          '01-01-1999' between '01-01-2000' and '01-01-2009' AND days(
>
          value('01-01-2009' , current date))- days('01-01-1999' )>=
>
          30 ))
>
  fetch first 1 rows only
>
Section Code Page = 1252
>
Estimated Cost = 0.000101
>
Estimated Cardinality = 0.000000
>
(    2) Table Constructor
>
        |  N-Rows
>
(    2) Residual Predicate(s)
>
        |  #Predicates = 1
>
(    1) Return Data to Application
>
        |  #Columns = 1
>
End of section
>
Optimizer Plan:
>
 RETURN
>
 (   1)
>
   |
>
 TBSCAN
>
 (   2)
>
   |
>
 TFunc:
>
 SYSIBM
>
 GENROW

I got it, actually db2 is far intelligent than what I thought :-p the
reason for this behaviour being, since one of the
constant predicates check was false, it didnt even go to the real
query.
Hail DB2,

[Serge Rielau]

Re: Query doing table scan when added simple predicates

Arun Srinivasan wrote:

>Optimizer Plan:
>>
> RETURN
>>
> ( 1)
>>
> |
>>
> NLJOIN
>>
> ( 2)
>>
> / \
>>
> TBSCAN IXSCAN
>>
> ( 3) ( 2)
>>
> | / \
>>
> TFunc: Index: Table:
>>
> SYSIBM ELG ELG
>>
> GENROW PK_ARTELGF2 ARTELGF

That's the push out I described (GENROW == VALUES).

>Optimizer Plan:
>>
> RETURN
>>
> ( 1)
>>
> |
>>
> TBSCAN
>>
> ( 2)
>>
> |
>>
> TFunc:
>>
> SYSIBM
>>
> GENROW

>
I got it, actually db2 is far intelligent than what I thought :-p the
reason for this behaviour being, since one of the
constant predicates check was false, it didnt even go to the real
query.
Hail DB2,

Indeed.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab