Query Performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

db2udbgirl - 27 Mar 2006 17:42 GMT

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.

Reply to this Message

db2udbgirl - 27 Mar 2006 17:51 GMT

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

Reply to this Message

Serge Rielau - 27 Mar 2006 18:15 GMT

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;

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

db2udbgirl - 27 Mar 2006 19:19 GMT

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?

Reply to this Message

Serge Rielau - 27 Mar 2006 19:26 GMT

> 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
[quoted text clipped - 41 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

db2udbgirl - 27 Mar 2006 19:29 GMT

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

Reply to this Message

Serge Rielau - 27 Mar 2006 19:36 GMT

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

no clue

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

db2udbgirl - 27 Mar 2006 21:01 GMT

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

Reply to this Message

db2udbgirl - 27 Mar 2006 21:06 GMT

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

Reply to this Message

Chris - 27 Mar 2006 20:21 GMT

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

Reply to this Message