�ㄦ����涓���ㄥ�浜�В濡��浣跨� Oracle �版�搴��琛��������杞戒互���浣����Oracle ���杩��婊��绐�������/p>
澶у��版��典�锛���版�浠������版�����轰��″���(OLTP) 婧�郴缁��娌℃��存�杩���版�����版�������搴�郴缁����父锛��浜�OLTP 绯荤�浠ュ��ㄦ�浠剁�褰㈠�����版�棣�����杩���版�蹇�』��浇�版����搴�腑锛��濂芥�骞惰���浇锛���藉�����ㄧ����婧��
渚��锛��浜�����涓�娇�ㄧ�绀轰���� (MyCompany) ����¢�姹��纾��绌洪�����讹����杩��涓�勾�������������稿��������������版����蹇�』娓�������������Oracle �版�搴�〃��缉�ラ��剧���┖�淬�杩���������ㄧ��f�浣��缁存����杩�娇��Oracle ���瀹�����
涓轰�灏���ㄦ�浠跺�杞藉�瀹�滑������搴�腑锛�yCompany 浣跨�浜�Oracle �版�搴���ㄨ〃�规�����规����灏���ㄦ����濡�钩�㈡�浠讹��ㄦ����涓��绀轰负涓�釜���������琛ㄣ��变���互浣跨� SQL 璁块�澶��琛������ㄥ�浠ヤ娇��SQL��L/SQL ��Java �������界��ュ苟琛��璇㈠��ㄦ�浠躲���父锛��浼�������浆�㈠���浇 (ETL) 杩��涓�娇�ㄥ��ㄨ〃锛���版�杞��锛��杩�SQL锛���版���浇缁���颁�涓��楠や腑���浜�ETL 涓��澶���界�搴��浠ュ�澶��骞抽���欢������浠���������ヨ�锛���ㄨ〃�芥�涓�」��父寮哄ぇ����姐�澶��琛ㄦ�浣跨� SQL*Loader ���浠f�娉��
骞惰��ц���互�捐����澶у��版�搴�����������������搴���达�杩�������父涓��绛����郴缁�(DSS) ������搴��璧峰���娇�ㄣ��ㄨ���互�ㄦ�浜�被��� OLTP 绯荤���贩��郴缁��瀹��骞惰��ц�������璇达�骞惰�灏辨�灏�换�″��诧�浠��浣垮�涓��绋���舵�琛�换�★�������涓�釜杩��涓��涓�釜�ヨ��ц����浠诲����濡���ㄥ�涓��绋���跺����骞翠腑��釜涓�����搴��������涓�釜杩�����澶�����瀛e害����典�锛����互浣跨�骞惰��ц���
�版�浠�����绠$�绯荤�涓��涓��甯搁�瑕��浠诲�灏辨�淇���版�涓�OLTP锛��锛�郴缁�腑���绉���瑰�姝ャ����锛�����瑙�害���锛�����瀵垮���父寰��锛��姝ゅ���浇�版����蹇�』灏���版�浠����郴缁�腑娓��锛��绉��浣��甯哥О涓烘��ㄧ��f�浣������跺�涓���ㄥ�璇ュ敖蹇��瀹��姝ゆ�浣��涓��搴����换浣�����搴�郴缁��骞跺����璁块���/p>
杩��涓婚���〃
�ㄥ�濮��琛�� OBE ��换�′�������瀵圭���������� (
SH
) 妯″�瀹��涓���存���������
SH
妯″�涓��寤哄�浠��璞°�姝ゅ�锛��杩��瑕��浜�
SH
�ㄦ��朵�绯荤�������浜���ㄨ�浜���圭� SQL ��欢��
modifySH_11g.sql
���琛�互涓��楠わ�
���涓�釜缁��绐�����杩��缁��浼���ц�浠ヤ��戒护锛��宸ヤ�����存�涓�
/home/oracle/wkdir
锛�/p>
cd wkdir
(
娉��
���绋��璁炬��ユ�
/home/oracle/wkdir
��欢澶广�濡��娌℃�锛�������缓姝ゆ�浠跺す锛�苟灏�
etl.zip
���瀹硅В��缉�版���欢澶逛腑��
���涓�釜 SQL*Plus 浼��锛�互
SH
�ㄦ�韬�唤��
SH
�d护杩��杩����/p>
�ㄦ���SQL*Plus 浼��涓��濡����ず�ц�
modifySH_11g.sql
���锛�
@modifySH_11g.sql
�峰�����虹����搴��浠ヤ��惧��归���
�ㄤ互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�
create_directory.sql
���锛��灏�互涓�SQL 璇��澶����SQL*Plus 浼��涓��
DROP DIRECTORY data_dir;
DROP DIRECTORY log_dir;
CREATE DIRECTORY data_dir AS '/home/oracle/wkdir';
CREATE DIRECTORY log_dir AS '/home/oracle/wkdir';
杩��������瀵�Linux 绯荤���缓���骞朵������欢宸茬�瑙e�缂╁�
o
/home/oracle/wkdir
涓��娉ㄦ�锛��浜���ㄦ��㈢����锛���版�搴�����绗���炬�褰㈠���
DIRECTORY
瀵硅薄��/p>
�ㄤ互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�
create_external_table.sql
���锛��灏���朵互涓��浠ゃ�
DROP TABLE sales_delta_XT;
CREATE TABLE sales_delta_XT
PROD_ID NUMBER,
CUST_ID NUMBER,
TIME_ID DATE,
CHANNEL_ID CHAR(2),
PROMO_ID NUMBER,
QUANTITY_SOLD NUMBER(3),
AMOUNT_SOLD NUMBER(10,2)
ORGANIZATION external
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE log_dir:'sh_sales.bad'
LOGFILE log_dir:'sh_sales.log_xt'
FIELDS TERMINATED BY "|" LDRTRIM
(prod_id, cust_id,
time_id CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
channel_id, promo_id, quantity_sold, amount_sold
location
'salesDec01.dat'
)REJECT LIMIT UNLIMITED NOPARALLEL
�ㄥ�浠ラ�杩���������歌��炬�����ㄨ〃��俊���
- [USER | ALL| DBA]_EXTERNAL_TABLES
- [ALL| DBA]_DIRECTORIES
- [USER | ALL| DBA]_EXTERNAL_LOCATIONS
�ㄤ互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ��ヨ���
select_et.sql
��欢锛�/p>
SELECT COUNT(*) FROM sales_delta_xt;
SELECT MAX(time_id) FROM sales_delta_xt;
濡���ㄦ�纭���朵���欢锛����ぇ��
TIME_ID
搴�负 2001 骞�12 ������涓�ぉ��/p>
parallel_select_from_ET.sql
������浜�����涓��楠よ��ㄥ���SQL 璇����
�ㄤ互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ��ヨ���
parallel_select_from_ET.sql
��欢浠ユ������苟琛��璇��缁��淇℃�锛�/p>
SELECT *
FROM v$pq_sesstat
WHERE statistic in ('Queries Parallelized',
'Allocation Height');
�ц�����ㄨ����璇�互骞惰�搴�4 璁块�璇ュ��ㄨ〃锛�苟浣跨�涓�釜��ず�у������互浣跨�浠ヤ����浠ゆ�
parallel_select_from_ET_2.sql
�����/P>
SELECT /*+ parallel(a,4) */ COUNT(*)
FROM sales_delta_XT a;
灏界�澶��琛ㄤ����涓�釜杈��婧��浠讹�浣��灏��澶��琛ㄤ腑骞惰�������澶���ㄥ�浠ヤ娇�ㄥ�涓��绀虹�
ALTER TABLE
�戒护�存�澶��琛ㄧ�
PARALLEL
灞��锛�/p>
ALTER TABLE sales_delta_XT PARALLEL 4;
����ョ�浼�����璁′俊���瀵绘�涓��缁��淇℃���樊����ц�浠ヤ��戒护��
parallel_select_from_ET.sql
�����敞���骞惰�浼�����璁′俊������������绀轰����涓�釜骞惰�����ヨ�锛���剧ず浜�苟琛�����搴��
SELECT *
FROM v$pq_sesstat
WHERE statistic in
('Queries Parallelized', 'Allocation Height');
Oracle �版�搴��姣�釜������渚������剁�骞惰��存�璺��
INSERT
��������互浣跨��ц�璁″��ョ‘瀹����互骞惰��瑰��ц�
INSERT
���澶���ㄥ�浠ユ��ヤ�涓��浣�� SAL 缂��涓���ц�璁″�锛�������
EXPLAIN
PLAN
�戒护��
妫��浠ヤ�涓茶�璁″����浜�����璞¢�涓��骞惰�瀹������ら��ㄦ��逛�涓��璞$�榛��骞惰�搴����娇�ㄤ�涓��绀猴��ㄥ����浠ヤ覆琛��寮��琛�换�°�
瑕��绀�
SERIAL INSERT
琛�负���琛������ц�
show_serial_exec_plan.sql
锛��灏�互涓�SQL 璇��澶���版���SQL*Plus 浼��涓��
EXPLAIN PLAN FOR INSERT /*+ APPEND */ INTO sales
PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD
SELECT
PROD_ID,
CUST_ID,
TIME_ID,
case CHANNEL_ID
when 'S' then 3
when 'T' then 9
when 'C' then 5
when 'I' then 4
when 'P' then 2
else 99
PROMO_ID,
sum(QUANTITY_SOLD),
sum(AMOUNT_SOLD)
FROM SALES_DELTA_XT
GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);
ALTER TABLE sales DISABLE CONSTRAINT sales_product_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_customer_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_time_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_channel_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_promo_fk;
COMMIT;
ALTER SESSION ENABLE PARALLEL DML;
EXPLAIN PLAN FOR INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD
SELECT /*+ parallel (sales_delta_XT,4) */
PROD_ID,
CUST_ID,
TIME_ID,
case CHANNEL_ID
when 'S' then 3
when 'T' then 9
when 'C' then 5
when 'I' then 4
when 'P' then 2
else 99
PROMO_ID,
sum(QUANTITY_SOLD),
sum(AMOUNT_SOLD)
FROM SALES_DELTA_XT
GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);
�ц�浠ヤ� SQL 璇����
parallel_insert_file.sql
��欢锛��琛��涓�苟琛�span class="style5">������璁℃�璁剧疆涓�
ON
��/P>
set timing on
COMMIT;
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
(
PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD
)
SELECT /*+ PARALLEL (sales_delta_XT,4) */
PROD_ID,
CUST_ID,
TIME_ID,
case CHANNEL_ID
when 'S' then 3
when 'T' then 9
when 'C' then 5
when 'I' then 4
when 'P' then 2
else 99
end,
PROMO_ID,
sum(QUANTITY_SOLD),
sum(AMOUNT_SOLD)
FROM SALES_DELTA_XT
GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set timing off
SELECT * FROM TABLE(dbms_xplan.display_cursor);
SELECT *
FROM v$pq_sesstat
WHERE statistic in ('DML Parallelized','Allocation Height');
璁颁�璇ヨ��ョ��ц��堕�锛���朵�浣跨� SQL*Loader �跺�浣跨���画����������堕��告�杈��璇锋敞����变��ㄦ��ㄤ娇�ㄤ�涓�������CPU 璁$��轰�骞惰�璁块���父灏�����������������骞惰�����ㄨ〃璁块�浠ュ�灏�浆�����浇�哥�����ㄩ�浼����/p>
MODIFY CONSTRAINT sales_product_fk ENABLE NOVALIDATE;
ALTER TABLE sales
MODIFY CONSTRAINT sales_customer_fk ENABLE NOVALIDATE;
ALTER TABLE sales
MODIFY CONSTRAINT sales_time_fk ENABLE NOVALIDATE;
ALTER TABLE sales
MODIFY CONSTRAINT sales_channel_fk ENABLE NOVALIDATE;
ALTER TABLE sales
MODIFY CONSTRAINT sales_promo_fk ENABLE NOVALIDATE;
�ㄥ����涓�����灏卞�杞藉苟杞��浜�����瑕�娇��SQL*Loader ��浇��浆�㈡�����ㄩ�瑕�袱涓��绋��楠わ�杩�氨琛ㄧず�翠釜杩�������伐浣��澶������芥�浣��
SELECT COUNT(*)
FROM sales PARTITION (sales_q1_1998);
�ㄤ�涓�互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ��戒护��
exchange_old_partition.sql
���浜ゆ����锛�/P>
ALTER TABLE sales
EXCHANGE PARTITION sales_q1_1998
WITH TABLE sales_old_q1_1998
INCLUDING INDEXES;
璇锋敞����ㄥ�浠ヤ娇�ㄤ�涓�
DROP PARTITION
璇���ヤ唬�裤�
SALES_OLD_Q1_1998
琛ㄧ��ㄥ��ㄤ� 1998 骞寸�涓��搴������版������互���璇ヨ〃锛��绯荤�涓���ㄥ��よ�浜�����
�ㄤ�涓�互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ��戒护��
count_sales.sql
����ョ����涓���版�锛�/P>
SELECT COUNT(*)
FROM sales PARTITION (sales_q1_1998);
SELECT COUNT(*)
FROM sales_old_q1_1998;
涓��琛�
EXCHANGE
�戒护涔��涓��锛�����琛ㄧ��ㄥ��ㄧ����琛�������
SALES
���搴���轰负绌恒�
���绱㈠�涓��浜ゆ���奖����ц�浠ヤ��戒护��
show_sales_idx_status.sql
����ョ�绱㈠�淇℃���
SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
FROM user_ind_partitions uip, user_indexes ui
WHERE ui.index_name=uip.index_name(+)
AND ui.table_name='SALES'
GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);
�ц�浠ヤ�姝ラ�锛��寤轰�涓����┖���锛�/p>
�ㄤ�涓�互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ� SQL 璇��锛�����涓�釜���2002 骞�3 ���浠��搴���轰腑����烘�锛��涓������扮储寮�淮�ゃ��ㄥ�浠ユ�琛�互涓��浠ゆ�
split1_10g.sql
���瀹��姝や换�°�
ALTER TABLE sales SPLIT PARTITION sales_q1_2002
AT (TO_DATE('01-MAR-2002','DD-MON-YYYY'))
INTO (PARTITION sales_1_2_2002 TABLESPACE example,
PARTITION sales_MAR_2002 TABLESPACE example NOCOMPRESS)
UPDATE INDEXES;
�ㄥ�����扮储寮���轰�琛ㄥ��哄����浣�疆锛�苟涓�储寮���哄���部琚������藉����琛�互涓��浠ゆ�
see_split.sql
����ョ����淇℃���/P>
COL segment_name format a25
COL partition_name format a25
COL tablespace_name format a25
SELECT segment_name, partition_name, tablespace_name
FROM user_segments
WHERE segment_type='INDEX PARTITION'
AND segment_name IN
(SELECT index_name
FROM user_indexes
WHERE table_name='SALES');
灏��涓�釜瀛e害������浣������蹭负涓������������恒�涓轰�杩��婕�ず锛����
SYSAUX
琛ㄧ┖�翠腑��缓�朵腑涓�釜�板��猴�骞舵�寮�����浜�储寮��
�ㄤ�涓�互
SH
韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ� SQL 璇��锛���插�浣�����锛��涓������扮储寮�淮�ゃ��ㄥ�浠ヤ娇�ㄤ互涓���戒护��
split2_10g.sql
�����
ALTER TABLE sales SPLIT PARTITION sales_1_2_2002
AT (TO_DATE('01-FEB-2002','DD-MON-YYYY'))
INTO
(PARTITION sales_JAN_2002 TABLESPACE sysaux COMPRESS,
PARTITION sales_FEB_2002 TABLESPACE example NOCOMPRESS)
UPDATE INDEXES (sales_time_bix
(PARTITION jan_02 TABLESPACE example,
PARTITION feb_02 TABLESPACE system));
�ㄤ�涓�互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ��戒护��
cleanup_split_10g.sql
���绉诲��������扮储寮��
ALTER TABLE sales MOVE PARTITION sales_JAN_2002 TABLESPACE example COMPRESS
UPDATE INDEXES (sales_time_bix (PARTITION sales_jan_2002 TABLESPACE example),
sales_cust_bix (PARTITION sales_jan_2002 TABLESPACE example),
sales_channel_bix (PARTITION sales_jan_2002 TABLESPACE example),
sales_prod_bix (PARTITION sales_jan_2002 TABLESPACE example),
sales_promo_bix (PARTITION sales_jan_2002 TABLESPACE example))
;
ALTER INDEX sales_time_bix
REBUILD PARTITION feb_02 TABLESPACE example;
SELECT segment_name, partition_name, tablespace_name
FROM user_segments
WHERE segment_type='INDEX PARTITION'
AND segment_name IN (SELECT index_name
FROM user_indexes
WHERE table_name='SALES')
AND tablespace_name <> 'EXAMPLE';
姝ラ�锛�/p>
�ㄤ�涓�互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ��戒护��
prep4_global_index.sql
������杩���ㄥ�绱㈠�缁存���
CREATE TABLE sales_mar_2002_temp
NOLOGGING AS SELECT * FROM sales
PARTITION (sales_MAR_2002);
ALTER TABLE sales TRUNCATE PARTITION sales_MAR_2002;
SELECT COUNT(*)
FROM sales PARTITION (sales_MAR_2002);
ALTER TABLE sales_mar_2002_temp COMPRESS;
ALTER TABLE sales_mar_2002_temp NOCOMPRESS;
CREATE BITMAP INDEX sales_prod_mar_2002_bix
ON sales_mar_2002_temp (prod_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_cust_mar_2002_bix
ON sales_mar_2002_temp (cust_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_time_mar_2002_bix
ON sales_mar_2002_temp (time_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_channel_mar_2002_bix
ON sales_mar_2002_temp (channel_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_promo_mar_2002_bix
ON sales_mar_2002_temp (promo_id)
NOLOGGING COMPUTE STATISTICS ;
�ㄤ�涓�互
SH
�ㄦ�韬�唤�诲���SQL*Plus 浼��涓���ц�浠ヤ��戒护��
create_global_index.sql
�����
SALES
琛ㄤ���缓涓�釜涓茶����涓�储寮��
CREATE UNIQUE INDEX sales_pk
ON sales (prod_id, cust_id, promo_id, channel_id, time_id)
NOLOGGING COMPUTE STATISTICS;
杩���介�瑕�������/p>
����ц�
add_sales_pk.sql
�����缓涓�釜�╃�姝ょ储寮��绾��锛�/p>
ALTER TABLE sales ADD CONSTRAINT sales_pk
PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id) USING INDEX;
璇锋敞���濡��浣跨��ㄥ�绱㈠�瀹��绾��锛��蹇�』瀵硅�浜ゆ���〃涔��涔�����绾��锛��琛�互涓��浠ゆ�
add_salestemp_pk.sql
���瀹��姝や换�°�
ALTER TABLE sales_mar_2002_temp
ADD CONSTRAINT sales_mar_2002_temp_pk
PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id)
DISABLE VALIDATE;
褰���ц�璇ユ�璇㈡�锛���ㄧ��d�涓��琛���㈢�姝ラ����灏���帮��ㄨ�琛���虹淮�ゆ�浣��锛�娇�ㄥ�灞�储寮��浜�苟���璇㈣���病��换浣�奖���
璇ユ�璇�strong>涓��澶辫触���杩�����锛�����浜ゆ��戒护���涔��锛��璇㈢����绔����������racle �版�搴���″�浼��璇��绉���典���font face="Courier New, Courier, mono">璇诲�
涓����/font>锛�苟涓��浠ユ�渚��楂������鸿〃��储寮�淮�ゆ�浣�����浼�����浣跨���������/p>
ALTER TABLE sales
EXCHANGE PARTITION sales_mar_2002
WITH TABLE sales_mar_2002_temp
INCLUDING INDEXES UPDATE GLOBAL INDEXES;
灏界�杩��涓�釜 DDL �戒护锛�����涓哄�灞�储寮��浣�负
PARTITION EXCHANGE
����戒护����ㄥ�杩��缁存������互������涓���堕���/p>
�ㄥ����锛����储寮�����缁存����涔��浠��������琛�互涓��浠ゆ�
show_sales_idx_status.sql
�����/p>
SELECT ui.index_name,
DECODE(uip.status,null,ui.status,uip.status) status,
count(*) num_of_part
FROM user_ind_partitions uip, user_indexes ui
WHERE ui.index_name=uip.index_name(+)
AND ui.table_name='SALES'
GROUP BY ui.index_name,
DECODE(uip.status,null,ui.status,uip.status);
���浜ゆ��扮� Q1-2001 ����版����灞�储寮��浣�负
PARTITION EXCHANGE
�戒护����ㄥ�缁存�������涓��褰卞�浠讳����浣跨���/p>
�ヤ��ュ���┒娌℃��ㄥ�绱㈠�缁存����涓恒�