oracle ETL

一、ETL常用技术

1、SQL Loader (略）

2、外部表例子
 
 <1>、create directory bdump as 'D:\oracle\admin\ORCL\bdump'

 <2>、创建表
         
        create table e_table (empno int,ename varchar2(10),age int,salary number(10,2))
         organization external(
         type oracle_loader
         default directory bdump
         Access parameters(records delimited by newline
                            fields terminated by ',')
         Location('data.txt')
      ) reject limit 1000

 

 

<3>、Select * from e_table where empno =1;

2、多表插入示例

       create table tb1 as select empno,ename,sal from emp where 1=2;
       create table tb2 as select empno, deptno,comm from emp where 1=2;

    insert all
    into tb1 values(empno,ename,sal)
    into tb2 values(empno,deptno,comm)
    Select empno,ename,deptno,comm,sal from emp where empno=7900;

   insert all
   When sal>700 then
    into tb1 values(empno,ename,sal)
   When comm>800  then
   into tb2 values(empno,deptno,comm)
   Select empno,ename,deptno,comm,sal from emp where empno=7900;

      insert first
    When sal>700 then
     into tb1 values(empno,ename,sal)
    When deptno=30  then
    into tb2 values(empno,deptno,comm)
    Select empno,ename,deptno,comm,sal from emp where empno=7900;

 

3、合并功能 并

 A、Create table tb1 as select * from emp where 1=2;

 B、
  Merge into tb1 c
  using emp e
  on(c.empno=e.empno)
  when matched then
      update set
                      c.ename=e.ename,
                      c.sal=e.sal,
                      c.comm=e.comm
  When not matched then
            insert   values(e. EMPNO, e.ENAME,e.JOB,e.MGR,e.HIREDATE,e.SAL,e.COMM,e.DEPTNO); 

 

4、表函数功能

  create type mytype as object(id number(10),name varchar2(20));
  create type mytypelist as table of mytype;
  create or replace function mypipe
    return mytypelist pipelined
  is
     v_mytype mytype;
  begin
    for v_count in 1..20 loop
        v_mytype:=mytype(v_count,'zhangsan');
        pipe row(v_mytype);
   end loop;
   return;
 end;
 
select * from table(mypipe);

 

Select ename,hiredate, sal, lag(sal,1,0) over (order by hiredate) as prev_sal
From emp
Where job='CLERK'

 

二、分区操作

1、分区：将表数据划分成更小的子集。
   经验：在一个表的数据超过2000万条或占用2G空间时，建议建立分区表。

2、范围分区
　 使用列值的范围来确定一个数据行被插入到那个分区中

 create table myrange
 (empno number(4),
  ename varchar2(13),
  hiredate date,
  sale number(10))
 PARTITION BY RANGE (hiredate)
 (PARTITION p_2007 VALUES LESS THAN (to_date('2008-1-1','yyyy-mm-dd')) TABLESPACE users,
  PARTITION p_2008 VALUES LESS THAN (to_date('2009-1-1','yyyy-mm-dd')),
  PARTITION p_other VALUES LESS THAN (MAXVALUE)
 )

　select * from myrange partition(p_2007);

3、列表分区
create table mylist
(empno number(4),
 ename varchar2(13),
 hiredate date,
 sale number(10))
PARTITION BY LIST (ename)
(PARTITION p_manager VALUES ('KING','CLARK','SCOTT'),
 PARTITION p_boss VALUES ('TINA'),
 PARTITION p_CLARK VALUES ('SMITH','ALLEN','WARD','JONES','MARTIN')
)

insert into mylist
select empno,ename,hiredate,sal from scott.emp
where ename='KING';

4、散列分区

create table myhash1
 (empno number(4),
  ename varchar2(13),
  hiredate date)
 PARTITION BY HASH (empno)
 (PARTITION h1,
  PARTITION h2,
  PARTITION h3)

create table myhash2
 (empno number(4),
  ename varchar2(13),
  hiredate date)
 PARTITION BY HASH (empno)
 PARTITIONS 3
 STORE in(USERS，USERS，USERS)

5、组合分区
create table myrh
 (empno number(4),
  ename varchar2(13),
  hiredate date,
  sale number(10))
  PARTITION BY RANGE (hiredate)
    SUBPARTITION BY HASH (empno)
    SUBPARTITIONS 2
    STORE IN (users, users)
 (
   PARTITION p_2000 VALUES LESS THAN (to_date('2001-1-1','yyyy-mm-dd')),
   PARTITION p_2001 VALUES LESS THAN (to_date('2002-1-1','yyyy-mm-dd')),
   PARTITION p_other VALUES LESS THAN (MAXVALUE)
 )

6、分区索引
A、局部索引：索引中的分区与基础表的分区逐个匹配，也称分区索引

create index myemp_idx_t on myemp(hiredate)
local
(
partition idx_1 tablespace idxtbs1,
partition idx_2 tablespace idxtbs2,
partition idx_3 tablespace idxtbs3
);

B、全局索引，前缀局部索引，非前缀局部索引

create index myemp_idx_t on myrange(sale)
global partition by range(sale)
(
partition idx_1 values less than (1000) tablespace idxtbs1,
partition idx_2 values less than (5000) tablespace idxtbs2,
partition idx_3 values less than (maxvalue) tablespace idxtbs3
);

7、前缀索引

create table p_table
(a int,
 b int
)
partition by range(a)
(
  partition part_1 values less than (2),
  partition part_2 values less than (3)
);

create index local_prefixed on p_table(a, b) local;

create index local_nonprefixed on p_table(b) local;

8、添加分区、删除分区、删除分区数据

alter table myrange add partition p_2011
   values less than(to_date('2012-1-1','yyyy-mm-dd'));

alter table myrange truncate partition p_2009;

alter table myrange drop partition p_2009;

9、交换分区
create table mytemp as select ＊ from myrange where 1=2;

alter table mytest exchange partition
 p_2009 with table mytemp

alter table myrange move partition p_2009 tablespace mytbs1;

10、相看分区信息

DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS

 

===============================================================================
11g下分区新特性
===============================================================================

1、间隔分区

create table myInterval
 (empno number(4),
  ename varchar2(13),
  hiredate date,
  sale number(10))
 PARTITION BY RANGE (hiredate)
 INTERVAL (NUMTOYMINTERVAL(1,'year'))
 (PARTITION p_2008 VALUES LESS THAN (to_date('2009-1-1','yyyy-mm-dd')),
  PARTITION p_2009 VALUES LESS THAN (to_date('2010-1-1','yyyy-mm-dd')),
  PARTITION p_2010 VALUES LESS THAN (to_date('2011-1-1','yyyy-mm-dd'))
 )

操作
insert into myInterval values(1,'tina',to_date('20120301','yyyymmdd'),1000);

select * from user_tab_partitions;

2、虚拟列分区
 create table myTotal
  (empno number(4),
   ename varchar2(13),
   hiredate date,
   sale number(10)，
   total_sale as (sale*12)
   )
  PARTITION BY RANGE (total_sale)
  (PARTITION p1 VALUES LESS THAN (20000),
   PARTITION p2 VALUES LESS THAN (50000),
   PARTITION p3 VALUES LESS THAN (MAXVALUE)
  )

3、系统分区
CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
 PARTITION p1 TABLESPACE tbs_1,
 PARTITION p2 TABLESPACE tbs_2,
 PARTITION p3 TABLESPACE tbs_3,
 PARTITION p4 TABLESPACE tbs_4
);

4、引用分区

创建主键表
CREATE TABLE orders
  ( order_id     NUMBER(12) ,
  　order_date   DATE,
    order_mode   VARCHAR2(8),
    customer_id  NUMBER(6),
    order_status NUMBER(2),
    order_total  NUMBER(8,2),
    sales_rep_id NUMBER(6),
    promotion_id NUMBER(6),
    CONSTRAINT   orders_pk PRIMARY KEY(order_id)
  )
 
 PARTITION BY RANGE(order_date)
 (PARTITION Q105 VALUES LESS THAN (TO_DATE('1-4-2005','DD-MM-YYYY')),
  PARTITION Q205 VALUES LESS THAN (TO_DATE('1-7-2005','DD-MM-YYYY')),
  PARTITION Q305 VALUES LESS THAN (TO_DATE('1-10-2005','DD-MM-YYYY')),
  PARTITION Q405 VALUES LESS THAN (TO_DATE('1-1-2006','DD-MM-YYYY')));

创建外键表
CREATE TABLE order_items
 ( order_id     NUMBER(12) NOT NULL, line_item_id NUMBER(3) NOT NULL,
   product_id   NUMBER(6) NOT NULL,  unit_price   NUMBER(8,2),
   quantity     NUMBER(8),
   CONSTRAINT   order_items_fk
             FOREIGN KEY(order_id) REFERENCES orders(order_id)
) PARTITION BY REFERENCE(order_items_fk);