dml次数的view oracle 记录partition_oracle最后dml记录 - CSDN
精华内容
参与话题
  • Oracle 中truncate与delete的区别

    万次阅读 2007-05-30 10:54:00
    truncate是oracle中独有的关键字吧!它的作用是清空一个表格,在删除数据方面,其与delete有一些区别,以便自己记住: 1、在功能上,truncate是清空一个表的内容,它相当于delete from table_name。2、delete是dml...

    truncate是oracle中独有的关键字吧!它的作用是清空一个表格,在删除数据方面,其与delete有一些区别,以便自己记住:

     


    1、在功能上,truncate是清空一个表的内容,它相当于delete from table_name。
    2、delete是dml操作,truncate是ddl操作;因此,用delete删除整个表的数据时,会产生大量的roolback,占用很多的rollback segments, 而truncate不会。
    3、在内存中,用delete删除数据,表空间中其被删除数据的表占用的空间还在,便于以后的使用,另外它是“假相”的删除,相当于windows中用delete删除数据是把数据放到回收站中,还可以恢复,当然如果这个时候重新启动系统(OS或者RDBMS),它也就不能恢复了!
    而用truncate清除数据,内存中表空间中其被删除数据的表占用的空间会被立即释放,相当于windows中用shift+delete删除数据,不能够恢复!
    4、truncate 调整high water mark 而delete不;truncate之后,TABLE的HWM退回到 INITIAL和NEXT的位置(默认)delete 则不可以。
    5、truncate 只能对TABLE,delete 可以是table,view,synonym。
    6、TRUNCATE TABLE 的对象必须是本模式下的,或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的,或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限。
    7、在外层中,truncate或者delete后,其占用的空间都将释放。
    8、truncate和delete只删除数据,而drop则删除整个表(结构和数据)。 

     

    delete 用法

    Oracle Delete Statements
    Version 10.2
     
    Basic Delete Statements
    1.Delete All Rows:  DELETE <table_name>
                                   or
                                   DELETE FROM <table_name>;
                                   CREATE TABLE t  AS
                                   SELECT *
                                    FROM all_tables;

                                    SELECT COUNT(*)
                                    FROM t;

    DELETE FROM t;

    COMMIT;

    SELECT COUNT(*)
    FROM t;


    2.Delete Selective Rows

     DELETE FROM <table_name>
    WHERE <condition>;
    CREATE TABLE t AS
    SELECT *
    FROM all_tables;

    SELECT COUNT(*)
    FROM t;

    DELETE FROM t
    WHERE table_name LIKE '%MAP';

    COMMIT;

    SELECT COUNT(*)
    FROM t;


    3.Delete From A SELECT Statement

    DELETE FROM (<SELECT Statement>);
    CREATE TABLE t AS
    SELECT *
    FROM all_tables;

    SELECT COUNT(*)
    FROM t;

    DELETE FROM (
      SELECT * FROM t WHERE table_name LIKE '%MAP');

    SELECT COUNT(*)
    FROM t;


    4.Delete With Returning Clause

     DELETE FROM (<SELECT Statement>);
    CREATE TABLE t AS
    SELECT *
    FROM all_tables;

    set serveroutput on

    DECLARE
     r  urowid;
    BEGIN
      DELETE FROM t
      WHERE rownum = 1
      RETURNING rowid INTO r;

      dbms_output.put_line(r);
    END;
    /

    5.Delete Restricted To A Partition

    DELETE FROM <table_name>
    PARTITION <partition_name>;
    DELETE FROM sales PARTITION (q1_2001_invoices);
    Delete From A Remote Database DELETE FROM <table_name>@<database_link>
    DELETE FROM t@remote_db;
     

     在删除大数据量时(一个表中大部分数据时),

    方法:
    1、先将不需要删除的数据复制到一个临时表中
    2、trunc table 表
    3、将不需要删除的数据复制回来。

    delete只循环了一次,(去查并删)
    游标循环了二次,(先查出来,再循环一次删)


    展开全文
  • oracle分区表详解

    万次阅读 2013-06-25 21:57:24
    分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。 Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(SEGMENT),可以存放到不同的表空间中。查询时可以通过查询...

    分区表理论知识

    Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。

    Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(SEGMENT),可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据,也可以通过在查询时直接指定分区的方法来进行查询。

    When to Partition a Table什么时候需要分区表,官网的2个建议如下:

    (1)Tables greater than 2GB should always be considered for partitioning.

    (2)Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

    oracle 10g中最多支持:1024k-1个分区:

    Tables can be partitioned into up to 1024K-1 separate partitions

    联机文档上有关分区表和索引的说明:

    Partitioned Tables and Indexes

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604

    分区提供以下优点:

    (1)由于将数据分散到各个分区中,减少了数据损坏的可能性;

    (2)可以对单独的分区进行备份和恢复;

    (3)可以将分区映射到不同的物理磁盘上,来分散IO;

    (4)提高可管理性、可用性和性能。

    Oracle 10g提供了以下几种分区类型:

    (1)范围分区(range);

    (2)哈希分区(hash);

    (3)列表分区(list);

    (4)范围-哈希复合分区(range-hash);

    (5)范围-列表复合分区(range-list)。

    Range分区:

      Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中。

    如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的时候,需要指定基于的列,以及分区的范围值。

    在按时间分区时,如果某些记录暂无法预测范围,可以创建maxvalue分区,所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

    如:

    create table pdba (id number, time date) partition by range (time)

    (

    partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

    partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

    partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

    partition p4 values less than (maxvalue)

    )

    Hash分区:

      对于那些无法有效划分范围的表,可以使用hash分区,这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的hash值自动分配,因此你并不能控制也不知道哪条记录会被放到哪个分区中,hash分区也可以支持多个依赖列。

    如:

    create table test

    (

    transaction_id number primary key,

    item_id number(8) not null

    )

    partition by hash(transaction_id)

    (

    partition part_01 tablespace tablespace01,

    partition part_02 tablespace tablespace02,

    partition part_03 tablespace tablespace03

    );

    在这里,我们指定了每个分区的表空间。

    List分区:

      List分区也需要指定列的值,其分区值必须明确指定,该分区列只能有一个,不能像range或者hash分区那样同时指定多个列做为分区依赖列,但它的单个分区对应值可以是多个。

      在分区时必须确定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/更新就会失败,因此通常建议使用list分区时,要创建一个default分区存储那些不在指定范围内的记录,类似range分区中的maxvalue分区。

    在根据某字段,如城市代码分区时,可以指定default,把非分区规则的数据,全部放到这个default分区。

    如:

    create table custaddr
    (

    id varchar2(15 byte) not null,

    areacode varchar2(4 byte)
    )

    partition by list (areacode)
    ( partition t_list025 values ('025'), 
    partition t_list372 values ('372') , 
    partition t_list510 values ('510'),

    partition p_other values (default)

    )

    组合分区:

    如果某表按照某列分区之后,仍然较大,或者是一些其它的需求,还可以通过分区内再建子分区的方式将分区再分区,即组合分区的方式。

      组合分区呢在10g中有两种:range-hash,range-list。注意顺序,根分区只能是range分区,子分区可以是hash分区或list分区。

    如:

    create table test

    (

    transaction_id number primary key,

    transaction_date date

    )

    partition by range(transaction_date) subpartition by hash(transaction_id)

    subpartitions 3 store in (tablespace01,tablespace02,tablespace03)

    (

    partition part_01 values less than(to_date(’2009-01-01’,’yyyy-mm-dd’)),

    partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),

    partition part_03 values less than(maxvalue)

    );

    create table emp_sub_template (deptno number, empname varchar(32), grade number)

    partition by range(deptno) subpartition by hash(empname)

    subpartition template

    (subpartition a tablespace ts1,

    subpartition b tablespace ts2,

    subpartition c tablespace ts3,

    subpartition d tablespace ts4

    )

    (partition p1 values less than (1000),

    partition p2 values less than (2000),

    partition p3 values less than (maxvalue)

    );

    create table quarterly_regional_sales

    (deptno number, item_no varchar2(20),

    txn_date date, txn_amount number, state varchar2(2))

    tablespace ts4

    partition by range (txn_date)

    subpartition by list (state)

    (partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy'))

    (subpartition q1_1999_northwest values ('or', 'wa'),

    subpartition q1_1999_southwest values ('az', 'ut', 'nm'),

    subpartition q1_1999_northeast values ('ny', 'vm', 'nj'),

    subpartition q1_1999_southeast values ('fl', 'ga'),

    subpartition q1_1999_northcentral values ('sd', 'wi'),

    subpartition q1_1999_southcentral values ('ok', 'tx')

    ),

    partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy'))

    (subpartition q2_1999_northwest values ('or', 'wa'),

    subpartition q2_1999_southwest values ('az', 'ut', 'nm'),

    subpartition q2_1999_northeast values ('ny', 'vm', 'nj'),

    subpartition q2_1999_southeast values ('fl', 'ga'),

    subpartition q2_1999_northcentral values ('sd', 'wi'),

    subpartition q2_1999_southcentral values ('ok', 'tx')

    ),

    partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))

    (subpartition q3_1999_northwest values ('or', 'wa'),

    subpartition q3_1999_southwest values ('az', 'ut', 'nm'),

    subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),

    subpartition q3_1999_southeast values ('fl', 'ga'),

    subpartition q3_1999_northcentral values ('sd', 'wi'),

    subpartition q3_1999_southcentral values ('ok', 'tx')

    ),

    partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))

    (subpartition q4_1999_northwest values ('or', 'wa'),

    subpartition q4_1999_southwest values ('az', 'ut', 'nm'),

    subpartition q4_1999_northeast values ('ny', 'vm', 'nj'),

    subpartition q4_1999_southeast values ('fl', 'ga'),

    subpartition q4_1999_northcentral values ('sd', 'wi'),

    subpartition q4_1999_southcentral values ('ok', 'tx')

    )

    );

    在Oracle 11g中,组合分区功能这块有所增强,又增加了range-range,list-range,

    list-list,list-hash,并且 11g里面还支持Interval分区和虚拟列分区。

    这块可以参考Blog

    Oracle 11g 新特性简介

    http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx

    分区表  Interval分区  虚拟列 按星期分区表

    http://blog.csdn.net/tianlesoftware/archive/2010/06/10/5662337.aspx

    普通表转分区表方法

    将普通表转换成分区表有4种方法:

    1. Export/import method

    2. Insert with a subquery method

    3. Partition exchange method

    4. DBMS_REDEFINITION

    具体参考:

    How to Partition a Non-partitioned Table [ID 1070693.6]

    http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

    逻辑导出导入这里就不做说明,我们看看其他三种方法。

    2.1 插入: Insert with a subquery method

    这种方法就是使用insert 来实现。 当然在创建分区表的时候可以一起插入数据,也可以创建好后在insert 进去。这种方法采用DDL语句,不产生UNDO,只产生少量REDO,建表完成后数据已经在分布到各个分区中。

    SQL> select count(*) from dba;

    COUNT(*)

    ----------

    2713235

    SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

    会话已更改。

    SQL> select time_fee from dba where rownum<5;

    TIME_FEE

    -------------------

    2011-02-17 19:29:09

    2011-02-17 19:29:15

    2011-02-17 19:29:18

    2011-02-17 19:29:20

    SQL>

    2.1.1 Oracle 11gInterval

    在11g里的Interval创建,这种方法对没有写全的分区会自动创建。 比如我这里只写了1月日期,如果插入的数据有其他月份的,会自动生成对应的分区。

    /* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */

    CREATE TABLE intervaldave

    PARTITION BY RANGE (time_fee)

    INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

    (PARTITION part1

    VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))

    AS

    SELECT ID, TIME_FEE FROM DAVE;

    SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE';

    TABLE_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    INTERVALDAVE PART1

    INTERVALDAVE SYS_P24

    INTERVALDAVE SYS_P25

    INTERVALDAVE SYS_P26

    INTERVALDAVE SYS_P33

    INTERVALDAVE SYS_P27

    INTERVALDAVE SYS_P28

    2.1.2 Oracle 10g 版本

    在10g里面,我需要写全所有的分区。

    sql> create table pdba (id, time) partition by range (time)

    2 (partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

    3 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

    4 partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

    5 partition p4 values less than (maxvalue))

    6 as select id, time_fee from dba;

    表已创建。

    SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';

    TABLE_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    PDBA P1

    PDBA P2

    PDBA P3

    PDBA P4

    sql> select count(*) from pdba partition (p1);

    count(*)

    ----------

    1718285

    sql> select count(*) from pdba partition (p2);

    count(*)

    ----------

    183667

    sql> select count(*) from pdba partition (p3);

    count(*)

    ----------

    188701

    sql> select count(*) from pdba partition (p4);

    count(*)

    ----------

    622582

    sql>

    现在分区表已经建好了,但是表名不一样,需要用rename对表重命名一下:

    SQL> rename dba to dba_old;

    表已重命名。

    SQL> rename pdba to dba;

    表已重命名。

    SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA';

    TABLE_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    DBA P1

    DBA P2

    DBA P3

    DBA P4

    2.2 . 交换分区:Partition exchange method

    这种方法只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。

    交换分区的操作步骤如下:

    1. 创建分区表,假设有2个分区,P1P2.

    2. 创建表A存放P1规则的数据。

    3. 创建表存放P2规则的数据。

    4. 用表P1 分区交换。 把表A的数据放到到P1分区

    5. 用表p2 分区交换。 把表B的数据存放到P2分区。

    创建分区表:

    sql> create table p_dba

    2 (id number,time date)

    3 partition by range(time)

    4 (

    5 partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')),

    6 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd'))

    7 );

    表已创建。

    注意:我这里只创建了2个分区,没有创建存放其他数据的分区。

    创建2个分别对应分区的基表:

    SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE time_fee<TO_DATE('2010-09-1', 'YYYY-MM-DD');

    表已创建。

    SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE time_fee<TO_DATE('2010-11-1', 'YYYY-MM-DD') and time_fee>TO_DATE('2010-09-1', 'YYYY-MM-DD');

    表已创建。

    SQL> select count(*) from dba_p1;

    COUNT(*)

    ----------

    1536020

    SQL> select count(*) from dba_p2;

    COUNT(*)

    ----------

    365932

    SQL>

    2个基表与2个分区进行交换:

    SQL> alter table p_dba exchange partition p1 with table dba_p1;

    表已更改。

    SQL> alter table p_dba exchange partition p2 with table dba_p2;

    表已更改。

    查询2个分区:

    SQL> select count(*) from p_dba partition(p1);

    COUNT(*)

    ----------

    1536020

    SQL> select count(*) from p_dba partition(p2);

    COUNT(*)

    ----------

    365932

    注意:数据和之前的基表一致。

    查询原来的2个基表:

    SQL> select count(*) from dba_p2;

    COUNT(*)

    ----------

    0

    SQL> select count(*) from dba_p1;

    COUNT(*)

    ----------

    0

    注意: 2个基表的数据变成成0

    在这里我们看一个问题,一般情况下,我们在创建分区表的时候,都会有一个其他分区,用来存放不匹配分区规则的数据。 在这个例子中,我只创建了2个分区,没有创建maxvalue分区。 现在我来插入一条不满足规则的数据,看结果:

    SQL> insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'));

    insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'))

    *

    第 1 行出现错误:

    ORA-14400: 插入的分区关键字未映射到任何分区

    SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd'));

    已创建 1 行。

    SQL> select * from p_dba where id=999999;

    ID TIME

    ---------- --------------

    999999 29-12月-09

    SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

    会话已更改。

    SQL> select * from p_dba where id=999999;

    ID TIME

    ---------- -------------------

    999999 2009-12-29 00:00:00

    SQL>

    通过这个测试可以清楚,如果插入的数据不满足分区规则,会报ORA-14400错误。

    2.3 . 使用在线重定义:DBMS_REDEFINITION

    在线重定义能保证数据的一致性,在大部分时间内,表都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。

    关于DBMS_REDEFINITION的介绍,参考官方连接:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC

    关于用在线重定义创建分区表,参考:

    How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

    http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218693.aspx

    这个功能只在9.2.0.4以后的版本才有,在线重定义表具有以下功能:

    (1)修改表的存储参数;

    (2)将表转移到其他表空间;

    (3)增加并行查询选项;

    (4)增加或删除分区;

    (5)重建表以减少碎片;

    (6)将堆表改为索引组织表或相反的操作;

    (7)增加或删除一个列。

    使用在线重定义的一些限制条件:

    (1) There must be enough space to hold two copies of the table.

    (2) Primary key columns cannot be modified.

    (3) Tables must have primary keys.

    (4) Redefinition must be done within the same schema.

    (5) New columns added cannot be made NOT NULL until after the redefinition operation.

    (6) Tables cannot contain LONGs, BFILEs or User Defined Types.

    (7) Clustered tables cannot be redefined.

    (8) Tables in the SYS or SYSTEM schema cannot be redefined.

    (9) Tables with materialized view logs or materialized views defined on them cannot be redefined.

    (10) Horizontal sub setting of data cannot be performed during the redefinition.

    在Oracle 10.2.0.4和11.1.0.7 版本下,在线重定义可能会遇到如下bug:

    Bug 7007594 - ORA-600 [12261]

    http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

    在线重定义的大致操作流程如下:

    (1)创建基础表A,如果存在,就不需要操作。

    (2)创建临时的分区表B。

    (3)开始重定义,将基表A的数据导入临时分区表B。

    (4)结束重定义,此时在DB的 Name Directory里,已经将2个表进行了交换。即此时基表A成了分区表,我们创建的临时分区表B 成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。

    下面看一个示例:

    1. 创建基本表和索引

    sql> conn icd/icd;

    已连接。

    sql> create table unpar_table (

    2 id number(10) primary key,

    3 create_date date

    4 );

    表已创建。

    sql> insert into unpar_table select rownum, created from dba_objects;

    已创建72288行。

    sql> create index create_date_ind on unpar_table(create_date);

    索引已创建。

    sql> commit;

    提交完成。

    2. 收集表的统计信息

    sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);

    pl/sql 过程已成功完成。

    3. 创建临时分区表

    sql> create table par_table (id number primary key, time date) partition by range (time)

    2 (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')),

    3 partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),

    4 partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')),

    5 partition p4 values less than (maxvalue));

    表已创建。

    4. 进行重定义操作

    4.1 检查重定义的合理性

    sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');

    pl/sql 过程已成功完成。

    4.2 如果4.1 没有问题,开始重定义,这个过程可能要等一会。

    这里要注意:如果分区表和原表列名相同,可以用如下方式进行:

    SQL> BEGIN

    DBMS_REDEFINITION.start_redef_table(

    uname => 'ICD',

    orig_table => 'unpar_table',

    int_table => 'par_table');

    END;

    /

    如果分区表的列名和原表不一致,那么在开始重定义的时候,需要重新指定映射关系:

    SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(

    'ICD',

    'unpar_table',

    'par_table',

    'ID ID, create_date TIME', -- 在这里指定新的映射关系

    DBMS_REDEFINITION.CONS_USE_PK);

    这一步操作结束后,数据就已经同步到这个临时的分区表里来了。

    4.3 同步新表,这是可选的操作

    SQL> BEGIN

    2 dbms_redefinition.sync_interim_table(

    3 uname => 'ICD',

    4 orig_table => 'unpar_table',

    5 int_table => 'par_table');

    6 END;

    7 /

    PL/SQL 过程已成功完成。

    4.4 创建索引,在线重定义只重定义数据,索引还需要单独建立。

    sql> create index create_date_ind2 on par_table(time);

    索引已创建。

    4.5 收集新表的统计信息

    sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);

    pl/sql 过程已成功完成。

    4.6 结束重定义

    SQL> BEGIN

    2 dbms_redefinition.finish_redef_table(

    3 uname => 'ICD',

    4 orig_table => 'unpar_table',

    5 int_table => 'par_table');

    6 END;

    7 /

    PL/SQL 过程已成功完成。

    结束重定义的意义:

    基表unpar_table 和临时分区表par_table 进行了交换。 此时临时分区表par_table成了普通表,我们的基表unpar_table成了分区表。

    我们在重定义的时候,基表unpar_table是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。

    5. 删除临时表

    SQL> DROP TABLE par_table;

    表已删除。

    6. 索引重命名

    SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

    索引已更改。

    7. 验证

    sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE';

    par

    ---

    yes

    sql> select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE';

    partition_name

    ------------------------------

    p1

    p2

    p3

    p4

    sql> select count(*) from unpar_table;

    count(*)

    ----------

    72288

    sql> select count(*) from unpar_table partition (p4);

    count(*)

    ----------

    72288

    sql>

    分区表的其他操作

    3.1 添加新的分区

    添加新的分区有2中情况:

    (1)原分区里边界是maxvalue或者default。 这种情况下,我们需要把边界分区drop掉,加上新分区后,在添加上新的分区。 或者采用split,对边界分区进行拆分。

    (2)没有边界分区的。 这种情况下,直接添加分区就可以了。

    以边界分区添加新分区示例:

    1)分区表和索引的信息如下:

    SQL> create table custaddr

    2 (

    3 id varchar2(15 byte) not null,

    4 areacode varchar2(4 byte)

    5 )

    6 partition by list (areacode)

    7 (

    8 partition t_list556 values ('556') tablespace icd_service,

    9 partition p_other values (default)tablespace icd_service

    10 );

    表已创建。

    SQL> create index ix_custaddr_id on custaddr(id)

    2 local (

    3 partition t_list556 tablespace icd_service,

    4 partition p_other tablespace icd_service

    5 );

    索引已创建。

    2)插入几条测试数据:

    SQL> insert into custaddr values('1','556');

    已创建 1 行。

    SQL> insert into custaddr values('2','551');

    已创建 1 行。

    SQL> insert into custaddr values('3','555');

    已创建 1 行。

    SQL> commit;

    提交完成。

    SQL> select * from custaddr;

    ID AREA

    --------------- ----

    1 556

    2 551

    3 555

    SQL> select * from custaddr partition(t_list556);

    ID AREA

    --------------- ----

    1 556

    SQL>

    3)删除default分区

    sql> alter table custaddr drop partition p_other;

    表已更改。

    sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

    table_name partition_name

    ------------------------------ ------------------------------

    custaddr t_list556

    4)添加新分区

    SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service;

    表已更改。

    SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

    TABLE_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    CUSTADDR T_LIST556

    CUSTADDR T_LIST551

    5)添加default 分区

    SQL> alter table custaddr add partition p_other values (default) tablespace icd_service;

    表已更改。

    SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

    TABLE_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    CUSTADDR T_LIST556

    CUSTADDR T_LIST551

    CUSTADDR P_OTHER

    6)对于局部索引,oracle会自动增加一个局部分区索引。验证一下:

    sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id';

    owner index_name table_name

    ---------------------- ------------------------------ ------------------

    icd ix_custaddr_id custaddr

    sql> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='ix_custaddr_id';

    index_owner index_name partition_name

    ------------------------------ ------------------------------ ------------------

    icd ix_custaddr_id p_other

    icd ix_custaddr_id t_list551

    icd ix_custaddr_id t_list556

    分区索引自动创建了。

    3.2 split 分区拆分

    在3.1 中,我们说明了可以使用split的方式来添加分区。 这里我们用split方法继续上面的实验。

    sql> alter table custaddr split partition p_other values('552') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service);

    表已更改。

    --注意这里红色的地方,如果是Range类型的,使用atList使用Values

    SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

    TABLE_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    CUSTADDR T_LIST556

    CUSTADDR T_LIST551

    CUSTADDR T_LIST552

    CUSTADDR P_OTHER

    SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID';

    index_owner index_name partition_name

    ------------------------------ ------------------------------ ------------------

    icd ix_custaddr_id p_other

    icd ix_custaddr_id t_list551

    icd ix_custaddr_id t_list552

    icd ix_custaddr_id t_list556

    注意:分区表会自动维护局部分区索引。全局索引会失效,需要进行rebuild

    3.3 合并分区Merge

    相邻的分区可以merge为一个分区,新分区的下边界为原来边界值较低的分区,上边界为原来边界值较高的分区,原先的局部索引相应也会合并,全局索引会失效,需要rebuild

    SQL> alter table custaddr merge partitions t_list552,p_other into partition p_other;

    表已更改。

    SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID';

    index_owner index_name partition_name

    -------------------- ------------------------------ ------------------

    icd ix_custaddr_id p_other

    icd ix_custaddr_id t_list551

    icd ix_custaddr_id t_list556

    SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

    table_name partition_name

    ------------------------------ ------------------------------

    custaddr t_list556

    custaddr t_list551

    custaddr p_other

    3.4 . 移动分区

    SQL> alter table custaddr move partition P_OTHER tablespace system;

    表已更改。

    SQL> alter table custaddr move partition P_OTHER tablespace icd_service;

    表已更改。

    注意:分区移动会自动维护局部分区索引,oracle不会自动维护全局索引,所以需要我们重新rebuild分区索引,具体需要rebuild哪些索引,可以通过dba_part_indexes,dba_ind_partitions去判断。

    SQL> Select index_name,status From user_indexes Where table_name='CUSTADDR';

    INDEX_NAME STATUS

    ------------------------------ --------

    IX_CUSTADDR_ID N/A

    3.5. Truncate分区

    SQL> select * from custaddr partition(T_LIST556);

    ID AREA

    --------------- ----

    1 556

    SQL> alter table custaddr truncate partition(T_LIST556);

    表被截断。

    SQL> select * from custaddr partition(T_LIST556);

    未选定行

    说明:

    Truncate相对delete操作很快,数据仓库中的大量数据的批量数据加载可能会有用到;截断分区同样会自动维护局部分区索引,同时会使全局索引unusable,需要重建

    3.6. Drop分区

    SQL> alter table custaddr drop partition T_LIST551;

    表已更改。

    SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

    TABLE_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    CUSTADDR T_LIST556

    CUSTADDR P_OTHER

    同样会自动维护局部分区索引,同时会使全局索引unusable,需要重建

    分区表的索引

    分区索引分为本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理而全局索引比较快。

    与索引有关的表:

    dba_part_indexes 分区索引的概要统计信息,可以得知每个表上有哪些分区索引,分区索引的类型(local/global)

    dba_ind_partitions 每个分区索引的分区级统计信息

    dba_indexes/dba_part_indexes 可以得到每个表上有哪些非分区索引

    Local索引肯定是分区索引,Global索引可以选择是否分区,如果分区,只能是有前缀的分区索引。

    分区索引分2类:有前缀(prefix)的分区索引无前缀(nonprefix)的分区索引:

    (1)有前缀的分区索引指包含了分区键,并且将其作为引导列的索引。

    如:

    create index i_id_global on PDBA(id) global --引导列

    2 partition by range(id) --分区键

    3 (partition p1 values less than (200),

    4 partition p2 values less than (maxvalue)

    5 );

    这里的ID 就是分区键,并且分区键id 也是索引的引导列。

    (2)无前缀的分区索引的不是以分区键开头,或者不包含分区键列

    如:

    create index ix_custaddr_local_id_p on custaddr(id)

    local (

    partition t_list556 tablespace icd_service,

    partition p_other tablespace icd_service

    )

    这个分区是按照areacode来的。但是索引的引导列是ID。 所以它就是非前缀分区索引。

    全局分区索引不支持非前缀的分区索引,如果创建,报错如下:

    SQL> create index i_time_global on PDBA(id) global --索引引导列

    2 partition by range(time) --分区建

    3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

    4 partition p2 values less than (maxvalue)

    5 );

    partition by range(time)

    *

    第 2 行出现错误:

    ORA-14038: GLOBAL 分区索引必须加上前缀

    4.1 Local 本地索引

    对于local索引,当表的分区发生变化时,索引的维护由Oracle自动进行。

    注意事项:

    (1) 局部索引一定是分区索引分区键等同于表的分区键

    (2) 前缀和非前缀索引都可以支持索引分区消除,前提是查询的条件中包含索引分区键。

    (3) 局部索引只支持分区内的唯一性,无法支持表上的唯一性,因此如果要用局部索引去给表做唯一性约束,则约束中必须要包括分区键列。

    (4) 局部分区索引是对单个分区的,每个分区索引只指向一个表分区;全局索引则不然,一个分区索引能指向n个表分区,同时,一个表分区,也可能指向n个索引分区,对分区表中的某个分区做truncate或者move,shrink等,可能会影响到n个全局索引分区,正因为这点,局部分区索引具有更高的可用性。

    (5) 位图索引必须是局部分区索引。

    (6) 局部索引多应用于数据仓库环境中。

    (7) B树索引和位图索引都可以分区,但是HASH索引不可以被分区。

    示例:

    sql> create index ix_custaddr_local_id on custaddr(id) local;

    索引已创建。

    和下面SQL 效果相同,因为local索引就是分区索引:

    create index ix_custaddr_local_id_p on custaddr(id)

    local (

    partition t_list556 tablespace icd_service,

    partition p_other tablespace icd_service

    )

    SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;

    索引已创建。

    验证2个索引的类型:

    SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR';

    index_name table_name partition locali alignment

    ------------------------------ ---------- --------- ------ ------------

    ix_custaddr_local_areacode custaddr list local prefixed

    ix_custaddr_local_id custaddr list local non_prefixed

    因为我们的custaddr表是按areacode进行分区的,所以索引ix_custaddr_local_areacode是有前缀的索引(prefixed)。而ix_custaddr_local_id是非前缀索引。

    4.2 Global索引

    对于global索引,可以选择是否分区,而且索引的分区可以不与表分区相对应。全局分区索引只能是B树索引,到目前为止(10gR2),oracle只支持有前缀的全局索引。

    另外oracle不会自动的维护全局分区索引,当我们在对表的分区做修改之后,如果对分区进行维护操作时不加上update global indexes的话,通常会导致全局索引的INVALDED,必须在执行完操作后 REBUILD。

    注意事项:

    (1)全局索引可以分区,也可以是不分区索引,全局索引必须是前缀索引,即全局索引的索引列必须是以索引分区键作为其前几列。

    (2)全局索引可以依附于分区表;也可以依附于非分区表。

    (3)全局分区索引的索引条目可能指向若干个分区,因此,对于全局分区索引,即使只截断一个分区中的数据,都需要rebulid若干个分区甚至是整个索引。

    (4)全局索引多应用于oltp系统中。

    (5)全局分区索引只按范围或者散列分区,hash分区是10g以后才支持。

    (6) oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引,用消耗一定资源来换取高度的可用性。

    (7) 表用a列作分区,索引用b做局部分区索引,若where条件中用b来查询,那么oracle会扫描所有的表和索引的分区,成本会比分区更高,此时可以考虑用b做全局分区索引。

    注意:Oracle只支持2中类型的全局分区索引:

    range partitioned  Hash Partitioned.

    官网的说明如下:

    Global Partitioned Indexes

    Oracle offers two types of global partitioned index: range partitioned and hash partitioned.

    1Global Range Partitioned Indexes

    Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

    The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

    You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

    2Global Hash Partitioned Indexes

    Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

    3Maintenance of Global Partitioned Indexes

    By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

    ADD (HASH)

    COALESCE (HASH)

    DROP

    EXCHANGE

    MERGE

    MOVE

    SPLIT

    TRUNCATE

    示例全局索引,全局索引对所有分区类型都支持:

    sql> create index ix_custaddr_ global_id on custaddr(id) global;

    索引已创建。

    示例2:全局分区索引,只支持Range 分区和Hash 分区:

    1)创建2个测试分区表:

    sql> create table pdba (id number, time date) partition by range (time)

    2 (

    3 partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

    4 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

    5 partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

    6 partition p4 values less than (maxvalue)

    7 );

    表已创建。

    SQL> create table Thash

    2 (

    3 id number primary key,

    4 item_id number(8) not null

    5 )

    partition by hash(id)

    7 (

    8 partition part_01,

    9 partition part_02,

    10 partition part_03

    11 );

    表已创建。

    2)创建分区索引

    示例2:全局分区索引

    SQL> create index i_id_global on PDBA(id) global

    2 partition by range(id)

    3 (partition p1 values less than (200),

    4 partition p2 values less than (maxvalue)

    5 );

    索引已创建。

    --这个是有前缀的分区索引。

    SQL> create index i_time_global on PDBA(id) global

    2 partition by range(time)

    3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

    4 partition p2 values less than (maxvalue)

    5 );

    partition by range(time)

    *

    第 2 行出现错误:

    ORA-14038: GLOBAL 分区索引必须加上前缀

    SQL> create index i_time_global on PDBA(time) global

    2 partition by range(time)

    3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

    4 partition p2 values less than (maxvalue)

    5 );

    索引已创建。

    --有前缀的分区索引

    SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA';

    index_name table_name partition locali alignment

    ------------------------------ ---------- --------- ------ ------------

    i_id_global pdba range global prefixed

    i_time_global pdba range global prefixed

    SQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL

    2 PARTITION BY HASH (id)

    3 (PARTITION p1,

    4 PARTITION p2,

    5 PARTITION p3,

    6 PARTITION p4);

    索引已创建。

    只要索引的引导列包含分区键,就是有前缀的分区索引。

    4.3 索引重建问题

    1)分区索引

    对于分区索引,不能整体进行重建,只能对单个分区进行重建。语法如下:

    Alter index idx_name rebuild partition index_partition_name [online nologging]

    说明:

    online:表示重建的时候不会锁表。

    nologging:表示建立索引的时候不生成日志,加快速度。

    如果要重建分区索引,只能drop表原索引,在重新创建:

    SQL>create index loc_xxxx_col on xxxx(col) local tablespace SYSTEM;

    这个操作要求较大的临时表空间和排序区。

    示例:

    SQL> select index_name,partition_name from user_ind_partitions where index_name='I_TIME_GLOBAL';

    INDEX_NAME PARTITION_NAME

    ------------------------------ ------------------------------

    I_TIME_GLOBAL P1

    I_TIME_GLOBAL P2

    SQL> alter index I_TIME_GLOBAL rebuild partition p1 online nologging;

    索引已更改。

    SQL> alter index I_TIME_GLOBAL rebuild partition p2 online nologging;

    索引已更改。

    2)全局索引

    Oracle 会自动维护分区索引,对于全局索引,如果在对分区表操作时,没有指定update index,则会导致全局索引失效,需要重建。

    SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

    owner index_name table_name status

    ------------------------------ ------------------------------ ---------- -------

    sys ix_pdba_global pdba valid

    删除一个分区:

    SQL> alter table pdba drop partition p2;

    表已更改。

    SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

    owner index_name table_name status

    ------------------------------ ------------------------------ ---------- -------

    sys ix_pdba_global pdba valid

    split 分区:

    SQL> alter table pdba split partition P4 at(TO_DATE('2010-12-21 00:00:00','YYYY-MM-DD HH24:MI:SS')) into (partition P4, partition P5);

    表已更改。

    SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

    owner index_name table_name status

    ------------------------------ ------------------------------ ---------- -------

    sys ix_pdba_global pdba valid

    drop 分区时使用update indexes

    SQL> alter table pdba drop partition P4 UPDATE INDEXES;

    表已更改。

    SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

    owner index_name table_name status

    ---------------------- ------------------------------ ---------- -------

    sys ix_pdba_global pdba valid

    做了几个drop分区操作,全局索引没有失效,有点奇怪。 不过如果在生产环境中,还是小心点。

    重建全局索引命令如下:

    Alter index idx_name rebuild [online nologging]

    示例:

    SQL> Alter index ix_pdba_global rebuild online nologging;

    索引已更改。

    补充一点,分区表存储空间的问题:

    SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='DBA';

    TABLE_NAME PARTITION_NAME TABLESPACE_NAME
    ---------- ------------------------------ ------------------------------
    DBA P1 SYSTEM
    DBA P2 SYSTEM
    DBA P3 SYSTEM
    DBA P4 SYSTEM

    通过user_tab_partitions 表可以查看到每个分区对应的tablesapce_name. 但是,如果通过all_tables 表,却查不到分区表对应表空间的信息。


    分区表:
    SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DBA';

    OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
    ----- ---------- ------------------------------ -----------------------------------------------------
    SYS DBA

    普通表:
    SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DAVE';

    OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
    ----- ---------- ------------------------------ ---------------------------------------------------
    SYS DAVE SYSTEM

    展开全文
  • oracle 数据仓库

    千次阅读 2009-03-10 15:57:00
    一.Data warehouse和OLTP系统的对比和OLTP系统不同,数据仓库的主要目的是集中统一的存放业务的历史数据,以便用于查询和分析。数据仓库理论的鼻祖William Inmon给数据仓库的特性归纳为一下四点:面向主题(Subject ...

    一.Data warehouse和OLTP系统的对比

    和OLTP系统不同,数据仓库的主要目的是集中统一的存放业务的历史数据,以便用于查询和分析。

    数据仓库理论的鼻祖William Inmon给数据仓库的特性归纳为一下四点:

    • 面向主题(Subject Oriented)
    • 集成(Inegrated)
    • 不可修改(Nonvolatile)
    • 时间相关(Time Variant)

    数据仓库和OLTP是基于不同的需求而得出的解决方案。下面是数据仓库和OLTP系统的一些主要方面的对比:

    1.负载
    数据仓库主要设计为适合即席查询(Ad hoc query),我们无法预先知道数据仓库的负荷,所以,数据仓库需要设计为能够适应各种可能的查询
    而OLTP系统一般执行的操作是可以预先确定的,可以按照估计出的系统负荷来设计OLTP系统.

    2.数据修改
    数据仓库一般是通过ETL,周期性的将新的数据批量装载。数据进入数据仓库后,一般是不可修改的,所以数据仓库当中一般会建较多的索引用于优化查询。而OLTP系统,终端用户可以修改其对应的某些数据,所有OLTP系统一般即时体现着当前最新的数据。

    3.架构设计
    数据仓库一般采用非规范化或者部分非规范化的设计,以优化查询性能,典型的设计如星型模型和雪花模型。因为非规范化设计,可以只需要join较少的表就得到更多的数据。而OLTP系统一般采用规范化的设计,以避免出现update/insert/delete异常,并保证数据的一致性。

    4.典型操作
    数据仓库中,一条查询可能需要访问上千,甚至几百万行数据,所以查询的性能相当重要。在oracle中,dimension,materialized view,bitmap index等技术,都在数据仓库中大量使用以优化查询性能。OLTP中,一个操作一般只需要访问有限的几条数据。

    5.历史数据
    数据仓库中需要大量的历史数据,以便从历史数据中分析所需要的信息OLTP系统则一般会将历史数据删除,以保证当前事务处理的性能。

    二.数据仓库建模

    目前来说,数据仓库的数据存储载体还是关系数据库。对于数据仓库的建模,自然也要遵循关系数据库的一般设计准则。从数据仓库诞生以来,主要有两种建模方式。一种是Inmon推荐的关系模型,遵循规范化理论。另外一种是kimpall提出的多维模型,这是目前项目实践中的主流建模方式。多维模型中,最基本用的最多的就是星型模型(star schema)。

    Star schema主要的思想在于将我们关心的数据和用于描述数据的属性分隔开来。实际的数据存放于Fact table中,从不同角度来描述数据的属性放到不同的dimension table中。比如,一个sales数据仓库可以这样设计,每一笔销售记录,应该会包含销售的产品,销售的客户,销售的供货商,销售的时间,销售的数量和获得的收入等。当我们要分析整个公司的所有销售记录时,毫无疑问,我们最关心的是一共销售了多少?

    一共获得了多少收入?然后更进一步,在某个时间段内销售了多少?来自哪家供货商的产品的销售额最大?面向哪种客户的销售额最大?哪种产品的销售额最大?等等。

    从上面我们关心的这些问题我们可以看到,对于销售的数量和金额这类具体的数字型的数据,通常是我们分析的对象,而对于像时间,产品,客户,供货商,我们希望从这些不同的角度来得到数字型数据的一个统计结果。所以,我们将数字型的数据存放在fact table中,将时间,产品,客户,供货商存放在不同的dimension table中,自然,在fact table和dimension table之间存在一个主-外键的关联,各个dimension table之间则没有关系。由此我们可以得到如下的一个star schema:

    star_schema.jpg

    star schema之所以叫star schema,就是由于上面这个图形的形状来的,fact table处于中间的位置,dimension table围成一圈,每个dimension table和fact table关联。Fact table中除了区分每条记录的主键(fact table的主键很有可能是所有dimension table的外键组合起来的一个组合主键),连接每个dimension table的外键外,就只有我们关心的数字型数据,所以fact table中的每条记录,有个专门的术语称之为度量(measurement),因为我们利用数据仓库做统计分析的时候,这些数据就是统计分析的一个个基本单位,也就是度量值。

    显然,star schema是反规范化的。如果将dimension table按照规范化拆开,则star schema演化成了雪花模型(snowflake schema).规范化减少了数据的冗余,但是由于查询的时候要连接更多的表,性能就会受到影响。由于数据仓库主要用于查询的特性,除非你有非常特别的原因,一般推荐此采用star schema来进行数据仓库的架构设计。

    三.RELY constraint

    数据仓库中的数据,一般是通过ETL定期load进来的。在做ETL的时候,一般会对load的数据的一致性做检查。所以,我们有理由认为,数据仓库中的数据都是符合一致性要求的。

    既然我们已经可以确定数据仓库中的数据是一致的,那么就可以不在表上建constraint,constraint对DML或者load操作是有性能影响的,能不用当然不用的好。但是,虽然表中的数据实际上一致了,oracle自己却不知道,优化器也不知道。在利用物化视图查询重写(query rewrite)时,constraint和dimension的作用是很大的(一般在数据仓库环境中,query_rewrite_integrity参数设置为trusted),查询重写对数据仓库的性能影响相当大。

    为了告诉oracle,数据应该符合某种一致性条件了,而又不想创建的constraint其作用,就可以创建类型为RELY的constraint,也就是一种可以让oracle知道这些数据是符合这些约束的,但这个约束本身却是没有其实际作用的约束。

    文档中给出了一个例子:

    ALTER TABLE sales ADD CONSTRAINT sales_time_fk
    FOREIGN KEY (time_id) REFERENCES times (time_id)
    RELY DISABLE NOVALIDATE;

    另外,视图只能创建RELY constraint,不能创建普通的constraint。

    四.Dimension

    前面我们提到,除了constraint,另外一个影响物化视图查询重写的重要因素就是dimension。

    要理解oracle中的dimension,首先要搞清楚dimension和dimension table之间的区别。dimension table是table,和关系数据库中的其他table一样,存放数据,需要实际的存储空间。而dimension则只是一个逻辑结构,定义了dimension table中的一个列或一组列于其他列之间的一个层次关系,dimension只保存定义,可以将其理解为一种特定的constraint。所以,dimension不是一种必须存在的结构,但是,创建dimension对于数据仓库中一些复杂的查询重写有着相当重要的意义。而查询重写,则是数据仓库性能优化的一个不二法门。

    数据仓库中由于数据量巨大,一些聚合计算等操作往往通过物化视图预先计算存储。但是,不可能对所有维度的所有可能的聚合操作都建立物化视图,一则空间不允许,二则刷新时间也不允许。那么,在对某些聚合操作的sql进行查询重写时,就希望能利用已经存在的物化视图,尽管他们的聚合操作条件不完全一致。而dimension定义的各个level之间的层次关系,对于一些上卷(rolling up)和下钻(drilling down)操作的查询重写的判断是相当重要的,而dimension中定义的attributes对于使用不同的列来做分组的查询重写起作用。

    一个典型的dimension定义如下:

    CREATE DIMENSION products_dim
    LEVEL product IS (products.prod_id)
    LEVEL subcategory IS (products.prod_subcategory)
    LEVEL category IS (products.prod_category)
    HIERARCHY prod_rollup (
    product CHILD OF
    subcategory CHILD OF
    category
    )
    ATTRIBUTE product_info LEVEL product DETERMINES
    (products.prod_name, products.prod_desc,
    prod_weight_class, prod_unit_of_measure,
    prod_pack_size, prod_status, prod_list_price, prod_min_price)
    ATTRIBUTE subcategory DETERMINES
    (prod_subcategory, prod_subcategory_desc)
    ATTRIBUTE category DETERMINES
    (prod_category, prod_category_desc);

    dimension中三个重要的属性:level,hierarchy,attribute。其中level定义了一个或一组列为一个整体,而hierarchy则定义了各个level之间的层次关系,父level和子level之间是一种1:N的关系,而且,在dimension中可以指定多个hierarchy层次关系。attribute则定义了level和其他列的一个1:1的关系,但这种1:1的关系不一定是可逆的,比如上面的列子,根据product_info,也就是prod_id,可以确定prod_name,但不一定要求prod_name就能确定prod_id。

    而且,各个level之间的列不一定要来自同一个table,对于雪花模型,dimension table可能被规范化为许多的小表,则dimension中的level可能是来自不同表中的列。这是需要在dimension中指定join key来指出各个表之间的关联列。例如:

    CREATE DIMENSION customers_dim
    LEVEL customer IS (customers.cust_id)
    LEVEL city IS (customers.cust_city)
    LEVEL state IS (customers.cust_state_province)
    LEVEL country IS (countries.country_id)
    LEVEL subregion IS (countries.country_subregion)
    LEVEL region IS (countries.country_region)
    HIERARCHY geog_rollup (
    customer CHILD OF
    city CHILD OF
    state CHILD OF
    country CHILD OF
    subregion CHILD OF
    region
    JOIN KEY (customers.country_id) REFERENCES country);

    如果不指定skip when null子句,每个level中都不允许出现null值。
    通过dbms_dimension.describe_dimension可以查看dimension的定义。
    通过dbms_dimension.validate_dimension可以检查dimension是否定义正确,在执行之前需要执行ultdim.sql创建一个dimension_exceptions表,如果定义有误,则会在dimension_exceptions中查到相应的记录。在9i里,validate_dimension在dbms_olap包中。

    关于dimension, AskTom上有个问题写得比较详尽,值得仔细研究,点此阅读

    五.Bitmap join index

    Bitmap index的主要思想就是,针对每一个可能的值x,建立一个或一组位图映射,每个bit为1代表这个位置的值等于x,为0则不等于x。而每个位置都可以直接映射到某一行的rowid。由于在执行DML操作时,锁定的是整个bitmap,而不是bitmap中的某个位,所以bitmap index对于并发DML的性能很差,而且频繁的DML操作会使得bitmap index的空间效率大打折扣,所以OLTP系统并不合适使用bitmap index。对于基本没有DML操作,有大量ad hoc查询的Data warehouse环境则相当有效。关于bitmap index的理解,可以参考itpub上的一篇深入讨论

    从oracle9i起,oracle又引进了一种新的索引类型:bitmap join index。和bitmap index建立在单个table上不同,bitmap join index是基于多表连接的,连接条件要求是等于的内连接(equi-inner join)。对于数据仓库而言,较普遍的是Fact table的外键列和相关的Dimension table的主键列的连接操作。

    Bitmap join index能够消除查询中的连接操作,因为它实际上已经将连接的结果保存在索引当中了。而且,相对于在表的连接列上建普通bitmap index来说,bitmap join index需要更少的存储空间。同样的基于连接的Metarialized view也可以用来消除连接操作。但bitmap join index比起物化视图来更有效率,因为通过bitmap join index可以直接将基于索引列的查询对应到事实表的rowid。

    以oracle的sample schema SH中的sales和customers表做个例子

    1.建立基于维度表中一个列的bitmap join index

    create bitmap index sales_cust_gender_bjix
    on sales(customers.cust_gender)
    from sales,customers
    where sales.cust_id=customers;

    建立这样的bitmap join index后,下面的查询就可以从index中直接得到结果,而不再需要连接sales和custmoers两张表来获得结果了。相当于根据连接条件,将customers表中的cust_gender列保存到sales表中了。

    select sum(sales.amount_sold)
    from sales,customers
    where sales.cust_id,customers.cust_id
    and customers.cust_gender='M';

    通过将bitmap join index dump出来可以看到,实际上,索引是按照ustomers.cust_gender分成2个位图,每个位图映射到sales表的ROWID。

    所以根据customers.cust_gender来过滤连接结果时,从索引中可以直接得到目标数据在sales中的rowid,无须执行join操作了。

    一个可能的执行计划如下:

    Execution Plan
    --------------------------------------------------------
    --
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3751 Card=1 Bytes=9)

    1 0 SORT (AGGREGATE)
    2 1 PARTITION RANGE (ALL)
    3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (Cost=3751 Card=508136 Bytes=4573220)
    4 3 BITMAP CONVERSION (TO ROWIDS)
    5 4 BITMAP INDEX (SINGLE VALUE) OF 'IX_BITMAP'

    2.建立基于一个维度表中多个列的bitmap join index

    create bitmap index sales_cust_ms_bjix
    on sales(customers.cust_gender,customers.cust_id)
    from sales,customers
    where sales.cust_id=customers.cust_id;

    3.建立多个维度表到一个事实表的bitmap join index

    create bitmap index sales_c_gender_p_cat_bjix
    on sales(customers.cust_gender,products.prod_category)
    from sales,customers,products
    where sales.cust_id=customers.cust_id
    and sales.prod_id=products.prod_id;

    4.建立基于snowflake schme的bitmap join index
    雪花模型的维度表被规范化为多个小表,也就是建index的时候需要额外连接其他几个表

    create bitmap index sales_co_country_name_bjix
    on sales(countries.country_name)
    from sales,countries,customers
    where sales.country_id=countries.country_id
    and sales.cust_id=customers.cust_id;

    Bitmap join index的一些限制条件

    • 只支持CBO
    • 只能是equi-inner连接,任何外连接都无法使用bitmap join index
    • 多个连接条件只能是AND关系
    • 只能在fact table上执行并行DML。如果在dimension table上执行并行DML,会导致索引变为unusable状态。
    • 不同的事务中,只能并发更新一个表
    • 在From字句中,任何一个表都不能出现两次
    • 在索引组织表(IOT)和临时表上不能建立bitmap join index
    • 索引只能基于dimenion table中的列
    • 维度表用于连接的列只能是主键列或者是有唯一约束的列。
    SQL> create bitmap index sales_cust_gender_bjix
    2 on sales(customers.cust_gender)
    3 from sales,customers
    4 where sales.cust_id=customers.cust_id;
    from sales,customers
    *
    ERROR at line 3:
    ORA-25954: missing primary key or unique constraint on dimension

    如果维度表的主键是组合主键,那么连接条件需要是全部主键列都参与其他对于bitmap index的限制条件同样使用于bitmap join index,比如在分区表上只能是local,不能是global。

    六.Unique constraint & unique index

    一般情况下,unique constraint都是通过unique index来实现的。但是在数据仓库中,由于数据量巨大,建立一个索引可能需要花费相当大的时间和空间,假如查询中又用不上这个索引的话,那么建立索引的高代价却没有带来什么收益,这是很不划算的。

    举个例子,假如有一个sales表,其中sales_id的数据是唯一的,我们在sales_id上建一个unique constraint,语法如下:

    alter table sales add constraint sales_uk unique(sales_id);

    这样建立的unique constraint是enable validate状态的,oracle会自动在sales_id列上创建一个的名为sales_uk的unique index。通过查询user_indexes或者user_ind_columns视图可以看到这个index:

    SQL> select index_name,column_name from user_ind_columns where index_name='SALES_UK';
    INDEX_NAME COLUMN_NAME
    -------------------
    -- ---------------------
    SALES_UK SALES_ID

    在数据仓库环境中,这个unique index可能是不合适的:
    1.这个索引可能会相当的大。
    2.在查询中几乎不会用到sales_id来做为过滤条件
    3.多数情况下,sales会是一个分区表,而且分区键不会是sales_id。这样这个unique index必须是global index,在对分区的一些DDL操作中可能会导致global index失效。那么怎么能在创建unique constraint的同时不生成unique index呢?很简单,创建一个状态为disable validate的unique constraint就能满足上述要求。

    alter table sales add constraint sales_uk unique(sales_id) disable validate;

    再来查询user_ind_columns可以发现没有记录:

    SQL> select index_name,column_name from user_ind_columns where index_name='SALES_ID';
    no rows selected

    但是disable validate状态的索引会导致无法对该列进行DML操作

    SQL> delete from sales where rownum=1;
    delete from sales where rownum=1
    *
    ERROR at line 1:
    ORA-25128: No insert/update/delete on table with constraint (NING.SALES_UK) disabled and validated

    那么,要修改有disable validate约束的表中的数据,只有以下两种方法:
    1.使用DDL操作,比如分区表的exchange partition
    2.首先drop constraint,修改数据,再重新创建disable validate的constraint

    七.Partition table

    分区表(partition table)在数据仓库中的重要性不言而寓,数据仓库的事实表中的数据量一般都比较大,而且很多时候是和时间相关的历史数据,使用范围分区是最合适的,但有时也要结合实际考虑其他的分区方式。

    分区有三种基本的方式:range,hash和list。某个分区还可以继续进行子分区,所以,上面三种基本的分区还可以组成两种组合分区:range-hash和range-list。

    Range partition
    范围分区就是安装分区键的不同范围的数据进入到不同的分区当中,对于按照时间延续性的历史数据,这种分区非常合适。所以这种分区也是

    最常见的分区形式。
    例如,对于一个销售记录的表,可以按照销售时间来分区,每个月的数据都单独做为一个分区:

    CREATE TABLE sales_range
    (salesman_id NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_amount NUMBER(10),
    sales_date DATE)
    PARTITION BY RANGE(sales_date)
    (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
    PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
    PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
    PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));

    注意,分区表达式中是一个小于的关系,也就是不包括边界的,等于边界值的数据会进入到下一个分区。如果我们还有些数据在所有的分区定义之外,那么可以定义一个条件为MAXVALUE的分区,例如partition sales_other values less than(Maxvalue),则不符合前面所有分区条件的数据都会进入这个“默认分区”。

    如果你遇到以下情况,考虑使用范围分区是非常合适的:
    1.对于一个大表,经常使用范围条件来查询的,可以考虑将该条件作为分区键进行反问分区。
    2.你希望对表中的数据滚动更新。比如保持36个月的销售记录,每个月删除36个月前的分区,再建一个新的分区,将新的一个月的记录加进来。
    3.当一个表中的数据量非常大的时候,一些管理任务,比如备份恢复什么的,都会花费相当长的时间。而将这些表改造成分区表对于简化这些管理任务相当有效,可以针对单个的分区来进行管理。

    Hash partition
    Hash分区是,根据oracle内部的一种hash算法,将不同的数据放到不同的分区当中,因此能够将所有的数据比较平均的分到所有的分区中,是各个分区中的数据量比较平衡。
    Oracle采用的是一种线性hash算法,分区的数目建议是2的指数个,比如2,4,8,16……

    下面是4个分区的例子:

    CREATE TABLE sales_hash
    (salesman_id NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_amount NUMBER(10),
    week_no NUMBER(2))
    PARTITION BY HASH(salesman_id)
    PARTITIONS 4;

    由于hash分区不是按照数据本身的一些逻辑来分区的,所以对于历史数据不合适,主要用来将数据平衡到各个分区当中。而且,hash分区的分区排除只能基于等于条件。

    使用hash分区,你可以:
    1.对于一些大数据量的表,提供更好的可用性和可管理性。这个算是所有分区表的共性。
    2.防止数据在不同分区表中间分布不均。这个是hash分区的特性。
    3.对于经常使用等于条件或者in条件的查询,hash分区可以很好的使用分区排除和分区级连接。这个可以算hash分区的一个限制条件。

    List partition
    List分区可能是用的比较少的一种分区方式了。采用list分区,你可以完全的控制数据到分区的映射,不想范围分区只能指定某个范围的数据到某个分区。对于一些没有明显范围性的离散数据,采用list分区就比较合适了。

    比如按照销售的地点进行list分区:

    CREATE TABLE sales_list
    (salesman_id NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_state VARCHAR2(20),
    sales_amount NUMBER(10),
    sales_date DATE)
    PARTITION BY LIST(sales_state)
    (PARTITION sales_west VALUES('California', 'Hawaii') COMPRESS,
    PARTITION sales_east VALUES('New York', 'Virginia', 'Florida'),
    PARTITION sales_central VALUES('Texas', 'Illinois'));

    对于list分区,如果有其他不符合上面所有分区定义条件的数据,可以建一个default分区来存放,就好像Range分区的Maxvalue一样:PARTITION sales_other VALUES(DEFAULT))。List分区还有一个限制,就是分区键只能是一个列,而不像Range和hash分区的分区键可以是多个列的组合,当然,也有限制,最多不能超过16个列的组合。

    Composite partition
    Oracle只有两种组合分区方式,Range-hash和Range-list。组合分区的有点就是集合了两种不同分区方式的优点。而且,对于每个子分区,oracle都单独建了一个segment。

    Range-hash分区的例子:

    CREATE TABLE sales_range_hash(
    s_productid NUMBER,
    s_saledate DATE,
    s_custid NUMBER,
    s_totalprice NUMBER)
    PARTITION BY RANGE (s_saledate)
    SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8
    (PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
    PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
    PARTITION sal99q3 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
    PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));

    Range-list分区的例子:

    CREATE TABLE quarterly_regional_sales
    (deptno NUMBER, item_no VARCHAR2(20),
    txn_date DATE, txn_amount NUMBER, state VARCHAR2(2))
    PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
    SUBPARTITION TEMPLATE(
    SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE ts1,
    SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE ts2,
    SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE ts3,
    SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE ts4,
    SUBPARTITION northcentral VALUES ('SD', 'WI') TABLESPACE ts5,
    SUBPARTITION southcentral VALUES ('NM', 'TX') TABLESPACE ts6)
    (
    PARTITION q1_1999 VALUES LESS THAN(TO_DATE('1-APR-1999','DD-MON-YYYY')),
    PARTITION q2_1999 VALUES LESS THAN(TO_DATE('1-JUL-1999','DD-MON-YYYY')),
    PARTITION q3_1999 VALUES LESS THAN(TO_DATE('1-OCT-1999','DD-MON-YYYY')),
    PARTITION q4_1999 VALUES LESS THAN(TO_DATE('1-JAN-2000','DD-MON-YYYY')));

    注意中间的template关键字,使用template,就不必要为每个分区的子分区都单独做一次定义了,所有分区的子分区都会使用template定义的子分区条件和子分区名字。

    八.Materialized view

    通常,在数据仓库中可以通过创建摘要信息(summary)来提升性能。这里的摘要指的是预先对一些连接(join)和聚合(aggregation)进行计算并将结果保存下来,后续查询的时候可以直接利用保存的摘要信息来生成报表。在oracle中,可以利用物化视图(materialized view)来创建数据仓库中的摘要。物化视图另外一种重要的功能是复制数据。结合oracle优化器的查询重写(query rewrite)功能,可以在不改写应用的情况下,利用物化视图提升系统性能。

    1.创建物化视图所需要的权限
    在自己的schema下:
    create materialized view
    create tabel
    在其他schema下:
    create any materialized view
    物化视图的拥有者必须有create table系统权限,以及对物化视图所引用的对象的查询权限
    对于提交刷新(refresh-on-commit)的物化视图,还需要基表的on commit refresh对象权限,或者有On commit refresh系统权限。
    如果要支持查询重写,还需要query rewrite或者global query rewrite权限。
    对于预定义表物化视图(materialized view on prebuilt container),需要对预定义表有select with grant option权限。
    物化视图创建后,oracle会自动在同一个schema下创建一个内部表和最少一个索引,有可能还会创建一个视图。所以,还需要有创建这些对象的权限。

    2.物化视图的刷新方式
    当基表数据变化后,物化视图需要刷新,以便反映基表的最新数据。一共采用三种刷新方式:

    Complete
    完全刷新会先删除物化视图中的所有现有数据(如果基于单表的物化视图,可能会采用truncate),然后根据定义重新生成物化视图。

    Fast
    快速刷新是一种增量刷新,只会将上次刷新以后对基表的操作刷新到物化视图中。要实现快速刷新,需要满足快速刷新的条件。

    Force
    强制刷新其实叫做智能刷新更合适,oracle会自动判断是否满足快速刷新的条件,如果满足,采用快速刷新方式,否则完全刷新。

    对于fast refresh,还可以指定是提交刷新(on commit),按需刷新(on demand),或者按计划刷新。

    On commit方式,只要针对基表的事务提交,就刷新对应的物化视图,如果基表含有对象类型,则不适用该方式。采用该选择,可能会延长事务提交的时间,因为提交需要等待刷新完成。

    on demand方式则需要调用dbms_mview.refresh或者dbms_mview.refresh_all来执行刷新。默认是on demand。另外,可以使用start with和next字句指定物化视图按计划刷新。

    物化视图刷新,需要能够唯一识别出基表数据的每一列。可以使用的包括primary key,rowid和objet_id。

    With primary key方式是默认和推荐使用的方式,要求基表有主键,且基表对应物化视图日志是记录primary key的。
    with rowid方式,在基表没有可用主键的情况下,可以使用rowid方式。但采用rowid的物化视图只能基于单一基表,并且不能包含以下查询:

    • distinct或者聚合
    • group by或者connect by
    • 子查询
    • 集合操作(比如union/union all/minus)

    采用rowid方式,如果要使用fast refresh,必须先执行一次complete refresh。
    with object id。如果是对象物化视图(object materialized view),则只能采用该方式。
    通过使用never refresh选项,可以阻止对物化视图进行任何方式的刷新。

    展开全文
  • 执行DML期间,为防止对与DML相关的对象进行修改,执行DML的进程必须对该表获得TM锁。若在获得TM锁的过程中发生争用,则等待enq: HW - contention 事件。 SQL> select name,parameter1,parameter2,parameter3 from v...

    执行DML期间,为防止对与DML相关的对象进行修改,执行DML的进程必须对该表获得TM锁。若在获得TM锁的过程中发生争用,则等待enq: HW - contention 事件。

    SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name like 'enq: TM - contention';
    
    NAME                           PARAMETER1           PARAMETER2           PARAMETER3
    ------------------------------ -------------------- -------------------- --------------------
    enq: TM - contention           name|mode            object #             table/partition
    

    注意:可能大家以为存在DML锁和DDL锁这两个锁,实际上,DML锁和DDL锁只是为了合理分配锁而赋予的名称。
    DML锁:data lock。执行DML时保护数据的锁。Row Lock(TX)保护特定行,Table Lock(TM)保护整个表,可以通过dba_dml_locks观察。这个视图的作用是从v$lock视图上筛选出锁类型为TM的。数据库上允许的TM锁数量,可以利用dml_locks参数指定。

    DDL锁:data dictionary lock。保护User/Table/View/Procedure等定义,可以通过dba_ddl_locks观察。

     

    一般发生TM锁争用的情况如下:

    1、不当的DDL引起的TM锁争用

     对于事务正运行的表,基本上不可能执行DDL。因此这时不会发生争用引起的性能问题。对于已经完成update但还没有提交的表,不可能执行DDL。相反,对于正在执行DDL的表执行DML时,可能发生TM锁争用。
    若对于数据多的表执行不当的DDL,则访问此表的所有DML会话都会陷入等待状态,可能发展至故障状态。通过合理的管理,从根本上防止才是最好的方法。
    执行DDL时,最好使用online选项。随着oracle版本升级,online状态下可执行的DDL逐步增加。
    使用parallel DDL将DDL的执行速度最大化。对拥有大量数据的表执行DDL时,若恰当使用parallel选项,可将DDL本身性能最大化,而且同时使用nologging选项也比较好。

     

    2、利用Lock table ...主动获取TM锁时

    利用Lock table ...语句有意获取TM锁时可能发生TM锁争用。发生TM锁引起的争用,收集锁拥有着在会话上执行的SQL语句尤为重要。

     

    3、执行Direct/Parallel Load工作时

    INSERT /*+ APPEND */ INTO ...或SQL*Loader的direct path load之类的部分功能,对于相应的表以Exclusive模式获得TM锁。Direct load工作不经过SGA,而是直接写入到数据文件里,所以在执行工作期间不允许对表进行任何修改。Direct load工作在执行工作期间,不允许对于表任何DDL或DML。因此,事务多的时刻执行Direct load工作时,需要确认TM锁争用是否可能引发问题。将SQL*Loader利用Parallel模式执行时,对表以Shared模式获取TM锁。因此,此种情况下也不会允许其它会话上的DDL或DML。

    展开全文
  • Oracle 数据压缩(Compression) 技术 说明

    万次阅读 2017-12-01 15:24:48
     官网说明1.1 Oracle 11g Advanced Compression Oracle 11g EE版本中只有: Basic Table Compression ,而 AdvanceCompression Feature需要单独购买。 11g Advanced Compression 有如下特性:1. Compression for...
  • Oracle 19c 新特性概要

    万次阅读 2020-10-14 15:44:03
    本文概括出一些工作中可能会用到的Oracle 19c新特性,所有新增功能的说明请参考新特性官方文档《database-new-features-guide》 根据官方文档分为以下几个部分 应用开发 可用性 大数据和数据仓库 整体数据库 ...
  • Oracle/Hive/Impala SQL比较(3-4)

    千次阅读 2016-05-15 09:24:45
    3 DML 3.1 Load Files 操作 Oracle Hive Impala LOAD LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, ...
  • Oracle 数据库12c 16大新特性总结

    千次阅读 2016-05-31 00:34:11
    Oracle 12c 已发布很久,一直想找个时间好好学习一下,毕竟后续12c将会逐渐替代现有数据库版本,成为主流数据库版本。现就12c 一些常用的 特性给大家一起学习一下。 1. 在线重命名和重新定位活跃数据文件 不同于...
  • 从零开始学习Oracle

    万次阅读 多人点赞 2014-11-19 10:07:35
    (一)Oracle之 (二)Oracle之 (一)Oracle之 (一)Oracle之 (一)Oracle之 (一)Oracle之 (一)Oracle
  • oracle非分区表,转分区表

    万次阅读 2011-06-03 12:58:00
    本文原出处:http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4717318.aspx将普通表转换成分区表有4种方法: 1. Export/import method 2. Insert with a subquery method 3. Partition exchang
  • 收缩表段(shrink space)

    万次阅读 2011-07-25 09:26:27
    当表被创建后,随着记录的不断插入,组成表的区间会被填满,如果启用了自动扩展,则当区间填满后,会分配新的区间。假定高水 位线随着记录的增加从最左端往右端来移动,当到底部区间的尾端时,则新的区间将会被分配...
  • 利用Truncate清空Oracle各个分区的资料

    千次阅读 2008-12-26 10:18:00
    TRUNCATE清空资料以及DELETE与TRUNCATE数据库的差别:Note: Deletesperform normal DML. That is, they take locks on rows, they generateredo (lots of it), and they require segments in the UNDO tablespace.
  • Oracle Analyze 命令 详解

    万次阅读 2017-12-01 17:51:12
    官网的链接如下:http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4005.htm#SQLRF01105 使用DBMS_STATS 收集统计信息参考:Oracle Statistic 统计信息 小结...
  • Linux6安装Oracle12c

    万次阅读 2014-06-11 09:16:26
    前几天看到CSDN的一篇文章,Oracle12c可以下载试用了,赶紧跑到oracle官网下载去尝尝鲜?对一个ArcGISer来说关心的无怪乎两个问题1:ArcSDE10.2(最新版本)是否支持Oracle12c2:Oracle12c是否有一些新功能可以解决...
  • 一.Supported Oracle data types1.1 Numeric data types(1) NUMBER up to the maximum sizepermitted by Oracle(2) BINARY FLOAT(3) BINARY DOUBLE Limitations of support The support of rangeand ...
  • 分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。  Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(SEGMENT),可以存放到不同的表空间中。查询时可以通过...
  • 12C ORA-错误汇总10 ORA-12500 to ORA-19400

    万次阅读 2016-03-02 21:41:11
    ORA-12500: TNS:listener failed to start a dedicated server process Cause: The process of starting up a dedicated server process failed. The executable could not be found or the environment may be se
  • Oracle Table 创建参数 说明

    万次阅读 2017-12-06 09:45:37
    先看一个oracle 10g 下table 创建SQL,都是默认值:CREATE TABLE SYS.QS( USERNAME VARCHAR2(30 BYTE) NOT NULL, USER_ID NUMBER NOT NULL, CREATED DATE
  • 当我在数据库中创建order表时,系统提示 ora-00903:表名无效 create table ORDER (  oid NUMBER,  bid NUMBER,  createdate VARCHAR2(50),  count NUMBER,  price VARCHAR2(50), ... totalprice VAR
1 2 3 4 5 ... 20
收藏数 4,046
精华内容 1,618
关键字:

dml次数的view oracle 记录partition