Re: Trouble with my first bitmap join index
Date: Fri, 10 Jul 2009 12:30:02 -0700 (PDT)
Message-ID: <4a34ea09-6868-4d70-8c00-e39af0c3ec55_at_o7g2000yqb.googlegroups.com>
On Jul 9, 9:58 pm, AIM <Andre_Miku..._at_Hotmail.com> wrote:
> ORA-25954: missing primary key or unique constraint on dimension
>
> 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 the dimension tables... 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 the dimension tables 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 the DIMENSION objects 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 the dimension tables wouldn't lead to duplicate data. If that was the case, the dimension table 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/
Received on Fri Jul 10 2009 - 14:30:02 CDT