Oracle Dimension

Question

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?

umesh rakhe · Answer

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

Arulraj · Answer

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 ;