Home » SQL & PL/SQL » SQL & PL/SQL » bitmap index
bitmap index [message #230692] Thu, 12 April 2007 10:43 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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.
Re: bitmap index [message #230785 is a reply to message #230736] Thu, 12 April 2007 16:47 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Agreed.

oll3i, out of interest, is the idea to save yourself the effort of typing or to impart a casual tone to your message?
Previous Topic: Web service with PL/SQL Server Pages
Next Topic: trying to get percentage of count
Goto Forum:
  


Current Time: Mon Dec 05 18:48:59 CST 2016

Total time taken to generate the page: 0.13989 seconds