Re: Trouble with my first bitmap join index

From: ddf <oratune_at_msn.com>
Date: Thu, 9 Jul 2009 13:27:44 -0700 (PDT)
Message-ID: <479aa32f-2464-4542-9917-9118933d2f10_at_26g2000yqk.googlegroups.com>

On Jul 9, 2:58 pm, AIM <Andre_Miku..._at_Hotmail.com> wrote:
> Hi,
>
> I am trying to create a bitmap join index.
> I am not getting anywhere.
> I am using Oracle 10.1
> Any recommendations?
>
> --my work
>
> CREATE TABLE OPS$NFF397.ELB11
> (
>   ELBID     VARCHAR2(2)                      NOT NULL,
>   ELBVALUE  VARCHAR2(8)                      NOT NULL,
>   ELBVALUE2 VARCHAR2(8)
> )
> PARTITION BY RANGE (ELBVALUE)
> (
>   PARTITION ELB_01 VALUES LESS THAN ('002'),
>   PARTITION ELB_02 VALUES LESS THAN (MAXVALUE)
> )
> ;
> INSERT INTO OPS$NFF397.ELB11(ELBID, ELBVALUE, ELBVALUE2) VALUES('01',
> '001', 'AI');
> INSERT INTO OPS$NFF397.ELB11(ELBID, ELBVALUE, ELBVALUE2) VALUES('02',
> '001', 'AJ');
> INSERT INTO OPS$NFF397.ELB11(ELBID, ELBVALUE, ELBVALUE2) VALUES('03',
> '002', 'AK');
> INSERT INTO OPS$NFF397.ELB11(ELBID, ELBVALUE, ELBVALUE2) VALUES('04',
> '002', 'AL');
> COMMIT;
>
> CREATE TABLE OPS$NFF397.ELB12
> (
>   ELBID     VARCHAR2(2)                      NOT NULL,
>   ELBVALUE  VARCHAR2(8)                      NOT NULL,
>   ELBVALUE2 VARCHAR2(8)
> )
> PARTITION BY RANGE (ELBVALUE)
> (
>   PARTITION ELB_02 VALUES LESS THAN ('003'),
>   PARTITION ELB_03 VALUES LESS THAN (MAXVALUE)
> )
> ;
> INSERT INTO OPS$NFF397.ELB12(ELBID, ELBVALUE, ELBVALUE2) VALUES('03',
> '002', 'AK');
> INSERT INTO OPS$NFF397.ELB12(ELBID, ELBVALUE, ELBVALUE2) VALUES('04',
> '002', 'AL');
> INSERT INTO OPS$NFF397.ELB12(ELBID, ELBVALUE, ELBVALUE2) VALUES('05',
> '003', 'AM');
> INSERT INTO OPS$NFF397.ELB12(ELBID, ELBVALUE, ELBVALUE2) VALUES('06',
> '003', 'AN');
> COMMIT;
>
> CREATE TABLE OPS$NFF397.ELB13
> (
>   ELBID     VARCHAR2(2)                      NOT NULL,
>   ELBVALUE  VARCHAR2(8)                      NOT NULL,
>   ELBVALUE2 VARCHAR2(8)
> )
> PARTITION BY RANGE (ELBVALUE)
> (
>   PARTITION ELB_03 VALUES LESS THAN ('004'),
>   PARTITION ELB_04 VALUES LESS THAN (MAXVALUE)
> )
> ;
> INSERT INTO OPS$NFF397.ELB13(ELBID, ELBVALUE, ELBVALUE2) VALUES('05',
> '003', 'AM');
> INSERT INTO OPS$NFF397.ELB13(ELBID, ELBVALUE, ELBVALUE2) VALUES('06',
> '003', 'AN');
> INSERT INTO OPS$NFF397.ELB13(ELBID, ELBVALUE, ELBVALUE2) VALUES('07',
> '004', 'AO');
> INSERT INTO OPS$NFF397.ELB13(ELBID, ELBVALUE, ELBVALUE2) VALUES('08',
> '004', 'AP');
> COMMIT;
>
> --does nothing ...
>
> CREATE UNIQUE INDEX OPS$NFF397.ELB11_UBIX_ELBID ON OPS$NFF397.ELB11
> (ELBID);
>
> CREATE UNIQUE INDEX OPS$NFF397.ELB12_UBIX_ELBID ON OPS$NFF397.ELB12
> (ELBID);
>
> CREATE UNIQUE INDEX OPS$NFF397.ELB13_UBIX_ELBID ON OPS$NFF397.ELB13
> (ELBID);
>
> --does nothing ...
>
> CREATE DIMENSION OPS$NFF397.ELB11_DM_LEVEL_ELBID
>   LEVEL LEVEL_ELBID                    IS
>     (OPS$NFF397.ELB11.ELBID);
>
> CREATE DIMENSION OPS$NFF397.ELB12_DM_LEVEL_ELBID
>   LEVEL LEVEL_ELBID                    IS
>     (OPS$NFF397.ELB12.ELBID);
>
> CREATE DIMENSION OPS$NFF397.ELB13_DM_LEVEL_ELBID
>   LEVEL LEVEL_ELBID                    IS
>     (OPS$NFF397.ELB13.ELBID);
>
> 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;
>
> ORA-25954: missing primary key or unique constraint on dimension
>
> CREATE BITMAP INDEX S
> ON ELB11(ELB12.ELBVALUE)
> FROM ELB11, ELB12
> WHERE ELB12.ELBID = ELB11.ELBID;
>
> ORA-25954: missing primary key or unique constraint on dimension
>
> Any ideas?
>
> Thanks,
>
> AIM
Read the documentation?

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5006.htm#SQLRF01206

David Fitzjarrell Received on Thu Jul 09 2009 - 15:27:44 CDT