I can see a major difference just looking at the access plans. (This
doesn't include a full analysis of the detail information available.)
Your "good" run used an index to locate rows that satisfied the
predicate. The "bad" one used a tablespace scan to read the entire table
looking for qualifying rows.
This difference should have been easily seen in a visual explain. If you
still have the two statements explained, then I'd go back to the visual
tool and see if you can spot the difference.
The optimizer's decision to scan or use an index uses, as one of its
factors, its estimate of the total number of I/Os needed to satisfy the
query. Scanning always reads multiple pages with each I/O; indexes can
be assumed to read one data page per row. The optimizer incorrectly
(based on your actual results) assumed around 850k rows would be
retrieved. I'd guess that the partial statistics you gathered tipped the
optimizer into deciding that the tablespace scan would be faster. The
VOLATILE table setting will cause the optimizer to use the index even
though it would prefer to use a tablespace scan. (See ALTER TABLE in SQL
Reference V2.)
My experience with large tables and this type of query is that index
access becomes almost a certainty when the table is clustered to match
the index. The statistics must also indicate that the table is currently
clustered and not in need of reorganization.
Phil Sherman
kenfar wrote:
I've got two scenarios then for this simple query:
good:
- volatile
- stats collected with 20% sampling
- takes about 35 seconds to export 1.9 m rows
bad:
- non-volatile
- stats collected with 20% sampling
- takes about 60 minutes to export 1.9 m rows
************************************************** ****************
Here's the good explain:
******************** EXPLAIN INSTANCE ********************
Access Plan:
-----------
Total Cost: 1.25461e+07
Query Degree: 4
Rows
RETURN
( 1)
Cost
I/O
|
853509
LTQ
( 2)
1.25461e+07
508785
|
853509
FETCH
( 3)
1.25457e+07
508785
/---+---\
236.513 6.38425e+08
IXSCAN TABLE: SWARE
( 4) MF_TRM_NID_3
25.0285
1
|
6.38425e+08
INDEX: SYSIBM
SQL0402251807242
************************************************** ****************
and here's the bad one
******************** EXPLAIN INSTANCE ********************
Optimized Statement:
-------------------
SELECT 1
FROM SWARE.MF_TRM_NID_3 AS Q1
WHERE (Q1.SENSOR_TIME_ID = 1485)
Access Plan:
-----------
Total Cost: 9.53175e+06
Query Degree: 4
Rows
RETURN
( 1)
Cost
I/O
|
853509
LTQ
( 2)
9.53175e+06
5.66119e+06
|
853509
TBSCAN
( 3)
9.53141e+06
5.66119e+06
|
6.38425e+08
TABLE: SWARE
MF_TRM_NID_3