分区表和索引1-80页

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://llovewxm1314.blog.csdn.net/article/details/7485149

全区分区索引,本地分区索引:

http://www.itpub.net/thread-429282-1-1.html

全局分区索引在一个索引分区中包含来自多个表分区的键,一个全局分区索引的分区键是分区表中中不同的或者指定一个范围的值。在创建全局分区索引的时候,必须定义分区键的范围和值。

组合分区:

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

如下,创建组合分区:

create table test1_1(

  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              ——绿色部分为关键词:subpartition 3 store in()

)                                                                                                                                       ——俩个括号之间连着,没有分号。

(

 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 varchar2(32),
  grade number
)
partition by range(deptno) subpartition by hash(empname)   
subpartition template(                                      
             ——关键词:subpartition template(subpartitionatablespace ts1
 subpartition a tablespace ts1,
 subpartition b tablespace ts2,
 subpartition c tablespace ts3,
 subpartition d tablespace ts4
)                                                                               ——两个括号连着,没有分号,或者逗号。
(
 partition p2 values less than(1000),
 partition p2 values less than(2000),
 partition p3 values less than(maxvalue)
);

还有一种写法,每个父partition里含有所有子subpartition的所有描述。不写了具体看p45页。

oracle 11g中分区表的功能有所增强,有增加了range-range ,list-range , list-list , list-hash 这四个新的。

普通表转化成分区表:

1、export/import 命令;

2、子查询插入分区表;

3、partition exchange method。

4、DBMS_REDEFINITION

涉及到的操作:

重命名表:rename emp to emp_old ; 

交换分区:partition exchange method

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

      步骤如下:创建表分区P1,P2——创建表A(内部的数据是符合P1标准的数据)——创建表B(内部的数据是符合P2标准的数据)——用表A和P1进行交换,把表A的数据放到P1分区——用表B和P2进行交换,把表B的数据放到P2分区。

创建一个分区表:

   create table p_dba_test(

   object_id number, 

   created date)

   partition by range(created)

(

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

   partition p2 values less than (to_date('2012-04-23','yyyy-mm-dd'))

   );


创建两个对应的分区基表:

SQL> create table dba_p1 as select object_id,created from dba_test

          where created<to_date('2010-09-1','yyyy-mm-dd');

           SQL> create table dba_p2 as select object_id,created from dba_test 

                    where created <to_date('2012-04-23','yyyy-mm-dd')

                     and   created>to_date('2010-09-1','yyyy-mm-dd');


把两个基表与两个分区表进行交换:

                  SQL> alter table p_dba_test exchange partition p1 with table dba_p1;
                      表已更改。
                 SQL> alter table p_dba_test exchange partition p2 with table dba_p2;
                     表已更改。
                  SQL> select count(*) from p_dba_test partition(p1);
 

                     COUNT(*)
                      ----------
                      47521

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

  COUNT(*)

----------

       834

SQL> select count(*) from dba_p1;

  COUNT(*)

----------

         0

                  SQL> select count(*) from dba_p2;
                         COUNT(*)

   ----------

         0

如果插入的数据不满足分区规划,会报ora-14400错误

使用在线重定义:DBMS_REDIFINITION

http://blog.csdn.net/tianlesoftware/article/details/6218693

In this Document
  Goal
  Solution


Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Standard Edition - Version: 9.2.0.4 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.
"Checked for relevance on 29-Sep-2010"

Goal

The purpose of this document is to provide step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package.

Solution

1) Create unpartitioned table with the name unpar_table

SQL> CREATE TABLE unpar_table ( 
id NUMBER(10), 
create_date DATE, 
name VARCHAR2(100) 
);


2) Apply some constraints to the table:

SQL> ALTER TABLE unpar_table ADD ( 
CONSTRAINT unpar_table_pk PRIMARY KEY (id) 
); 

SQL> CREATE INDEX create_date_ind ON unpar_table(create_date);


3) Gather statistics on the table:

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE);

4) Create a Partitioned Interim Table:

SQL> CREATE TABLE par_table ( 
id NUMBER(10), 
create_date DATE, 
name VARCHAR2(100) 

PARTITION BY RANGE (create_date) 
(PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')), 
PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')), 
PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE));


5) Start the Redefinition Process:

a) Check the redefinition is possible using the following command:

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

b)If no errors are reported, start the redefintion using the following command:

SQL> BEGIN 
DBMS_REDEFINITION.start_redef_table( 
uname => USER,  
orig_table => 'unpar_table', 
int_table => 'par_table'); 
END; 
/


Note: This operation can take quite some time to complete.

c) Optionally synchronize new table with interim name before index creation:

SQL> BEGIN 
dbms_redefinition.sync_interim_table( 
uname => USER,  
orig_table => 'unpar_table', 
int_table => 'par_table'); 
END; 
/

d) Create Constraints and Indexes:

SQL> ALTER TABLE par_table ADD ( 
CONSTRAINT unpar_table_pk2 PRIMARY KEY (id) 
); 


SQL> CREATE INDEX create_date_ind2 ON par_table(create_date);

e) Gather statistics on the new table:

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

f) Complete the Redefintion Process:

SQL> BEGIN 
dbms_redefinition.finish_redef_table( 
uname => USER, 
orig_table => 'unpar_table', 
int_table => 'par_table'); 
END; 
/

At this point the interim table has become the "real" table and their names have been switched in the name dictionary. 

g) Remove original table which now has the name of the interim table:

SQL> DROP TABLE par_table; 

h)Rename all the constraints and indexes to match the original names.

ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk; 
ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

i) Check whether partitioning is successful or not:

SQL> SELECT partitioned 
FROM user_tables 
WHERE table_name = 'unpar_table'; 

PAR 
--- 
YES 

1 row selected. 

SQL> SELECT partition_name 
FROM user_tab_partitions 
WHERE table_name = 'unpar_table'; 

PARTITION_NAME 
------------------------------ 
unpar_table_2005 
unpar_table_2006 
unpar_table_2007 

3 rows selected.

Please not that the 9i redefinition procedures has some restrictions: 

* There must be enough space to hold two copies of the table. 
* Primary key columns cannot be modified. 
* Tables must have primary keys. 
* Redefinition must be done within the same schema. 
* New columns added cannot be made NOT NULL until after the redefinition operation. 
* Tables cannot contain LONGs, BFILEs or User Defined Types. 
* Clustered tables cannot be redefined. 
* Tables in the SYS or SYSTEM schema cannot be redefined. 
* Tables with materialized view logs or materialized views defined on them cannot be redefined. 
* Horizontal sub setting of data cannot be performed during the redefinition.

使用导入导出建立分区白表:

步骤:将普通表dump出来——创建分区表——将dump文件导入数据。

注意两点:分区表导出的dump文件要比普通表导出的大;分区表导入的时间要比普通表时间长。

导出表的时候,对应的索引,注释,和权限都会被一起导出,但要保障有足够的空间,否则会出现一个写失败的错误。我们可以用所有表所占的空间估计一下,这个估计值不准,因为这个是查询的数据库内所有的表的大小(查询结果不包括 lob , VARRAY,分区表):

select  sum(bytes)/1024/1024 from user_segment where segment_type='TABLE' ;  ——注意table是大写的,小写的没有查询结果。

在执行导出表之前,先要运行catexp.sql或者catalog.sql。这两个脚本有如下功能:

1、在数据字典里创建必要的导出视图。

2、创建角色: EXP_FULL_DATABASE。

3、分派所有必需的权限给EXP_FULL_DATABASE角色。

4、把EXP_FULL_DATABASE角色授权给dba角色。

5、记录已安装catexp.SQL脚本的版本号。


概念:

集群因子(clustering factor): 集群因子位于USER_INDEXES视图,该列反映了数据相对于已经建立索引的列是否显得有序,如果集群因子的值接近索引中的树叶块数目,表中的数据就越有序,如果集群因子的值接近于表中的行数,则表中的数据就不是很有序。


快速全局扫描(Fast Full Scan):这个选项允许oracle执行一个全局索引扫描操作,快速全局扫描读取B*树索引上的树叶块。初始化文件中的:

DB_FILE_MULTIBLOCK_READ_COUNT 参数可以控制同时被读取的块的数目。


跳跃式扫描(index skip scan):是Oracle9i用来提高性能的新特性,对于使用复合索引的数据库应用程序意义尤为重大。 复合索引(又称为连接索引)是一个包含多个字段的索引。在ORACLE9i以前的ORACLE版本里,只有那些在WHERE子句里引用整个索引或者是引用索引的一个或多个前导字段的查询,才能使复合索引有效而提高检索效率。而在ORACLE9i里,一个复合索引即使在一次查询中没有使用前导字段,也可能通过“索引跳跃式扫描”被有效引用。在一次跳跃式扫描中,每个复合索引前导字段的独特值(DISTINCT VALUE)只会被搜索一次,ORACLE9i会在复合索引前导字段每个独特值区间结合WHERE子句中的其它复合索引字段搜索符合条件的目标记录,这种做法的结果导致了在索引中的跳跃式扫描。跳跃式扫描参考:http://blog.csdn.net/changyanmanman/article/details/7708653

展开阅读全文

关于创建分区表和分区索引

09-03

最近看了几本书,对sql分区方面有几个问题不大明白,网上搜了一下也没有真正搞明白,所以发帖请教大家rnrnrn关于概念的问题:rn 1.书上总是会说“创建一个已分区的表,索引或索引视图”,要分区的表还没有创建,怎么就创建一个“已分区的...”。rn 2.“已分区的索引和包含列”。rnrnrn非概念问题:rn 1.我现在手上有一本《sql server 2005实现与维护》施平安译,清华出的。其中“分区一个现有的表或索引”小节中的一段话是“我们可以再不用删除并重建一个表或索引的情况下,对一个现有的表或索引进行分区。相反,[color=#FF6600]如果删除一个聚集索引,并在另一个文件组上重新建立它,sql server 将该表的全部内容移到与该聚集索引相同的文件组中。[/color]”而下面给出的步骤是:rn a.创建一个分区函数。rn b.创建一个分区方案。rn c.删除已经存在的聚集索引。rn d.在分区方案上重新建立聚集索引。rn 我不明白的是上面红色那句话,我们就是为了把一个表中的数据分到各个分区中,而删除聚集索引再在另一个文件组上重建他又会导致全部内容移到与该聚集索引相同的文件组中,那岂不是在经过第3,4步以后,原表所有的内容都被移动到其中一个分区(重建聚集索引那个分区)中了?rnrn 2.我在创建一个分区(无论是新建还是将原有的表分区)的时候,是不是在做完分区操作后都要重建索引(包括无论是为了重建聚集索引还是防止重建聚集索引时重复生成普通索引而将原来的普通索引删掉)? 论坛

sqlserver2005分区表索引利用不好

07-13

我用sqlserver2005的分区表功能,建了一个大数据量的分区表,rn过程如下:rn建文件组rnALTER DATABASE [aqs2211] ADD FILEGROUP [fg2008]rnALTER DATABASE [aqs2211] ADD FILEGROUP [fg2009]rnALTER DATABASE [aqs2211] ADD FILEGROUP [fg2010]rnALTER DATABASE [aqs2211] ADD FILEGROUP [fg2011]rnALTER DATABASE [aqs2211] ADD FILEGROUP [fg2012]rnrnALTER DATABASE [aqs2211] ADD FILErn(NAME = N'aqs2211_Data2008',rnFILENAME = N'G:\xp\data\aqs2211_Data2008.ndf',rnSIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2008]rnrnALTER DATABASE [aqs2211] ADD FILErn(NAME = N'aqs2211_Data2009',rnFILENAME = N'G:\xp\data\aqs2211_Data2009.ndf',rnSIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2009]rnrnrnALTER DATABASE [aqs2211] ADD FILErn(NAME = N'aqs2211_Data2010',rnFILENAME = N'G:\xp\data\aqs2211_Data2010.ndf',rnSIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2010]rnrnrnALTER DATABASE [aqs2211] ADD FILErn(NAME = N'aqs2211_Data2011',rnFILENAME = N'G:\xp\data\aqs2211_Data2011.ndf',rnSIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2011]rnrnrnALTER DATABASE [aqs2211] ADD FILErn(NAME = N'aqs2211_Data2012',rnFILENAME = N'G:\xp\data\aqs2211_Data2012.ndf',rnSIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2012]rnrn建分区函数:rnCREATE PARTITION FUNCTION auditinfotb_pf ( datetime )rnAS RANGE right rnFOR VALUES ('2008-01-01','2009-01-01','2010-01-01','2011-01-01')rnrn建分区架构:rnCREATE PARTITION FUNCTION auditinfotb_pf ( datetime )rnAS RANGE right rnFOR VALUES ('2008-01-01','2009-01-01','2010-01-01','2011-01-01')rnrn建分区表rnCREATE TABLE auditinfotb (rn id char(20) NOT NULL ,rn localCode varchar(6) ,rn appCode char(5) NOT NULL ,rn userIP varchar(15) DEFAULT NULL ,rn userRoleCode varchar(80) DEFAULT NULL ,rn privilegeCode varchar(512) DEFAULT NULL ,rn succeed char(1) DEFAULT NULL ,rn errMsg varchar(256) DEFAULT NULL ,rn isLocalUser char(1) DEFAULT NULL ,rn accessTime datetime NOT NULL ,rn userName varchar(128) NOT NULL ,rn userIDN varchar(32) DEFAULT NULL ,rn orgCode char(12) DEFAULT NULL ,rn policeType char(2) DEFAULT NULL ,rn dutyLevel char(4) DEFAULT NULL ,rn station char(4) DEFAULT NULL ,rn charge char(4) DEFAULT NULL ,rn ministry char(3) DEFAULT NULL ,rn certRole char(6) DEFAULT NULL ,rn orgName varchar(256) DEFAULT NULL ,rn appName varchar(256) DEFAULT NULL ,rn appType char(4) DEFAULT NULL,rn provCode char(2) DEFAULT NULL ,rn cityCode char(2) DEFAULT NULL ,rn countyCode char(2) DEFAULT NULL ,rn unit1Code char(2) DEFAULT NULL ,rn unit2Code char(2) DEFAULT NULL,rn unit3Code char(2) DEFAULT NULL ,rn) ON auditinfotbPS(accesstime)rn;rnrn分区表建好后我用导入导出工具,把数据导入进来rn然后创建索引rncreate index INDEX_auditinfo_orgcode on auditinfotb (accesstime,orgcode);rnrn索引建好后,我查询某日的记录的时候rn比如:rnselect *from auditinfotb rnwhere accesstime>'2009-06-01'rnand accesstime<'2009-06-02'rn就会全表扫描,rn而另一个时间段比如rnselect *from auditinfotb rnwhere accesstime>'2009-09-06'rnand accesstime<'2009-09-07' 就会利用上索引,rn这是怎么回事呢? 论坛

没有更多推荐了,返回首页