Home » Server Options » Text & interMedia » BITMAP CONVERSION - Performance problem
BITMAP CONVERSION - Performance problem [message #305015] Fri, 07 March 2008 09:13 Go to previous message
ankurgodambe
Messages: 45
Registered: March 2005
Member
hi,
I have a concatenated text index created over 5 columns in a table. Recently, started having performance issues with the query and saw that the plan for query has changed.

Old plan:
=======
SQL> select *
from newseims
where
contains(sma_company_name,'ibm',0) > 0
and contains(sma_company_name, 'cottle',1)>0
and contains(sma_company_name,'usa',2)>0
order by score(0) desc, score(1) desc; 3 4 5 6 7

1 row selected.

Elapsed: 00:00:00.52

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=78 Card=1 Bytes=146)
1 0 SORT (ORDER BY) (Cost=78 Card=1 Bytes=146)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'NEWSEIMS' (Cost=11 Car
d=1 Bytes=146)

3 2 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=19)

======

New Plan:

======
SQL> select *
from newseims
where
contains(sma_company_name,'ibm',0) > 0
and contains(sma_company_name, 'cottle',1)>0
and contains(sma_company_name,'usa',2)>0
order by score(0) desc, score(1) desc; 2 3 4 5 6 7

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=74 Card=1 Bytes=146)
1 0 SORT (ORDER BY) (Cost=74 Card=1 Bytes=146)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'NEWSEIMS' (Cost=7 Card
=1 Bytes=146)

3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP AND
5 4 BITMAP CONVERSION (FROM ROWIDS)
6 5 SORT (ORDER BY)
7 6 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=0)
8 4 BITMAP CONVERSION (FROM ROWIDS)
9 8 SORT (ORDER BY)
10 9 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=0)
11 4 BITMAP CONVERSION (FROM ROWIDS)
12 11 SORT (ORDER BY)
13 12 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=0)

======

I have tried all sort of things like deleting stats on DR$ tables, index etc, but nothing seems to give me back the old plan. Same column "sma_company_name" appears twice in the query as "within another_column" is passed as bind variable. The response from old plan was better as it was scanning over the domain index only once. Appreciate any help/pointers.

Thanks
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: intermedia in oracle 10g
Next Topic: How do I list Index Preferences I have created?
Goto Forum:
  


Current Time: Fri Mar 29 08:46:57 CDT 2024