Pavan:
Create a new index with different order (ODS_TX_TS, INT_ADDRESS_ID,
ODS_TX_TYP, ROWVERSION) and 'runstats on table <tabname> with distribution
and detailed indexes all'
Does the query still doing table scan?
Suresh G.
"pavan" <db2udbdba-ezmlmshield-x6575845.[Email address protected]
10/30/2006 03:28 PM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
why tablescan - urgent.
Gurus,
I have a 2 tablescans on the following query. Cost is approx 155k
timerons. I tried writing the sub-query in the where clause rather than
joining to the subquery. It performs even worse.
I have the following index on the table (Pl note that I am using only one
table in my sql).
(INT_ADDRESS_ID, ODS_TX_TS, ODS_TX_TYP, ROWVERSION) -- as part of primary
key. I see here that reverse scans are disabled. But, then, I tried doing
an explain on the same query on test machine on which the same index is
created. There I see the tablescan. Stats are up-to-date on both
databases. The only difference I know is the amount of data.
On prod -- 2 M rows.
On test -- 60K rows.
Can anyone throw light on why do I see a tablescan on prod while I see
the ind scan on test. Please note that the timestamp check condition
itself brings down then number of qualified rows to 6000 from 2M rows on
prod. I should 100% see an index scan when the # of rows in subset is so
low when compared to the no. of rows on table. Highly appreciate all
inputs.
SELECT V1.INT_ADDRESS_ID
,V1.VERS_CNTL_MAJ_MIN
,V1.DELETED_F
,V1.ADDRESS1
,V1.ADDRESS2
,V1.ADDRESS3
,V1.ADDRESS4
,V1.CITY
,V1.STATE_PROV_CD
,V1.COUNTY
,V1.HOUSE_NBR
,V1.POSTAL_CODE
,V1.ODS_TX_TS
,V1.COUNTRY_ID
,V1.ODS_TX_TYP
FROM POLICY_NCR.POL_INT_ADDRESS_V_NCR V1
INNER JOIN
(
SELECT LL.INT_ADDRESS_ID
,LL.VERS_CNTL_MAJ_MIN
,MAX (LL.ROWVERSION) AS MAXROWVERSION
FROM POLICY_NCR.POL_INT_ADDRESS_V_NCR LL
WHERE LL.ODS_TX_TS BETWEEN '2006-10-14 20:20:20.123457'
AND '2006-10-15 20:20:20.123456'
GROUP BY LL.INT_ADDRESS_ID
,LL.VERS_CNTL_MAJ_MIN
)V2
ON
V1.ROWVERSION = V2.MAXROWVERSION
AND V1.INT_ADDRESS_ID = V2.INT_ADDRESS_ID
AND V1.VERS_CNTL_MAJ_MIN = V2.VERS_CNTL_MAJ_MIN
WHERE V1.ODS_TX_TS BETWEEN '2006-10-14 20:20:20.123457' AND
'2006-10-15 20:20:20.123456'
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
CONFIDENTIALITY NOTICE:
This is a transmission from Kohl's Department Stores, Inc.
and may contain information which is confidential and proprietary.
If you are not the addressee, any disclosure, copying or distribution or use of the contents of this message is expressly prohibited.
If you have received this transmission in error, please destroy it and notify us immediately at 262-703-7000.
CAUTION:
Internet and e-mail communications are Kohl's property and Kohl's reserves the right to retrieve and read any message created, sent and received. Kohl's reserves the right to monitor messages to or from authorized Kohl's Associates at any time
without any further consent.
DB2 & UDB email list listserv db2-l LazyDBA home page