Query Performance

[db2udbgirl]

Is this possible to tune this query further :
Env : DB2 UDB 8.2 on AIX 5.3, Non partitioned tables

Query:
SELECT
ETL.T00601.*
FROM
ETL.T00601,
ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN
where
ETL.T00601.SVC_BUSNS_ASCT_CD =
ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN.BUSNS_ASCT_CD
AND
(
SUBSTR(VEH_IDENT_NBR,1,3) in ('9BG','8AG','6G1') AND
SUBSTR(VEH_IDENT_NBR,9,1) not in ('P','R','S','T','V') )
OR
(SUBSTR(VEH_IDENT_NBR,1,3) not in ('9BG','8AG','6G1') AND
SUBSTR(VEH_IDENT_NBR,10,1) in
('W','X','Y','1','2','3','4','5','6','7','8')
) "
I have the following indexes on these 2 tables
1) i4 ON ETL.T00601 ( VEH_IDENT_NBR ,SVC_BUSNS_ASCT_CD )
2) i2 ON ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN ( BUSNS_ASCT_CD )

After updating the system catalog statistics I found out that
CLUSTERRATIO is less that 60 for index i4 ( I tried to have index on
different columns but it didn't help to improve the CLUSTERRATION)

Access plan is generated as below
db2 set current explain mode explain
db2 explain all with snapshot for "SELECT ETL.T00601.* FROM
ETL.T00601,ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN where
ETL.T00601.SVC_BUSNS_ASCT_CD =
ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN.BUSNS_ASCT_CD AND (
SUBSTR(VEH_IDENT_NBR,1,3) in ('9BG','8AG','6G1') AND
SUBSTR(VEH_IDENT_NBR,9,1) not in ('P','R','S','T','V') ) OR
(SUBSTR(VEH_IDENT_NBR,1,3) not in ('9BG','8AG','6G1') AND
SUBSTR(VEH_IDENT_NBR,10,1) in
('W','X','Y','1','2','3','4','5','6','7','8')) "
db2exfmt -d card -g TIC -e card30 -f 0 -w -1 -1 -# 0 -o laam_expln4.out

Part of laam_expln4.out
Access Plan:
-----------
Total Cost: 1.04327e+06
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
4.03973e+07
DTQ
( 2)
1.04327e+06
28958
|
4.03973e+07
NLJOIN
( 3)
1.02434e+06
28958
/-----+-----\
301024 134.2
TBSCAN IXSCAN
( 4) ( 5)
58885.7 106.036
28949 8
| |
3.0102e+06 1342
TABLE: ETL INDEX: CARD30
T00601 I2
Always ETL.T00601 goes for tablescan because of low CLUSTERRATIO( Am I
right ?? )
Any help is appreciated to improve this query performance.

[db2udbgirl]

Guys, I'm sorry just now found out that this table is parititioned. So
this table T06001 is a partitioned table.

[Serge Rielau]

Try this. The idea is to have DB2 probe for the end result
after it knows the qualifying rows.
Now, there is a chance that DB2 outsmarts us and drops the new join.

SELECT
Y.*
FROM
(SELECT
ETL.T00601.SVC_BUSNS_ASCT_CD, ETL.T00601.VEH_IDENT_NBR
FROM
ETL.T00601,
ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN
where
ETL.T00601.SVC_BUSNS_ASCT_CD =
ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN.BUSNS_ASCT_CD
AND
(
SUBSTR(VEH_IDENT_NBR,1,3) in ('9BG','8AG','6G1') AND
SUBSTR(VEH_IDENT_NBR,9,1) not in ('P','R','S','T','V') )
OR
(SUBSTR(VEH_IDENT_NBR,1,3) not in ('9BG','8AG','6G1') AND
SUBSTR(VEH_IDENT_NBR,10,1) in
('W','X','Y','1','2','3','4','5','6','7','8')
)) AS X,
ETL.T00601 AS Y
WHERE X.SVC_BUSNS_ASCT_CD = Y.SVC_BUSNS_ASCT_CD
AND X.VEH_IDENT_NBR = Y.VEH_IDENT_NBR;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[db2udbgirl]

Thanks, Access plan shows less timerons now to run this query so I
believe that it would give a better performance. New access plan is
here
Access Plan:
-----------
Total Cost: 176642
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
4.03979e+06
DTQ
( 2)
176642
37150.7
|
4.03979e+06
NLJOIN
( 3)
174749
37150.7
/-------+------\
30102.9 134.2
NLJOIN IXSCAN
( 4) ( 9)
78097.5 106.036
37141.7 8
/---+---\ |
301024 0.100001 1342
TBSCAN IXSCAN INDEX: CARD30
( 5) ( 8) I2
59319.9 25.7384
28949 2
| |
301024 3.0102e+06
I've a quick question. I have 3 million rows in ETL.T00601 table and
1500 rows in ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN table but when I
execute this query it I expected it to fetch less than 3 million rows
but it has fetched so far around 22 Million row which puzzles me. Any
idea?

[Serge Rielau]

db2udbgirl wrote:

Thanks, Access plan shows less timerons now to run this query so I
believe that it would give a better performance. New access plan is
here
Access Plan:
-----------
Total Cost: 176642
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
4.03979e+06
DTQ
( 2)
176642
37150.7
|
4.03979e+06
NLJOIN
( 3)
174749
37150.7
/-------+------\
30102.9 134.2
NLJOIN IXSCAN
( 4) ( 9)
78097.5 106.036
37141.7 8
/---+---\ |
301024 0.100001 1342
TBSCAN IXSCAN INDEX: CARD30
( 5) ( 8) I2
59319.9 25.7384
28949 2
| |
301024 3.0102e+06
I've a quick question. I have 3 million rows in ETL.T00601 table and
1500 rows in ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN table but when I
execute this query it I expected it to fetch less than 3 million rows
but it has fetched so far around 22 Million row which puzzles me. Any
idea?

Hmm. This is not the plan I expected.
I was aiming for a NLJOIN between to covering indexes following by join
doing an ISCAN/FETCH

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[db2udbgirl]

But still gives a better performance to me though :-)
Do you have any idea about my other question?

[Serge Rielau]

db2udbgirl wrote:

But still gives a better performance to me though :-)
Do you have any idea about my other question?

no clue

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Chris]

I was looking at your where clause, and I'm wondering if you add
another set of parenthesis' in there if it would clean up the results.

where
ETL.T00601.SVC_BUSNS_ASCT_CD =
ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN.BUSNS_ASCT_CD
AND
(
(
SUBSTR(VEH_IDENT_NBR,1,3) in ('9BG','8AG','6G1')
AND SUBSTR(VEH_IDENT_NBR,9,1) not in
('P','R','S','T','V')
)
OR
(
SUBSTR(VEH_IDENT_NBR,1,3) not in
('9BG','8AG','6G1')
AND SUBSTR(VEH_IDENT_NBR,10,1) in
('W','X','Y','1','2','3','4','5','6','7','8')
)
)

I'm curious what DB2 would do without these extra set of parens - I
would expect it to join the two tables AND do the first set of
filtering, and then discard that set of filters (for the OR clause) and
do the second set of filters. Which would result in a very very large
(and incorrect) result set. I'm not positive, though.

-Chris

[db2udbgirl]

ok No problem, That was due to a studpid mistake in my query. I have
resolved it. Thanks for your timely help.

[db2udbgirl]

Yes, I missed that extra set of paranthesis earlier as pointed by Chris.