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]

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]

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]

On Nov 14, 2:17*pm, Arun Srinivasan <[e-mail]:

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]

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