Trouble with my first bitmap join index

From: AIM <Andre_Mikulec_at_Hotmail.com>
Date: Thu, 9 Jul 2009 12:58:05 -0700 (PDT)
Message-ID: <d048f1be-747c-4bba-b5b2-5f973ff28f2a_at_q11g2000yqi.googlegroups.com>

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 Received on Thu Jul 09 2009 - 14:58:05 CDT