oracle如何做表分析,oracle为什么要做表分析

我要对oracle 数据库 中的表 进行表分析

analyze table 表名 compute statistics (sample 20%);

创新互联是一家专注于网站制作、做网站与策划设计,三门网站建设哪家好?创新互联做网站,专注于网站建设10余年,网设计领域的专业建站公司;建站业务涵盖:三门等地区。三门做网站价格咨询:18982081108

后面括号的是抽样统计，是为了加快分析速度，也可以不用，做全表分析。

oracle partition table 表分析 怎么解决

一、什么是分区表

表分区有以下优点：

1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。

2、数据修剪：保存历史数据非常的理想。

3、备份：将大表的数据分成多个文件，方便备份和恢复。

4、并行性：可以同时向表中进行DML操作，并行性性能提高，均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

5、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

6、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

7、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

8、需要注意的是包含LONG、LONGRAW数据类型的表不能分区,如果表格大于2G需要考虑分区。

二、分区表的种类

1、RANGE 范围分区

说明：针对记录字段的值在某个范围。

规则：

（1）、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。

分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

（2）、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

（3）、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，

也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。若不添加maxvalue的分区插入数值一旦超过设置的最大上限会报错。

例一，按date范围创建分区表

CREATE TABLE PART_TAB_CUSTOMER_BY_RANGE

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,

FIRST_NAME VARCHAR2(30) NOT NULL,

LAST_NAME VARCHAR2(30) NOT NULL,

PHONE VARCHAR2(15) NOT NULL,

EMAIL VARCHAR2(80),

SEX VARCHAR2(10),

STATUS VARCHAR2(10),

INSERT_DATE DATE

)

PARTITION BY RANGE (INSERT_DATE) --按时间分区

(

PARTITION DATE_RANGE1 VALUES LESS THAN (TO_DATE(' 2001-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data1,

PARTITION DATE_RANGE2 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data2,

PARTITION DATE_RANGE3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3

)

例二、按照number范围分区

PARTITION BY RANGE (CUSTOMER_ID) --按id分区

(

PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE part_Data1,

PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE part_Data2,

PARTITION CUS_PART2 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3

)

2、LIST 列表分区

说明：该分区的特点是某列的值只有有限个值，基于这样的特点我们可以采用列表分区。

规则：默认分区为DEFAULT，若不添加DEFAULT的分区插入数值不属于所设置的分区会报错。

例一、姓氏

CREATE TABLE PART_TAB_CUSTOMER_BY_LIST

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,

PHONE VARCHAR2(15) NOT NULL,

EMAIL VARCHAR2(80),

SEX VARCHAR2(10),

CORP_ID VARCHAR2(3),

INSERT_DATE DATE，

SUM_DATE varchar2(4)

)

PARTITION BY LIST (SEX)

(

PARTITION MALE VALUES ('男') TABLESPACE part_Data1,

PARTITION FEMALE VALUES ('女') TABLESPACE part_Data2

)

例二、varchar2的日期

PARTITION BY LIST (SUM_DATE)

(

PARTITION SUM_DATE1 VALUES ('2012') TABLESPACE part_Data1,

PARTITION SUM_DATE2 VALUES ('2013') TABLESPACE part_Data2，

PARTITION SUM_DATE2 VALUES (DEFAULT) TABLESPACE part_Data2

)

3、HASH 散列分区

说明：这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。

规则：当列的值没有合适的条件，没有范围的规律，也没有固定的值，建议使用散列分区。

散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，

使得这些分区大小一致。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

创建hash分区有两种方法：一种方法是指定分区的名字，另一种方法是指定分区数量。

例一、常规方法指定分区名字

CREATE TABLE PART_TAB_CUSTOMER_BY_HASH

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,

FIRST_NAME VARCHAR2(30) NOT NULL,

LAST_NAME VARCHAR2(30) NOT NULL,

PHONE VARCHAR2(15) NOT NULL,

EMAIL VARCHAR2(80),

SEX VARCHAR2(10),

STATUS VARCHAR2(10),

INSERT_DATE DATE

)

PARTITION BY HASH (CUSTOMER_ID) --按id散列

(

PARTITION hash1 TABLESPACE part_Data1,

PARTITION hash2 TABLESPACE part_Data2

)

例二、指定分区数量

PARTITION BY HASH (empno) PARTITIONS 2 STORE IN (part_Data1,part_Data2);

--往往我们不需要知道bash分区的名字，因为数据放在哪个分区是Oracle根据bash算法存放的，并不是用户指定，

所以当用户插入一条记录，并不能确定放在哪个分区，这个不同于range和list

3、复合分区

说明：顾名思义，复合分区就由range+list+hash两两组合而来，一般分为range+list,list+range,range+hash,list+bash，这里指列出几种常用组合。

规则：如果组合中存在hash，皆把hash分区作为子分区，原因大家可以通过hash分区的性质知道。

例一、range+list，这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区。

CREATE TABLE PART_TAB_SALE_RANGE_LIST

(

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE,

SALES_COST NUMBER(10),

STATUS VARCHAR2(10),

)

PARTITION BY RANGE(SALES_DATE)

SUBPARTITION BY LIST (STATUS)

(

PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE part_Data1

(

SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,

SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,

SUBPARTITION P1SUB3 VALUES (DEFAULT) TABLESPACE part_Data3

),

PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE part_Data2

(

SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,

SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,

SUBPARTITION P2SUB3 VALUES (DEFAULT) TABLESPACE part_Data3

),

PARTITION P3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3

(

SUBPARTITION P3SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,

SUBPARTITION P3SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,

SUBPARTITION P3SUB3 VALUES (DEFAULT) TABLESPACE part_Data3

)

)

例二、range+bash，这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

partition by range(transaction_date)

subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)

(partition part_01 values less than(to_date(‘2006-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)

);

三、分区表的操作

1.DML操作

说明：DML操作和平常的表一样，有异于的地方是增加了可以指定表的特定分区才执行DML操作。

例如，查询分区表

SELECT * FROM PART_TAB_SALE_RANGE_LIST;--不指定分区直接查询

SELECT * FROM PART_TAB_SALE_RANGE_LIST PARTITION(P2);--指定分区查询

SELECT * FROM PART_TAB_SALE_RANGE_LIST SUBPARTITION(P1SUB2);--指定小分区查询

对于分区表，指定分区执行DML效率更高，但，如果指定了分区，而条件中的数据又不在该分区中时，将不会产生任何DML操作。

2.DDL操作

1）添加分区

（1）对range分区表添加分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2009-06-01','YYYY-MM-DD'));

注意：增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。

（2）对range分区表list子分区添加分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

2）删除分区

（1）对range分区表删除分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP PARTITION P3;

（2）对range分区表list子分区删除子分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP SUBPARTITION P4SUB1;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

3）截断分区

说明：截断某个分区是指清空某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。

注意：如果截断的分区表有约束，需要先关闭约束。alter table sales disable/enable constraint restraint_name，截断分区会使全局索引无效，需要重建。

(1)清空分区：

ALTER TABLE SALES TRUNCATE PARTITION P2;--这种方式使全局分区索引无效

ALTER TABLE SALES TRUNCATE PARTITION P2 update indexes;--这种方式使全局分区索引有效UPDATE GLOBAL INDEXES

(2)清空子分区：

ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2;

ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2 update indexes;

4）合并分区

说明：合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

ALTER TABLE PART_TAB_SALE_RANGE_LIST MERGE PARTITIONS P1,P2 INTO PARTITION P2;

注意：在本例中将原有的表的part_01分区和part_02分区进行了合并，合并后的分区为part_02,

如果在合并的时候把合并后的分区定为part_01的时候，系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

5）拆分分区

说明：拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE PART_TAB_SALE_RANGE_LIST SBLIT PARTITION P2 AT(TO_DATE('2003-03-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6）接合分区

说明：接合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，

值得注意的是，接合分区只能用于散列分区中。

ALTER TABLE PART_TAB_SALE_RANGE_LIST COALESCA PARTITION;

7）重命名表分区

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8）移动分区

说明：把分区移动到令一个表空间，移动后要重建索引

alter table sales move partiton sp1 tablespace tablespace_name;

alter index index_name rebuild;

--查询是否移动成功 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';

四、表分区相关的数据字典表

--显示表分区信息 显示数据库所有分区表的详细分区信息： select * from DBA_TAB_PARTITIONS

--显示子分区信息 显示数据库所有组合分区表的子分区信息： select * from DBA_TAB_SUBPARTITIONS

--显示数据库所有分区表的信息： select * from DBA_PART_TABLES

--显示数据库可访问的所有分区表的分区列信息：select * from DBA_PART_KEY_COLUMNS

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

--查询索引信息

select object_name,object_type,tablespace_name,sum(value)

from v$segment_statistics

where statistic_name IN ('physical reads','physical write','logical reads') and object_type='INDEX'

group by object_name,object_type,tablespace_name

order by 4 desc

五、局部索引与全局索引

表可以按range，hash，list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为2类，即局部索引和全局索引。

关于由Oracle分区表引出的局部索引与全局索引，查看 “Oracle 索引”

Oracle数据库表做表分析、索引分析的命令是什么？

analyze table 表名 compute statistics\x0d\x0aanalyze index 索引ID compute statistics\x0d\x0a\x0d\x0a如果想分析所有的表名和index名可以从视图user_tables，user_indexes取得相关的信息，自动生成SQL命令

如何在ORACLE系统中对表进行统计分析

begin

dbms_stats.gather_table_stats( ownname='XXXX',tabname = 'XXXX' ,ESTIMATE_PERCENT=100,degree=8, CASCADE=true);

end;

如何正确地分析Oracle数据库模式，表及索引

在Oracle 数据库中，我们经常需要去手工分析表和索引，还有某个模式下所有的表和索引等等。oracle 提供了两种分析方法，分别是dbms_stats 包和analyze 命令。这些是我们在日常项目管理中的真实使用的方法，供参考。

在10g 中分析某一个用户下所有的对象，如user_miki 用户

Sql代码 收藏代码

sys.dbms_stats.gather_schema_stats(ownname = 'user_miki',estimate_percent = 30,method_opt = 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade = true,options = 'GATHER');

这是我在项目中分析一个用户所有对象的分析方法，取样比例为30% ，分析所有索引字段，生成柱形图，并且也分析索引。

(miki西游 @mikixiyou 文档，原文链接: )

在10g 中分析某一个用户下某一个表及其索引，如user_miki 用户下info 表和它的索引

Sql代码 收藏代码

execute dbms_stats.gather_table_stats(ownname = 'user_miki',tabname = 'info',estimate_percent = 30,method_opt = 'for all indexed columns size auto');

这是我在项目中单独分析一个用户中一张表的分析方法，取样比例为30% ，分析所有索引字段，生成柱形图，并且也分析索引。

我们在google 时，还能看到使用analzye 命令分析表的方法。

Sql代码 收藏代码

analyze table table_name compute statistics;

这个会将索引和表一起分析；

Sql代码 收藏代码

analyze table table_name compute statistics for table;

这个仅分析表；

analyze table table_name compute statistics for all columns;

这个仅分析表，和上面所有的字段，生成柱形图

analyze table table_name compute statistics for all indexed columns;

这个仅分析表，和上面所有建立索引的字段，生成柱形图

在9i 中，我们常常使用analyze 方法，在10g 以后，就改为dbms_stats.gather_schema_stats 类似的包来分析数据库对象了。

10g 的分析函数包比9i 的analyze 方法更灵活一些。

在10g 中，会有个系统的计划任务，每天10 点和周末两天都自动分析新的数据库对象和它认为统计信息已经过期的数据库对象。这个分析结果有时候不靠谱。

因此，我都采用10g 中提供的dbms_stats.gather_schema_stats 方法作为补充，设置一个计划任务定期去分析业务用户下所有数据库对象。

Sql代码 收藏代码

variable job number;

begin

sys.dbms_job.submit(job = :job,

what = 'sys.dbms_stats.gather_schema_stats(ownname = ''USR_MIKI'',estimate_percent = 30,method_opt = ''FOR ALL INDEXED COLUMNS SIZE AUTO'',cascade = true,options = ''GATHER'');',

next_date = to_date('16-12-2012 00:56:24', 'dd-mm-yyyy hh24:mi:ss'),

interval = 'sysdate+7');

commit;

end;

/

例如，设置为每周六运行一次。

oracle如何判断需要做表分析

首先要了解为什么要进行表分析，表分析的结果被Oracle用于基于成本的优化生成更好的查询计划。所以，一般发现如有个表的查询性能比较差，或者原本有索引但是没有走，那就可以考虑对表进行分析。