Home » SQL & PL/SQL » SQL & PL/SQL » Compound bitmap join index on one dimension (merged 2) (Oracle 10g)
Compound bitmap join index on one dimension (merged 2) [message #438391] Fri, 08 January 2010 10:13 Go to next message
dan_berry
Messages: 3
Registered: January 2010
Junior Member
Hi all,

New to the forums and searched on this topic - didn't see anything, so here goes. Does anyone know if it's possible to create a compound bitmap join index using only one dimension. Here is some example code:

CREATE TABLE simple_fact (
    simple_dimension_1    NUMBER NOT NULL,
    simple_dimension_2    NUMBER NOT NULL,
    fact_1                NUMBER NOT NULL,
    CONSTRAINT sf_pk PRIMARY KEY (simple_dimension_1, simple_dimension_2)
);
    
CREATE TABLE simple_dimension (
    simple_dimension_key    NUMBER NOT NULL,
    dimension_attribute     VARCHAR2(5) NOT NULL,
    CONSTRAINT sd_pk PRIMARY KEY (simple_dimension_key)
);

INSERT INTO simple_dimension VALUES (1, 'YES');
INSERT INTO simple_dimension VALUES (2, 'NO');

INSERT INTO simple_fact VALUES (1, 2, 100);

CREATE BITMAP INDEX sf_sd_bji ON simple_fact (sd1.dimension_attribute, sd2.dimension_attribute)
    FROM simple_fact sf, simple_dimension sd1, simple_dimension sd2
WHERE sf.simple_dimension_1 = sd1.simple_dimension_key 
AND sf.simple_dimension_2 = sd2.simple_dimension_key;

When running the bitmap index portion of this command I get ORA-25954: missing primary key or unique constraint on dimension.

Anyone have any insight as to what I'm doing wrong or if I'm trying to do something that isn't possible?
Re: Compound bitmap join index on one dimension (merged 2) [message #438399 is a reply to message #438391] Fri, 08 January 2010 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
Nice post.

sorry for the delay.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#i2077608

Quote:

Restrictions on Bitmap Join Indexes In addition to the restrictions on bitmap indexes in general (see BITMAP), the following restrictions apply to bitmap join indexes:

* You cannot create a bitmap join index on an index-organized table or a temporary table.
* No table may appear twice in the FROM clause.

[Updated on: Sat, 09 January 2010 02:02] by Moderator

Report message to a moderator

Re: Compound bitmap join index on one dimension (merged 2) [message #438400 is a reply to message #438399] Fri, 08 January 2010 11:53 Go to previous messageGo to next message
dan_berry
Messages: 3
Registered: January 2010
Junior Member
BlackSwan,

Thanks for the quick reply! It looks like I'll have to create another dimension if I want bitmap join indexes.

I work for an insurance company and for each claim we have both a claim rep and nurse assigned. The dimensional data for both is exactly the same and the assumption was that we could create one dimension table, have two keys in our claim_fact table that tie back to appropriate dim record, and then just create a compound bitmap join index.

Looks like that is a failed strategy - but at least I learned something!! Now to go about solving this problem in a more elegant way...

Thanks!
Re: Compound bitmap join index on one dimension (merged 2) [message #438416 is a reply to message #438400] Fri, 08 January 2010 16:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why do you want to use a bitmap join index? If you use a simple bitmap index on each of the FK columns, then you get the benefit of the index regardless of which column you scan on in the dimension.

I always saw bitmap join indexes as a solution for OLAP-like cubes.

Ross Leishman
Re: Compound bitmap join index on one dimension (merged 2) [message #439190 is a reply to message #438391] Thu, 14 January 2010 16:31 Go to previous message
dan_berry
Messages: 3
Registered: January 2010
Junior Member
Hi Ross,

Having a bitmap index on a primary key column does me no good, as the cardinality is far too high and the index won't even be used.
Previous Topic: Join problem, need workaround
Next Topic: Zero fill an OUT parameter
Goto Forum:
  


Current Time: Wed Sep 28 14:20:51 CDT 2016

Total time taken to generate the page: 0.14467 seconds