Re: Trouble with my first bitmap join index
Date: Thu, 16 Jul 2009 13:01:41 -0700 (PDT)
Message-ID: <3c737924-043e-4f56-8f7f-967abfa0cdbc_at_o15g2000yqm.googlegroups.com>
On Jul 10, 2:30 pm, Randolf Geist <mah..._at_web.de> wrote:
> On Jul 9, 9:58 pm, AIM <Andre_Miku..._at_Hotmail.com> wrote:
>
> > ORA-25954: missing primary key or unique constraint ondimension
>
> > Any ideas?
>
> The error message is quite clear: Although you have created a unique
> index, you haven't declared a unique or primary key constraint on thedimensiontables... Although this is a bit picky, there is a
> difference between a unique constraint and a unique index, but I have
> to admit that in this particular case the unique index prevents any
> non-uniqueness similar to the constraint.
>
> All you need to do is to declare primary key/unique constraints on thedimensiontables that even can use the unique index already created.
> So the following should be sufficient:
>
> alter table ELB12 add constraint pk_elb12 primary key (elbid);
>
> alter table ELB13 add constraint pk_elb13 primary key (elbid);
>
> Note that theDIMENSIONobjects are not required for this to work,
> it's the constraint that is crucial. Obviously, otherwise the fact
> table wasn't "key-preserved" and Oracle couldn't guarantee that
> joining to thedimensiontables wouldn't lead to duplicate data. If
> that was the case, thedimensiontable elimination wouldn't be
> possible due to possibly wrong results. The same applies to the join
> table elimination feature added in 10.2. It can only be applied if you
> have suitable constraints in place.
>
> Regards,
> Randolf
>
> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> SQLTools++ for Oracle (Open source Oracle GUI for Windows):http://www.sqltools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/
Everyone,
Thanks for the responses.
Yes, I dropped the Dimensions.
They were not part of the solution.
Oracle 10gR1 seems perhaps not to allow
BITMAP join INDEXES one partitioned tables.
Everytime I tried creating an bitmap join index in any combination I recieved the following error.
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables
Anyways, here is my final solution.
CREATE TABLE ELB11
(
ELBID VARCHAR2(2) NOT NULL, ELBVALUE VARCHAR2(8) NOT NULL,ELBVALUE2 VARCHAR2(8)
);
CREATE TABLE ELB12
(
ELBID VARCHAR2(2) NOT NULL, ELBVALUE VARCHAR2(8) NOT NULL,ELBVALUE2 VARCHAR2(8)
);
CREATE TABLE ELB13
(
ELBID VARCHAR2(2) NOT NULL, ELBVALUE VARCHAR2(8) NOT NULL,ELBVALUE2 VARCHAR2(8)
);
CREATE UNIQUE INDEX ELB11_UBIX_ELBID ON ELB11(ELBID);
CREATE UNIQUE INDEX ELB12_UBIX_ELBID ON ELB12(ELBID);
CREATE UNIQUE INDEX ELB13_UBIX_ELBID ON ELB13(ELBID);
ALTER TABLE ELB11 ADD (
CONSTRAINT ELB11_UBIX_ELBID
UNIQUE
(ELBID)
USING INDEX);
ALTER TABLE ELB12 ADD (
CONSTRAINT ELB12_UBIX_ELBID
UNIQUE
(ELBID)
USING INDEX);
ALTER TABLE ELB13 ADD (
CONSTRAINT ELB13_UBIX_ELBID
UNIQUE
(ELBID)
USING INDEX);
CREATE BITMAP INDEX ELB123_IXB_11_12
ON ELB11(ELB12.ELBVALUE)
FROM ELB11, ELB12, ELB13
WHERE ELB12.ELBID = ELB11.ELBID AND
ELB12.ELBID = ELB13.ELBID;
--USAGE
SELECT ELB11.ELBID, ELB12.ELBVALUE
FROM ELB11, ELB12, ELB13
WHERE ELB12.ELBID = ELB11.ELBID AND
ELB12.ELBID = ELB13.ELBID;
--based on http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/indexes.htm#sthref353
Received on Thu Jul 16 2009 - 15:01:41 CDT