Табличные пространства для индексов таблиц фактов:
CREATE TABLESPACE QURT_IDX1_0
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\webtest\stud\QURT_IDX1_0.ora' SIZE 3M REUSE AUTOEXTEND ON DEFAULT STORAGE
(initial 40k next 40k pctincrease 0 maxextents unlimited);
CREATE TABLESPACE QURT_IDX2_0
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\webtest\stud\QURT_IDX2_0.ora' SIZE 3M REUSE AUTOEXTEND ON DEFAULT STORAGE
(initial 40k next 40k pctincrease 0 maxextents unlimited);
Создать пользователя:
CREATE USER USER_0 IDENTIFIED BY TEST
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO USER_0;
--GRANT dba TO USER_0;
GRANT CREATE ANY TABLE TO USER_0;
GRANT CREATE ANY INDEX TO USER_0;
GRANT RESOURCE TO USER_0;
GRANT CREATE SESSION TO USER_0;
Подключится к базе от имени созданного пользователя.
CONNECT USER_0@WEBTEST / TEST;
Создать таблицы измерений (при создании таблиц и индексов необходимо указать соответствующие табличные пространства – пример приведен ниже):
Покупатель
CREATE TABLE USER_0.CUSTOM (
ID NUMBER, FIO VARCHAR2 (30), COUNTRY VARCHAR2 (30) )
TABLESPACE DIM_0 STORAGE (INITIAL 16K NEXT 16K);
CREATE Unique INDEX USER_0.CS_PK_IND ON USER_0.CUSTOM (ID)
TABLESPACE IDX_0 STORAGE ( INITIAL 16K NEXT 16K);
ALTER TABLE CUSTOM ADD CONSTRAINT PK_CS PRIMARY KEY (ID);
Продукт
CREATE TABLE USER_0.PRODUCT (
ID NUMBER, NAME VARCHAR2 (30), PRICE number)
TABLESPACE DIM_0 STORAGE (INITIAL 16K NEXT 16K);
CREATE Unique INDEX USER_0.PD_PK_IND ON USER_0.PRODUCT (ID)
TABLESPACE IDX_0 STORAGE ( INITIAL 16K NEXT 16K);
ALTER TABLE PRODUCT ADD CONSTRAINT PK_PD PRIMARY KEY (ID);
Время покупки
CREATE TABLE USER_0.TIME (
TIME DATE, ISHOLIDAY NUMBER)
TABLESPACE DIM_0 STORAGE (INITIAL 16K NEXT 16K);
CREATE UNIQUE INDEX USER_0.TM_PK_IND ON USER_0.TIME (TIME)
TABLESPACE IDX_0 STORAGE ( INITIAL 16K NEXT 16K);
ALTER TABLE TIME ADD CONSTRAINT TM_PD PRIMARY KEY (TIME);
Создать таблицу фактов, с использованием секционирования по диапазонам ключей:
CREATE TABLE USER_0.POKUPKA
(
product_id NUMBER NOT NULL,
custom_id NUMBER NOT NULL,
time_key DATE NOT NULL
)
PARTITION BY RANGE (time_key)
(
PARTITION QURT1_0
VALUES LESS THAN (TO_DATE('31.03.2008', 'DD.MM.YYYY'))
TABLESPACE QURT1_0,
PARTITION QURT2_0
VALUES LESS THAN (TO_DATE('30.06.2008', 'DD.MM.YYYY'))
TABLESPACE QURT2_0,
PARTITION QURT_OTHER VALUES LESS THAN (MAXVALUE) TABLESPACE QURT_OTHER);
ALTER TABLE POKUPKA
ADD (FOREIGN KEY (product_id)
REFERENCES PRODUCT(ID));
ALTER TABLE POKUPKA
ADD (FOREIGN KEY (custom_id)
REFERENCES CUSTOM(ID));
ALTER TABLE POKUPKA
ADD (FOREIGN KEY (time_key)
REFERENCES TIME(TIME));
Создать индекс на основе битовых карт для секционирования по ключу времени. Индекс должен быть локальный.
CREATE Bitmap INDEX USER_0.POKUP_TIME_IND ON POKUPKA (TIME_KEY)
LOCAL (
PARTITION QURT_IDX1_0 TABLESPACE QURT_IDX1_0,
PARTITION QURT_IDX2_0 TABLESPACE QURT_IDX2_0);
Создать соединительный индекс на основе битовых карт для таблицы фактов и одной или более таблиц измерений:
Добавить в таблицу измерений поле, которое будет принимать 2-3 различных значения:
ALTER TABLE CUSTOM ADD POL VARCHAR2(1)
CONSTRAINT POL_CHECK CHECK(POL IN ('М', 'Ж'));
Создать соединительный индекс:
CREATE BITMAP INDEX USER_0.POL_IND
ON POKUPKA (CUSTOM.POL)
FROM POKUPKA, CUSTOM
WHERE POKUPKA.CUSTOM_ID = CUSTOM.ID
LOCAL;
Добавить пользователю привилегии для создания, изменения и удаления материализованных измерений:
CONNECT SCOTT@WEBTEST / TIGER;
GRANT CREATE ANY MATERIALIZED VIEW TO USER_0;
GRANT ALTER ANY MATERIALIZED VIEW TO USER_0;
GRANT DROP ANY MATERIALIZED VIEW TO USER_0;
Далее необходимо снова соединиться под тестовым пользователем:
CONNECT USER_0@WEBTEST / TEST;
Создать материализованное измерение(представление), соединяющее таблицу фактов и одну и более таблицу измерений. Материализованное измерение(представление) должно содержать одну или более функцию агрегирования и условие GROUP BY:
CREATE MATERIALIZED VIEW TODAY_SALL
TABLESPACE MVIEW_0
BUILD IMMEDIATE
REFRESH COMPLETE
AS
SELECT C.POL ,MAX(K.PRICE) AS MAX
FROM POKUPKA B, PRODUCT K, CUSTOM C, TIME T
WHERE B.CUSTOM_ID = C.ID AND B.PRODUCT_ID = K.ID AND
B.TIME_KEY = T.TIME AND
T.TIME = SYSDATE
GROUP BY (C.POL);
Загрузка данных в таблицу с использованием SQL* LOADER.
Загрузить данные в таблицу «Покупатель»:
CUSTOM.CTL
load data
infile "C:\TTT\CUSTOM.DAT"
APPEND
into table CUSTOM
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, FIO, POL)
CUSTOM.DAT
1,"JIMY","М"
2,"TOM","М"
3,"ANN","Ж"
D:\oracle\ora81\bin>SQLLDR USERID=USER_0@WEBTEST/TEST, CONTROL=C:\TTT\CUSTOM.CTL
Загрузить данные в таблицу «Продукт»:
PROD.CTL
load data
infile "C:\TTT\PROD.DAT"
APPEND
into table product
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PRICE)
PROD.DAT
1,"монитор","2000"
2,"клавиатура","30"
3,"мышь","10"
D:\oracle\ora81\bin>SQLLDR USERID=USER_0@ WEBTEST /TEST, CONTROL=C:\TTT\PROD.CTL
Загрузить данные в таблицу фактов с использованием процедуры INSERT.
Создать запросы на выбор данных из всех таблиц.
Например, запрос с использованием созданного ранее индекса:
SELECT * FROM POKUPKA P, CUSTOM A
WHERE P.CUSTOM_ID = A.ID AND A.POL = 'М';
Проверить заполнение материализованного измерения.
СОДЕРЖАНИЕ ОТЧЕТА
Цель работы.
Спроектированная схема данных для хранилища.
Тексты команд по каждому пункту задания.
Результаты выполнения каждого пункта задания.
Выводы.