OraFAQ Forum: Performance Tuning » CBO leaves out access path ?

Home » RDBMS Server » Performance Tuning » CBO leaves out access path ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CBO leaves out access path ? [message #172924]

Thu, 18 May 2006 11:26

iliakan
Messages: 1
Registered: May 2006

Junior Member

I have a table that is queried with many BETWEEN/IN combinations of conditions, so it has BITMAPS on 6 columns.
But sometimes, it is better to make INDEX RANGE scan of a multicolumn index, so there also exists such index.

The problem is that optimizer uses INDEX RANGE plan where it *should* use BITMAP. Stats are uptotime.
hinting shows better bitmap cost..

WHY optimizer chooses INDEX RANGE over bitmap ?!??

I attach 2 tracefiles, one of them "bitmap.txt" has NO_INDEX hint to disable INDEX RANGE SCAN.
When I looked into "range.txt" (no hint, index range scan), I noticed that bitmap access path was *not considered at all*.
IS that a bug or feature ? Any solution/workaround ?

Here goes the query
SELECT * FROM ti_offer_726 WHERE
meal IN (2,3,4) AND alloccat IN (7,6,5)
AND duration BETWEEN 7 AND 10
AND departure BETWEEN '18-MAY-06' AND '27-MAY-06'

The table:

CREATE TABLE TIUSER.TI_OFFER_726 (
CITY NUMBER(22,0) NOT NULL,
COUNTRY NUMBER(22,0) NOT NULL,
ROOMSIZE NUMBER(22,0) NOT NULL,
DEPARTURE DATE NOT NULL,
REAL_PRICE NUMBER(22,0) NOT NULL,
DURATION NUMBER(22,0) NOT NULL,
ID NUMBER(22,0) NOT NULL,
OPERATOR NUMBER(22,0) NOT NULL,
ALLOCATION NUMBER(22,0) NOT NULL,
ALLOCCAT NUMBER(22,0) NOT NULL,
RESORT NUMBER(22,0) NOT NULL,
MEAL NUMBER(22,0) NOT NULL,
ROOMTYPE NUMBER(22,0) NOT NULL,
ROOMVIEW NUMBER(22,0) NOT NULL,
PRICE NUMBER(22,0) NOT NULL,
CH1FROM NUMBER(22,0) NOT NULL,
CH1TO NUMBER(22,0) NOT NULL,
CH2FROM NUMBER(22,0) NOT NULL,
CH2TO NUMBER(22,0) NOT NULL,
ALLSPO NUMBER(22,0) NOT NULL,
STOP NUMBER(22,0) NOT NULL,
PARTITION_NUM NUMBER(22,0) NOT NULL,
CHILDTYPE NUMBER(22,0) NOT NULL,
PRICE_HELPER NUMBER(22,0) NULL,
DURATION_TYPE NUMBER(22,0) NULL
)

Trace files can be found here:

CBO-chosen range scan (no hints)
http://rafb.net/paste/results/z4YsvZ97.html
also attached to message.

Bitmap access path, with NO_INDEX hint for multicolumn btree
http://rafb.net/paste/results/XA549R55.html

Attachment: range.txt
(Size: 33.28KB, Downloaded 1362 times)

[Updated on: Thu, 18 May 2006 11:27]

Report message to a moderator

Previous Topic:	Can We Reduced the Time
Next Topic:	Gathering CBO Stats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 15:49:35 CST 2024