Indexes cannot be used for inequalities?
For years and years I thought it was true. Now I'd say it's only partially true. OK it is true for b-tree indexes (at least there isn't any reasonable effect of using an index for "not eaquals to"), but for bitmaps it's a different answer though. And I mean generally YES, they can be used :)
OK let's look at examples. I'll create a table and populate with almost all the same values but one. That's to make things more attractive for CBO to get the one and only value.
SQL> CREATE TABLE test (
2 id NUMBER PRIMARY KEY,
3 txt VARCHAR2(10) NOT NULL);
Table created.
SQL> INSERT INTO test
2 SELECT rownum, 'AAA'
3 FROM DUAL CONNECT BY LEVEL < 10000;
9999 rows created.
SQL> INSERT INTO test
2 VALUES (10000, 'BBB');
1 row created.
So now I have 9999 of "AAA" and only one "BBB".
I won't explain with examples what one can get with b-tree indexes, the best is INDEX FULL SCAN and the example by Richard Foote is here.
OK but what if we try bitmap index? Let's create it:
SQL> CREATE BITMAP INDEX txt_idx ON test (txt) COMPUTE STATISTICS;
Index created.
Now let's see what we get for the query we know should get only 1 row:
SQL> SELECT * FROM test
2 WHERE txt <> 'AAA';
ID TXT
---------- ----------
10000 BBB
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 7 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TXT"<>'AAA')
Statistics
----------------------------------------------------------
23 consistent gets
1 rows processed
Bang! Full scan. Not very promising. OK what if we try to hint the query?
At first let's give a soft hint - how about using an index?
SQL> SELECT /*+ index(test) */ * FROM test
2 WHERE txt <> 'AAA';
ID TXT
---------- ----------
10000 BBB
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 826 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 7 | 826 (0)|
| 2 | INDEX FULL SCAN | SYS_C009499 | 10000 | | 26 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TXT"<>'AAA')
Statistics
----------------------------------------------------------
39 consistent gets
1 rows processed
WoW! That's actually annoying. Bad plan, many consistent gets. Oracle is using primary key index, but not my brand new bitmap index! I'm disappointed.
OK now let's hammer it with the precise hint!
SQL> SELECT /*+ index(test txt_idx) */ * FROM test
2 WHERE txt <> 'AAA';
ID TXT
---------- ----------
10000 BBB
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 36 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 7 | 36 (0)|
| 2 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 3 | BITMAP INDEX FULL SCAN | TXT_IDX | | | |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TXT"<>'AAA')
Statistics
----------------------------------------------------------
2 consistent gets
1 rows processed
So that's it!! At last we get it! Only 2 consistent gets. And the cost is much less than using that stupid primary key index full scan. So that means Oracle even didn't consider using this plan at least for the query with just index hint. I looked at 10053 trace and found following for the query with just hint "index(test)":
The beginning looks promising:
Table Stats::
Table: TEST Alias: A
#Rows: 10000 #Blks: 20 AvgRowLen: 7.00
Index Stats::
Index: SYS_C009499 Col#: 1 (NOT ANALYZED)
LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00
User hint to use this index
Index: TXT_IDX Col#: 2
LVLS: 0 #LB: 1 #DK: 2 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00
User hint to use this index
However in access path analysis Oracle simply doesn't even consider my bitmap index.
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST[A]
Table: TEST Alias: A
Card: Original: 10000.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: index (FullScan)
Index: SYS_C009499
resc_io: 826.00 resc_cpu: 6932309
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 826.39 Resp: 826.39 Degree: 1
Best:: AccessPath: IndexRange
Index: SYS_C009499
Cost: 826.39 Degree: 1 Resp: 826.39 Card: 1.00 Bytes: 0
Things however get brighter if we are just counting rows, then we don't need hint or anything, just run the select:
SQL> SELECT count(*) FROM test
2 WHERE txt <> 'AAA';
COUNT(*)
----------
1
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | BITMAP CONVERSION COUNT | | 1 | 4 | 1 (0)|
|* 3 | BITMAP INDEX FAST FULL SCAN| TXT_IDX | | | |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
3 consistent gets
1 rows processed
So it is more or less OK that table full scan is better (i.e. cost less than) bitmap index full scan and then bitmap conversion to rowids and then access table by calculated rowids. However it is quite strange that for simple index hint Oracle doesn't even consider this access path however as we can see from CBO trace understands that bitmap index is one of the hinted ones.
Of course the usual problems with bitmap indexes remains - locking, concurrency issues etc.
And yea I just arrived from Richard's seminar in Tallinn. Many new things, fluent story, cool!! :)
