Query doing table scan when added simple predicates

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.

re: Query doing table scan when added simple predicates

Arun Srinivasan wrote: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

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

re: Query doing table scan when added simple predicates

On Nov 14, 2:17*pm, Arun Srinivasan <arunro...@gmail.comwrote: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,

re: Query doing table scan when added simple predicates

Arun Srinivasan wrote:That's the push out I described (GENROW == VALUES).
Indeed.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab