OraFAQ Forum: Performance Tuning » SQL QUERY NOT USING BITMAP INDEX

Home » RDBMS Server » Performance Tuning » SQL QUERY NOT USING BITMAP INDEX

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

SQL QUERY NOT USING BITMAP INDEX [message #123398]

Mon, 13 June 2005 03:47

puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India

Member

HI, group

I have a Simple SQL query and its not using the bitmap index I have created on it.

Following are details which help you to understand my problem
Desc stock_transaction

Name Null? Type
----------------------------------------- -------- --------------
ID NOT NULL NUMBER(10)
STOCK_BREAK_ID NUMBER(10)
ISIN NOT NULL VARCHAR2(12)
SECURITY_NUMBER NOT NULL VARCHAR2(12)
ACCOUNT_ID VARCHAR2(5)
TRANSACTION_NUMBER NOT NULL VARCHAR2(16)
AMOUNT NOT NULL NUMBER(30,4)
MARKET_VALUE NUMBER(30,4)
DEPARTMENT_ID VARCHAR2(5)
CATEGORY_ID VARCHAR2(10)
STATUS NOT NULL VARCHAR2(1)
LEDGER_STATEMENT NOT NULL VARCHAR2(3)
CUSTOMER_ID NOT NULL VARCHAR2(3)
VALUE_DATE NOT NULL DATE
STOCK_TYPE NOT NULL VARCHAR2(3)
SEC_LONGNAME NOT NULL VARCHAR2(100)
REMINDER DATE
BOOKING_DATE NOT NULL DATE
TRANSACTION_OWNER_ID VARCHAR2(5)
FUNCTIONAL_OWNER_ID VARCHAR2(5)
BUSINESS_OWNER_ID VARCHAR2(5)
ACCOUNT_NO VARCHAR2(15)
LAST_UPDATE DATE
EE2_USER_ID VARCHAR2(10)
Data --> 80,000

Select department_id, count(*) from stock_transaction group by department_id
DEPAR COUNT(*)
----- ----------
CAIN 20000
CAST 20000
CTRL 20000
SDM 19999
VAUL 1

Index --> Bitmap index on Department_ID

Select * from stock_transaction where DEPARTMENT_ID = 'VAUL'

Index USEAGE -- NO
Explain plan-- Full table Scan

Select department_id from stock_transaction where DEPARTMENT_ID = 'VAUL'
Index USEAGE -- Yes
Explain Plan -- Index used

INDEX_NAME --> TEST_IDX
INDEX_TYPE --> BITMAP
TABLE_OWNER --> XXBEAT
TABLE_NAME --> STOCK_TRANSACTION

I'm stuck can you please help me out from this.

Why my index is not been used.
what will i do so that my index work.
Why my index is working when I have entered department_id in select statement.

Please answer my queries...

Puneet

Attachment: stock_transaction_details.txt
(Size: 2.23KB, Downloaded 1515 times)

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123427 is a reply to message #123398]

Mon, 13 June 2005 07:50

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

How many records are in this table?

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123428 is a reply to message #123398]

Mon, 13 June 2005 07:53

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

Data ---> 80,000 rows?

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123431 is a reply to message #123398]

Mon, 13 June 2005 08:02

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

What DB version are you using? whats the optimizer mode? Are the stats gathered?

Seems like you are using the cost based optimizer (CBO) and it is doing absolutely the most correct thing possible.

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123435 is a reply to message #123398]

Mon, 13 June 2005 08:28

puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India

Member

Hi,

What DB version are you using? -- Oracle 9.2
Are the stats gathered? -- Yes
Seems like you are using the cost based optimizer (CBO) and it is doing absolutely the most correct thing possible.
-- what you mean by this..

As far as I know and books i refered they say if you are having unique records then go for Btree index else go for BITMAP.

here in department ID i have less distinct records...

and just recently I found its working with BTREE.
I'm confused can yu help me out.. please I have to give presentation on this and... I'm confused.

Puneet sachar

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123460 is a reply to message #123398]

Mon, 13 June 2005 10:17

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

The optimizer doesn't know the distribution of your data. If those counts are correct. Gather histograms when you gather statistics.

exec dbms_stats.gather_table_stats(user, 'table_name', cascade=>true, method_opt=>'for all indexed columns size 250');

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123474 is a reply to message #123398]

Mon, 13 June 2005 12:20

Frank
Messages: 7901
Registered: March 2000

Senior Member

[edit] Removed question after googling.

[Updated on: Mon, 13 June 2005 12:23]

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123585 is a reply to message #123398]

Tue, 14 June 2005 01:16

puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India

Member

Hi Guys,

I understand what you said.
But if I go what books says about bitmap Index

Bitmap index are used where

· Suitable for low cardinality columns
· Use much less space
· Bitmap indexes index nulls,whereas all other index types do not.
· Suitable if data is generally read than written
· Suitable if multiple columns are accessed in unpredictable combinations of AND and OR
· Suitable for star queries – BITMAP and hash joins
· Can’t lock bits so locking is not row level
· DML can be painfully slow. So if you add a new key, you add a whole new bitmap
· Bitmaps are stored in compressed format
· Bitmap looks like a big 2D matrix
· For bitmap indexes with direct load, the SORTED_INDEX flag does not apply.
· Bitmap indexes are not considered by the rule-based optimizer.
· Bitmap indexes cannot be used for referential integrity checking.
· You cannot specify BITMAP when creating a global partitioned index or an index-organized table.
· You cannot specify both UNIQUE and BITMAP.
· You cannot specify BITMAP for a domain index.

Now tell me, the department_id table have less distinct rows and has null values as per book guidlines I must have BITMAP INDEX

Interestingly when I chnaged my Index to BTREE its working.

One more fact
When I mentions

Select department_id from <table> where
department_id = <some value>
my Bitmap index work and BTREE don't work.

(Department_ID is indexed column)

and when I mention
Select [* or dperatment_id,[any other column]] then my BITMAP is not working.

I'm confused.. why this is happeing..

Puneet

Report message to a moderator

Re: SQL QUERY NOT USING BITMAP INDEX [message #123712 is a reply to message #123398]

Tue, 14 June 2005 09:30

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

When you select just the department_id, your index "is working" because all oracle has to do is read the index to get the value you requested in your select statement. But when you do select *, you are asking for something totally different. For each row, oracle has to go to the table data to retrieve the values for all the columns. So in that case it has to read both the index and the table data, which is much more costly than just reading the index. This is why it is generally better to only select the columns you need.

Did you gather statistics as I suggested above? What was the result after that?

Report message to a moderator

Previous Topic:	Index on varchar2(2500)
Next Topic:	which view to use to know about global partitioned index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 24 03:24:30 CST 2025