I have a query in which the predictate is like this:
WHERE ln.metro_id = i_metro_id AND
l.metro_id = :b1 ANd
eld.metro_id(+) = :b1 AND
ln.status_desc = 'ACTIVE' AND
ln.daset_id = ld.daset_id (+) AND
ln.sect_id = ld.sect_id (+) AND
ln.life_id = ld.life_id (+) AND
ln.version_id = ld.version_id (+) AND
ln.daset_id = l.daset_id AND
ln.sect_id = l.sect_id AND
ln.life_id = l.life_id AND
ln.version_id = l.version_id AND
l.status_desc = 'ACTIVE' AND
l.ramp = 'Y' AND
l.daset_id = eld.daset_id (+) AND
l.sect_id = eld.sect_id (+) AND
l.life_id = eld.life_id (+) AND
l.version_id = eld.version_id (+) AND
eld.life_id IS NULL
If the table life is analyzed, the bitmap index is not used, and the optimizer choose
a full table scan, and it take hours to run.
But if the table life is not analyzed, bitmap index is used, and the query is takes
only 1 minutes.
Here is some related info from event 10053
With table life ANALYZED
***********************
Table stats Table: LIFE Alias: L
(Using composite stats)
TOTAL :: CDN: 5485935 NBLKS: 7839 TABLE_SCAN_CST: 1190 AVG_ROW_LEN: 182
-- Index stats
INDEX#: 3906609 COL#: 6 16
USING COMPOSITE STATS
TOTAL :: LVLS: 1 #LB: 268 #DK: 2 LB/K: 10 DB/K: 11 CLUF: 293
INDEX#: 3905350 COL#: 1 2 3 4 5
USING COMPOSITE STATS
TOTAL :: LVLS: 2 #LB: 23411 #DK: 5485935 LB/K: 1 DB/K: 1 CLUF: 950571
INDEX#: 3905296 COL#:
USING COMPOSITE STATS
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
INDEX#: 3905332 COL#:
USING COMPOSITE STATS
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
***********************
***************************************
SINGLE TABLE ACCESS PATH
Column: METRO_ID Col#: 5 Table: LIFE Alias: L
NDV: 15 NULLS: 0 DENS: 6.6667e-02 LO: 1 HI: 48
Column: STATUS_DES Col#: 16 Table: LIFE Alias: L
NDV: 1 NULLS: 0 DENS: 1.0000e+00
Column: RAMP Col#: 6 Table: LIFE Alias: L
NDV: 2 NULLS: 0 DENS: 5.0000e-01
TABLE: LIFE ORIG CDN: 5485935 CMPTD CDN: 182865
Access path: tsc Resc: 1190 Resp: 1190
Access path: index (equal)
INDEX#: 3906609 TABLE: LIFE
CST: 134 IXSEL: 5.0000e-01 TBSEL: 5.0000e-01
******** Bitmap access path rejected ********
Cost: 5223 Selectivity: 0
Not believed to be index-only.
BEST_CST: 1190.00 PATH: 2 Degree: 1
***************************************
With table LIFE not analyzed
***********************
Table stats Table: LIFE Alias: L
(Averaging)
PARTITION [0] CDN: 798014 NBLKS: 9770 TABLE_SCAN_CST: 1483 AVG_ROW_LEN: 100
PARTITION [1] CDN: 1673215 NBLKS: 20485 TABLE_SCAN_CST: 3110 AVG_ROW_LEN: 100
PARTITION [2] CDN: 590384 NBLKS: 7228 TABLE_SCAN_CST: 1098 AVG_ROW_LEN: 100
PARTITION [3] CDN: 494246 NBLKS: 6051 TABLE_SCAN_CST: 919 AVG_ROW_LEN: 100
PARTITION [4] CDN: 577478 NBLKS: 7070 TABLE_SCAN_CST: 1074 AVG_ROW_LEN: 100
PARTITION [5] CDN: 1160673 NBLKS: 14210 TABLE_SCAN_CST: 2157 AVG_ROW_LEN: 100
PARTITION [6] CDN: 1365282 NBLKS: 16715 TABLE_SCAN_CST: 2538 AVG_ROW_LEN: 100
PARTITION [7] CDN: 654747 NBLKS: 8016 TABLE_SCAN_CST: 1217 AVG_ROW_LEN: 100
PARTITION [8] CDN: 614969 NBLKS: 7529 TABLE_SCAN_CST: 1143 AVG_ROW_LEN: 100
PARTITION [9] CDN: 427595 NBLKS: 5235 TABLE_SCAN_CST: 795 AVG_ROW_LEN: 100
PARTITION [10] CDN: 82 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 100
PARTITION [11] CDN: 572087 NBLKS: 7004 TABLE_SCAN_CST: 1064 AVG_ROW_LEN: 100
PARTITION [12] CDN: 420162 NBLKS: 5144 TABLE_SCAN_CST: 781 AVG_ROW_LEN: 100
PARTITION [13] CDN: 322065 NBLKS: 3943 TABLE_SCAN_CST: 599 AVG_ROW_LEN: 100
PARTITION [14] CDN: 479871 NBLKS: 5875 TABLE_SCAN_CST: 892 AVG_ROW_LEN: 100
PARTITION [15] CDN: 82 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 100
PARTITION [16] CDN: 733487 NBLKS: 8980 TABLE_SCAN_CST: 1364 AVG_ROW_LEN: 100
TOTAL :: (NOT ANALYZED) CDN: 640261 NBLKS: 7838 TABLE_SCAN_CST: 1190
AVG_ROW_LEN: 100
-- Index stats
INDEX#: 3906609 COL#: 6 16
-- Index stats
INDEX#: 3906609 COL#: 6 16
PARTITION[0] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[1] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[2] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[3] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[4] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[5] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[6] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[7] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[8] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[9] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[10] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1
CLUF: 800
PARTITION[11] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1
CLUF: 800
PARTITION[12] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1
CLUF: 800
PARTITION[13] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1
CLUF: 800
PARTITION[14] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1
CLUF: 800
PARTITION[15] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1
CLUF: 800
PARTITION[16] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1
CLUF: 800
TOTAL :: (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 7838
INDEX#: 3905350 COL#: 1 2 3 4 5
PARTITION[0] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[1] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
PARTITION[2] (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF:
800
...................
Omitted here for space saving
***************************************
SINGLE TABLE ACCESS PATH
Column: METRO_ID Col#: 5 Part#: 0 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 1 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 2 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 3 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 4 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 5 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 6 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 7 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 8 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 9 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 10 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: METRO_ID Col#: 5 Part#: 11 Table: LIFE Alias: L
NO STATISTICS (using defaults)
....................... omited some lines here for space saving
Column: RAMP Col#: 6 Part#: 16 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 20008 NULLS: 0 DENS: 4.9980e-05
Column: RAMP Col#: 6 Table: LIFE Alias: L
NO STATISTICS (using defaults)
NDV: 1177 NULLS: 0 DENS: 8.4966e-04
TABLE: LIFE ORIG CDN: 640261 CMPTD CDN: 1
Access path: tsc Resc: 1190 Resp: 1190
Access path: index (stp-guess)
INDEX#: 3906609 TABLE: LIFE
CST: 1 IXSEL: 1.0000e-04 TBSEL: 1.6000e-05
******** Bitmap access path accepted ********
Cost: 16 Selectivity: 0
Not believed to be index-only.
BEST_CST: 15.80 PATH: 20 Degree: 1
***************************************
Can somebody tell me, how does Oracle get the bitmap index cost of 5223 when table
LIFE is analyzed, and cost of 16 when is not?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shao, Chunning
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
