2
Answers

Oracle Dimension

Arulraj

Arulraj

15y
3k
1
Hai friends!

I am Arul. I am develping project in oracle data mining. I want to know what is the use of oracle dimension and how it works?



Answers (2)
0
umesh rakhe
NA 77 0 15y
A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. However, columns in one column set (called a level) can come from a different table than columns in another set. The optimizer uses these relationships with materialized views to perform query rewrite May This Solve your question Regards
Accepted
0
Arulraj
NA 5 0 15y
Hai friend,

how to create the dimension for the following oracle tables...

please help me

BILLMASTER
CREATE TABLE BILLMASTER (RESTCODE VARCHAR2(3
    byte) NOT NULL, BILLNO NUMBER(8) NOT NULL, BILLDATE DATE,
    SMODE CHAR(1 byte), STCODE NUMBER(3), SPCODE NUMBER(3),
    TABLENO NUMBER(3), SEATNO NUMBER(3), BCODE NUMBER(3), CCODE
    NUMBER(2) NOT NULL, TOTALAMT NUMBER(7, 2) NOT NULL, STAXAMT
    NUMBER(6, 2), SURCHARGE NUMBER(6, 2), CARSERVICE CHAR(1
    byte), VISITORS NUMBER(5), DUPLICATE NUMBER(2), CANCELFLAG
    CHAR(1 byte), ACBILL CHAR(1 byte), SERVICECHARGE
    NUMBER(8, 2), DISCAMOUNT NUMBER(8, 2), CARSERVICECHARGE
    NUMBER(8, 2), SERPERCENTAGE NUMBER(8, 2), STEMPCODE
    NUMBER(6) DEFAULT 0 NOT NULL, SPEMPCODE NUMBER(6) DEFAULT
    0 NOT NULL, PAIDFLAG CHAR(1 byte), ROUNDEDAMT NUMBER(11,
    2), BACKUPDATE DATE, SERVICETAX NUMBER(6, 2) DEFAULT 0
    NOT NULL, ECESS NUMBER(6, 2) DEFAULT 0 NOT NULL, HECESS
    NUMBER(6, 2) DEFAULT 0 NOT NULL, VATFORSVTAX NUMBER(6, 2)
    DEFAULT 0 NOT NULL,
    CONSTRAINT CHK_SMODE_BILLMASTER CHECK(SMODE IN ('A','S',
    'W','C','P','T','V')),
    CONSTRAINT MPK_PBILLMAS PRIMARY KEY(RESTCODE, CCODE, BILLNO)
    USING INDEX 
    TABLESPACE SYSTEM
    STORAGE ( INITIAL 12K NEXT 4260K MINEXTENTS 1 MAXEXTENTS 249
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10
    INITRANS 2 MAXTRANS 255) 
    TABLESPACE USERS PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
    255
    STORAGE ( INITIAL 32400K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
    LOGGING ;


BILLDETAIL

CREATE TABLE BILLDETAIL (RESTCODE VARCHAR2(3
    byte) NOT NULL, BILLDATE DATE NOT NULL, CCODE NUMBER(2),
    BILLNO NUMBER(8), ITEMCODE NUMBER(5), ITEMPRICE
    NUMBER(7, 2) NOT NULL, QTYSOLD NUMBER(8, 2) NOT NULL, STAX
    NUMBER(5, 2) NOT NULL, CANCELFLAG CHAR(1 byte), SURCHARGE
    NUMBER(5, 2) NOT NULL, TAXREPCODE NUMBER(2) NOT NULL, STBETAPERCT
    NUMBER(5, 2) DEFAULT 0 NOT NULL, SPBETAPERCT NUMBER(5, 2)
    DEFAULT 0 NOT NULL,
    CONSTRAINT MFK_BILLDETAIL_BILLNO FOREIGN KEY(RESTCODE, CCODE,
    BILLNO)
    REFERENCES BILLMASTER(RESTCODE, CCODE, BILLNO))
    
    TABLESPACE USERS PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
    255
    STORAGE ( INITIAL 32400K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
    LOGGING ;

IMASTER

CREATE TABLE IMASTER (ITEMCODE NUMBER(5) NOT NULL,
    ITEMNAME VARCHAR2(30 byte) NOT NULL, TITEMNAME
    VARCHAR2(30 byte), UNITPRICE NUMBER(8, 2) DEFAULT 0 NOT
    NULL, ACUNITPRICE NUMBER(8, 2) NOT NULL, SALESTAX
    NUMBER(5, 2) DEFAULT 0 NOT NULL, DEPTCODE NUMBER(3), KITCHENCODE
    NUMBER(2), KITCHENFLAG CHAR(1 byte) DEFAULT 'Y', SURCHARGE
    NUMBER(5, 2) DEFAULT 0 NOT NULL, TAXREPCODE NUMBER(2)
    DEFAULT 0 NOT NULL, LASTUPDATE DATE DEFAULT SYSDATE, HOMEDELIVERYPRICE
    NUMBER(8, 2) DEFAULT 0 NOT NULL, STBETAPERCT NUMBER(5, 2)
    DEFAULT 1, SPBETAPERCT NUMBER(5, 2) DEFAULT 1, ISFRACTIONAL
    CHAR(1 byte),
    CONSTRAINT FK_DEPTCODE FOREIGN KEY(DEPTCODE)
    REFERENCES DEPTMASTER(DEPTCODE) NOVALIDATE,
    CONSTRAINT FK_KITCODE FOREIGN KEY(KITCHENCODE)
    REFERENCES KITCHENMASTER(KITCHENCODE)
    NOVALIDATE,
    CONSTRAINT PK_ITEMCODE PRIMARY KEY(ITEMCODE)
    USING INDEX 
    TABLESPACE INDX
    STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
    PCTFREE 10 INITRANS 2 MAXTRANS 255,
    CONSTRAINT SYS_C003287 CHECK(KITCHENFLAG IN ('Y','N')),
    CONSTRAINT SYS_C003289 CHECK(ISFRACTIONAL IN ('Y','N'))) 
    TABLESPACE SYSTEM PCTFREE 10 PCTUSED 40 INITRANS 1
    MAXTRANS 255
    STORAGE ( INITIAL 280K NEXT 128K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1)
    LOGGING ;

ALLRESTINFO

CREATE TABLE ALLRESTINFO (REST_CODE VARCHAR2(2
    byte) NOT NULL, REST_NAME VARCHAR2(40 byte) NOT NULL, REST_REFER
    VARCHAR2(30 byte) NOT NULL, REST_COMM_DATE DATE NOT NULL, REST_ADD1
    VARCHAR2(50 byte), REST_ADD2 VARCHAR2(30 byte), REST_ADD3
    VARCHAR2(30 byte), REST_PHONE1 VARCHAR2(12 byte), REST_PHONE2
    VARCHAR2(12 byte), REST_PIN_CODE VARCHAR2(7 byte) NOT NULL,
    REST_ESI_CODE VARCHAR2(15 byte), SHORTNAME VARCHAR2(7
    byte) NOT NULL, SNO NUMBER(5, 2) DEFAULT 0, REMOTEADRESS
    VARCHAR2(150 byte), MEALSFLAG CHAR(1 byte) DEFAULT 'N' NOT
    NULL, CANTEENFLAG CHAR(1 byte) DEFAULT 'N', CATERINGFLAG
    CHAR(1 byte) DEFAULT 'N', DISP_FLAG CHAR(1 byte),
    CONSTRAINT RESTAURANT PRIMARY KEY(REST_CODE)
    USING INDEX 
    TABLESPACE INDX
    STORAGE ( INITIAL 8K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
    PCTFREE 10 INITRANS 2 MAXTRANS 255,
    CONSTRAINT SYS_C002939 CHECK(CANTEENFLAG IN ('Y','N','y',
    'n'))) 
    TABLESPACE SYSTEM PCTFREE 10 PCTUSED 40 INITRANS 1
    MAXTRANS 255
    STORAGE ( INITIAL 40K NEXT 12K MINEXTENTS 1 MAXEXTENTS 121
    PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1)
    LOGGING
    CACHE ;
Next Recommended Forum