| bitmap index [message #230692] |
Thu, 12 April 2007 10:43  |
oll3i
Messages: 9
Registered: April 2007
Location: Poland
|
Junior Member |
|
|
i have indexes
CREATE BITMAP INDEX Ind_znizka ON Sprzedaz(znizka);
CREATE BITMAP INDEX Ind_oddzial ON Struktura_Sprzedaż(oddzial);
CREATE BITMAP INDEX Ind_miasto ON Wymiar_Geograficzny(miasto);
CREATE BITMAP INDEX Ind_wojewodztwo ON Wymiar_Geograficzny(wojew);
wd use of these indexes look sth like the following?
select p.prod from produkty p,sprzedaz s
where s.id_prod=p.id_produkt
and s.znika=10
SELECT ss.oddzial,SUM(s.ilosc) FROM struktura_sprzedazy ss,sprzedaz s
GROUP BY ss.oddzial
HAVING SUM(s.ilosc)>1000
SELECT wg.miasto,SUM(s.ilosc) from wymiar_geograficzny wg, Sprzedaz s
where wg.miasto="Warszawa"
GROUP BY wg.miasto
SELECT wg.wojew,SUM(s.ilosc) from wymiar_geograficzny wg, Sprzedaz s
where wg.wojew="mazowieckie"
GROUP BY wg.wojew
|
|
|
|
| Re: bitmap index [message #230698 is a reply to message #230692] |
Thu, 12 April 2007 10:54  |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
|
In the past, I have had problems getting Oracle to use bitmap indexes for single column queries. The optimizer's view seems to be that they are only useful when several are used in combination (which is what they were designed for). I have often seen situations where the bitmap index on a single column is performant, but the optimizer still thinks a full table scan would be better. If all else fails, you can always use hints to force the use of the index.
|
|
|
|
| Re: bitmap index [message #230702 is a reply to message #230692] |
Thu, 12 April 2007 11:02 |
oll3i
Messages: 9
Registered: April 2007
Location: Poland
|
Junior Member |
|
|
but when i define an index like this
CREATE BITMAP INDEX Ind_znizka ON Sprzedaz(znizka);
and then write a query
select p.prod from produkty p,sprzedaz s
where s.id_prod=p.id_produkt
and s.znika=10
wd that query use that index ?
|
|
|
|
| Re: bitmap index [message #230705 is a reply to message #230692] |
Thu, 12 April 2007 11:06 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
|
The only way to find out for certain is to try it. There are no absolute guarantees where the optimizer is concerned. It will do what it thinks best, but occasionally gets it wrong. Make sure all your tables have stats (ideally taken at column histogram level).
|
|
|
|
| Re: bitmap index [message #230736 is a reply to message #230692] |
Thu, 12 April 2007 12:38 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| oll3i wrote on Thu, 12 April 2007 11:43 |
wd use of these indexes look sth like the following?
|
This is getting very annoying. Use full words. I don't have any friggin' idea what the hell you are saying. SQL requires you to use full words, so do it here please.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
