oracle怎么分析表 oracle 分析表

如何分析oracle数据库中的表结构？

去这里看看??

创新互联建站是一家专业提供怀安企业网站建设,专注与网站设计、成都做网站、成都h5网站建设、小程序制作等业务。10年已为怀安众多企业、政府机构等服务。创新互联专业网站建设公司优惠进行中。

数据结构被称为物理（存储）的数据结构在计算机中的表示（图片）。它包括一个数据元素表示的关系的表示。

物理结构，也就是由Oracle数据库所使用的操作系统的文件结构。对于数据库的物理结构文件，不同版本的Oracle，不同的操作系统平台上的数据库文件存放的目录结构不同的物理结构

其作用可分为三类：

数据日志文件的文件

控制文件

数据文件，数据文件，用于存储数据库中的数据，如表，索引等。当读取数据时，系统首先读取的数据从数据库文件，并存储到数据缓冲器，SGA。

重做日志文件，重做日志文件

所有的记录在数据库中的信息。这是三种类型的文件，文件中最复杂的，而且要保证数据库的安全性和数据库的备份和恢复文件直接。

控制文件

控制文件是一个二进制文件，用来描述数据库的物理结构，数据库只需要一个控制文件，控制文件的内容，包括：

同步需要恢复的数据文件和日志文件的信息标识数据库和数据库的名称，唯一标识

数据库，检查点数量

/

a

如果有，请记得采纳为满意的答复，谢谢你！我祝你幸福的生活！

vaela

如何分析Oracle

以oracle表分析为例：

drop table test;

select count(*) from test;

--创建测试表

create table test

(

id number(9),

nick varchar2(30)

);

--插入测试数据

begin

for i in 1..100000 loop

insert into test(id) values(i);

end loop;

commit;

end;

select * from test;

--更新nick字段，使数据发生严重倾斜

update test set nick='abc' where rownum99999;

--创建索引

create index idx_test_nick on test(nick);

update test set nick='def' where nick is null;

--只对索引进行分析

analyze index idx_test_nick compute statistics;

select * from user_indexes;

--查看索引名,对应存储的数据块,不同的key数量,记录数（行数）的分析信息

select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS

from user_indexes

where index_name = 'IDX_TEST_NICK';

--dba_tab_col_statistics

--查看表的统计信息

select COLUMN_NAME, NUM_BUCKETS, num_distinct

from USER_tab_columns

where table_name = 'TEST';

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)

select * from test where nick ='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)

--由上可以看到,对索引分析之后,sql的执行路径都是基于规则的,索引的字段的偏移

--先根据索引找到rowid,然后再根据rowid读取记录,这个过程肯定比全表扫描读取记录要慢

--user_part_col_statistics 分区分析信息

--分析表的第二列nick

analyze table test compute statistics for columns size 2 nick;

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)

--根据上面的执行计划，还是按照规则来执行的

--分析表

analyze table test compute statistics for table;

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=

1499970)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14

99970)

--分析表之后，完全按照成本来执行

--删除所有的统计数据，并只对表与列进行分析，不分析索引，

--ORACLE使用CBO的优化器，并产生了正确的执行计划

analyze table test delete statistics;

--分析列nick

analyze table test compute statistics for table for columns size 2 nick;

select * from test where nick ='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=

1499970)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14

99970)

--

select * from test where nick ='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt

es=30)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost

=1 Card=2)

--创建TEST表ID列上的索引，但不对索引进行分析

create index idx_test_id on test(id);

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15

000)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000

Bytes=15000)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1

Card=400)

--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick='abc'的值特别的多，所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引

select * from test where id=5 and nick='abc';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15

000)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000

Bytes=15000)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1

Card=400)

--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick='def'的值特别的少，所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引

select * from test where id=5 and nick='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=15)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost

=1 Card=2)

select * from test where nick='def' and id=5;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=15)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost

=1 Card=2)

--在分析ID列后，ORACLE发现ID列的选择度更高，所以不再选择IDX_TEST_NICK索引，而是选择IDX_TEST_ID

analyze table test compute statistics for columns size 1 id;

select * from test where id=5 and nick='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=7)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1

Card=1)

/*

下面来看另外一种情况，我们删除所有的统计数据，然后在ID列上创建唯一索引,在此条件下，

只分析表与分析列nick,我们看到ORACLE走了正确的执行计划，

走了UK_TEST_ID,其实从这里也给我们带来很多的启示：

在主键与唯一键约束的列上是否需要直方图的问题?

如果在这些列上有像这样的查询where id 100 and id 1000,

我们还是需要有直方图的，但除此之外，好像真的没有直方图的必要了!

*/

analyze table test delete statistics;

drop index idx_test_id;

create unique index uk_test_id on test(id);

--分析表的第二列nick

analyze table test compute statistics for table for columns size 2 nick;

select * from test where id=5 and nick='def';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt

es=15)

2 1 INDEX (UNIQUE SCAN) OF 'UK_TEST_ID' (UNIQUE) (Cost=1 Car

d=100000)

从以上一系列的实验可以看出，对ORACLE的优化器CBO来说，表的分析与列的分析才是最重要的，索引的分析次之。还有我们可以考虑我们的哪些列上需要直方图，对于bucket的个数问题,oracle的默认值是75个，所以根据你的应用规则，选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加，必然会带来SQL语句硬解析时产生执行计划的复杂度问题。

oracle分析表有什么用

Oracle中分析表的作用

1.分析更新表的统计信息,,有可能导致执行计划改变..

2.以的analyze

table

abc

compute

statistics;这条为例，生成的统计信息会存在于user_tables这个视图，查看一下select

*

from

user_tables

where

table_name='ABC';

观察一下NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN几列你就会明白，这就是变化。分析完表之后，会发现DBA_tables

视图中，以前很多列值是空的，现在开始有数据了。这些数据就是分析表得到的.

3.怎么样分析表或索引

命令行方式可以采用analyze命令

如Analyze

table

tablename

compute

statistics;

Analyze

index|cluster

indexname

estimate

statistics;

ANALYZE

TABLE

tablename

COMPUTE

STATISTICS

FOR

TABLE

FOR

ALL

[LOCAL]

INDEXES

FOR

ALL

[INDEXED]

COLUMNS;

ANALYZE

TABLE

tablename

DELETE

STATISTICS

ANALYZE

TABLE

tablename

VALIDATE

REF

UPDATE

ANALYZE

TABLE

tablename

VALIDATE

STRUCTURE

[CASCADE]|[INTO

TableName]

ANALYZE

TABLE

tablename

LIST

CHAINED

ROWS

[INTO

TableName]

等等。

如果想分析整个用户或数据库，还可以采用工具包，可以并行分析

Dbms_utility(8i以前的工具包)

Dbms_stats(8i以后提供的工具包)

如

dbms_stats.gather_schema_stats(User,estimate_percent=100,cascade=

TRUE);

dbms_stats.gather_table_stats(User,TableName,degree

=

4,cascade

=

true);

这是对命令与工具包的一些总结

(1)、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。

a)

可以并行进行，对多个用户，多个Table

b)

可以得到整个分区表的数据和单个分区的数据。

c)

可以在不同级别上Compute

Statistics：单个分区，子分区，全表，所有分区

d)

可以倒出统计信息

e)

可以用户自动收集统计信息

(2)、DBMS_STATS的缺点

a)

不能Validate

Structure

b)

不能收集CHAINED

ROWS,

不能收集CLUSTER

TABLE的信息，这两个仍旧需要使用Analyze语句。

c)

DBMS_STATS

默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True

(3)、对于Oracle

9里面的External

Table，Analyze不能使用，只能使用DBMS_STATS来收集信息.