How can i prohibit bitmap indexes in SQL hints?

Question

So I'd like to create a query that allows to use indexes, but don't allow to use bitmap indexes. How can I do that?

example:

--1. use indexes

select /*+ index(sz) index(c)  */  * 
from szallit sz natural join cikk c
where pkod = 10;

--2. use indexes, but no bitmap indexes

select /*+ ?  */  * 
from szallit sz natural join cikk c
where pkod = 10;

Why would you want to do that? What is the actual problem you are trying to solve with that? In almost all cases hints aren't needed to begin with — , Dec 07 '19 at 09:19
It's not important in this case, so you can use any table you want ( for example oracle employees or something like that), the point is that i have a bitmap index e.q create bitmap index bb on emp(hiredate) and a query select * from emp; and I need a hint that allow index and don't allow bitmap index. — Viktor Habony, Dec 07 '19 at 09:24
@AlexeyUsharovski, I think Oracle is the only RDBMS which supports bitmap indexes. — Wernfried Domscheit, Dec 07 '19 at 13:10
[INDEX_COMBINE](https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#BABHJBAJ) would be somehow the opposite. One solution would be a dynamic SQL where you build the `NO_INDEX` hint based on `ALL_INDEXES` — Wernfried Domscheit, Dec 07 '19 at 13:16

score 0 · Answer 1 · answered Dec 07 '19 at 09:49

0

You can use NO_INDEX hint in Oracle and explicite exclude an index. E.g. NO_INDEX(emp hiredate). There can be multiple excluded indexes for a table.

answered Dec 07 '19 at 09:49

Marcin Badtke

Yes, I know that, but my problem is when I have more bitmap indexes, and I don't want to write their name explicitly( or I don't know their name, because of someone else will create another bitmap index).. So I'd like to disallow ALL bitmap indexes and allow all other indexes – Viktor Habony Dec 07 '19 at 10:05
I understand now but not aware of such a hint. The only thing I can come up with now is to select bitmap indexes' names for particular table from all_indexes view and use dynamic SQL to construct up to date hint NO_INDEX. – Marcin Badtke Dec 07 '19 at 19:48

1 Answers1