如何查oracle表分析,oracle怎么做表分析
oracle如何判断需要做表分析
首先要了解为什么要进行表分析，表分析的结果被Oracle用于基于成本的优化生成更好的查询计划。所以，一般发现如有个表的查询性能比较差，或者原本有索引但是没有走，那就可以考虑对表进行分析。
创新互联建站是一家集网站建设,从江企业网站建设,从江品牌网站建设,网站定制,从江网站建设报价,网络营销,网络优化,从江网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。
Oracle之查询详解
查询是数据的一个重要操作。用户发送查询请求，经编译软件变异成二进制文件供服务器查询，后返回查询结果集给用户，查询会产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正的存储，每次执行查询只是从数据表中提取数据，并按照表的形式显示出来。
SELECT 列名
FGROM 表名
[WHERE 查询条件表达式]
[GROUP BY 分组表达式]
[HAVING 分组查询表达式]
[ORDER BY 排序的列名 [ASC或DESC]]
group by 用于对查询的结果分组统计，通过对group by后面的名字进行分组后输出结果。
group by后面还可以跟多列表示 多列分组 ，在多列分组时放前面的优先分组。
group by 列名,列名
having 子句用于限制分组显示结果，其只能和group by一起连用。在where中没有办法直接使用聚合函数，即sum avg等无法使用，所以引用了having，在having中可以使用这些函数。
order by 表示排序，后跟列名和排序方式。如果什么都不加默认为升序。ASC表示升序，DESC表示降序。
在Oracle中还可以设置多列排序
order by 列名1 升降,列名2 升降;
前面的为主要排序，后面的为次一级排序。
注：碰到自己与自己比较的情况下，不能用having，可以创建一个新列。
注：如果select语句同时包含group by,having,order by,按group by,having,order by排序
分组和聚合一起使用，目的是为了统计信息。
where是为了from服务的，只能跟 真实的字段 ，用来筛选from子句中指定的操作所产生的行
group by 用来分组where子句的输出
having 用来从分组的结果中筛选行
order by用来对筛选的结果进行排序
（1）分组函数：max min avg sum count
max表示该列的最大值，min表示该列的最小值，avg表示该列的平均值，sum表示该列的和，count表示该列的行数。
注：分组函数(max、min、avg、count、sum)只能出现在选择列表中having子句、order by子句、不能出现在where子句和group by子句中。
（2）多表查询
多表查询是指两个和两个以上的表或者是视图的查询，在实际应用中，当查询单个表不能满足需求时，一般使用多表查询。如：显示sales部门位置和其员工的姓名，这种情况下需要使用到(dept表和emp表)。
多表查询的连接一般可以分为：内连接、左外连接、右外连接、全连接。
注：在使用多表查询的时候每个表可以设置别名，如果表指定了别名，那么语句中所有语句必须使用别名，而不能再使用实际表名。且在写属性的时候如果属性为其中一个表特有的属性则不需要写别名，如果是两个表都有则必须指定是哪一个表的哪个属性格式为：表名.属性名。
select 列名 from 表1 别名,表2 别名...
注：e是emp的别名，d是dept的别名。
但如果对表进行了操作则需要设置别名，如：查询每个部门中工资高于该部门平均工资的员工人数。在其中有一个avg表，这个表必须设置别名（提醒：如果仅有一个被修改的表，则可以不设置别名，但如果有多个表则必须设置别名）。
内连接
内连接通过使用比较运算符来使每个表的通用列中的值匹配来组成一个新表，即：把两个表中间共有的那些行拿出来进行连接，如果某些行不是两个表共有的，则不进行连接。
select
from 表1
inner join 表2
on 匹配条件
或
select
from 表1 表2
where匹配条件
左外连接
左外连接与内连接的区别是：设置左外连接的时候设置了主表和附表，主表在前，附表在后。内连接是将两个表匹配的地方输出出来，而左外连接则是主表全写，附表一一对应，附表有则加上，没有不写。
select
from 表1
left join 表2
on 匹配条件
右外连接
右外连接和左外连接基本相同只是右外连接的主表写在后边。
select
from 表1
right join 表2
on 匹配条件
全连接
全连接是在等值连接的基础上将左表和右表的未匹配数据都加上，使用的关键字为full outer join或者full join。
select
from 表1
full join 表2
on 匹配条件
自连接
还有一种特殊情况即自连接，在Oracle中一个表无法与自己进行比较，所以当需要自己表的两个信息做比较的时候也需要使用连接来连接，即同一张表的连结查询。
（3）子查询
子查询是指嵌套在其他sql语句中的select语句，也叫嵌套查询。sql语句执行顺序为从右到左执行，所以在执行查询时会先执行左侧的子查询后进行主查询。
子查询分为单行子查询和多行子查询，单行子查询是指返回一行数据的子查询语句，多行子查询是指返回多行数据的查询语句。子查询还可以分为多列子查询、多行子查询、多列多行子查询。
在进行子查询时如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返回任何结果。
总结为：
单行子查询是指子查询只返回单列、单行数据
多行子查询是指返回单列多行数据，都是针对单列而言的
多列子查询则是指查询返回多个列数据的子查询语句
单行子查询
where deptno = (单行数值)
多行子查询
where deptno in ( 多行数值 )
多列子查询：
where (job,deptno)=(select job,deptno from emp where ename='KING')
多列多行子查询
where (job,deptno) in (select job,deptno from emp where ename='KING')
单行子查询
在单行子查询的外部查询中可以使用=、、、=、=、等比较运算符。
内部查询返回的结果必须与外部查询条件中字段(DEPTNO)相匹配。
多行子查询
在WHERE子句中使用多行子查询时，可以使用多行比较运算符（IN,ALL,ANY）。
IN：等于任何一个。
ALL：和子查询返回的所有值比较。例如：salALL(1,2,3)等价于sal3，即大于所有。
ANY：和子查询返回的任意一个值比较。例如：salANY(1,2,3)等价于sal1，即大于任意一个就可以。
注：ANY运算符必须与单行比较运算符结合使用,并且返回行只要匹配子查询的任何一个结果即可。
多列子查询
多列子查询和多行子查询相同，只是使用多列子查询的时候会有多列进行匹配。
（4）集合运算
为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus。
union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行
union all：该操作与union相似，但是它不会取消重复行，而且不会排序
intersect：使用该操作符用于取得两个结果集的交集
minus：使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据
总结为集合运算就是将两个或者多个结果集组合成一个结果集。
intersect 交集 返回两个查询共有的记录
union all 并集 返回各个查询的所有记录，包括重复的记录
union 交集 返回各个查询的所有记录，不包括重复的记录
MINUS 补集 返回第一个查询检查出的记录减去第二个查询检索出来的记录之后剩余的记录
注意：当使用集合操作的时候，查询所返回的列数以及列的类型必须匹配，列名可以不同。
（1）Distinct关键字
在Oracle中，可能出现若干相同的情况，那么可以用Distinct消除重复行
（2）多表查询与单行子查询可以实现相同的功能
查询出销售部（sales）下面的员工姓名，工作，工资
（3）显示高于自己部门平均工资的员工信息
分析：
1.找到所有部门的平均工资
select deptno,avg(sal) from emp group by deptno;
2.找到所有人的工资信息
select ename,sal,deptno from emp;
3.把两个结果集使用多表连接组合组合起来
select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno;
4.去掉低于平均工资的那些数据即可：
select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno and salavgsal;
（4）emp表介绍
字段 类型 描述
empno NUMBER(4) 表示雇员编号，是唯一编号
ename VAECHAR2(10) 表示雇员姓名
job VARCHAR2(9) 表示工作职位
mgr NUMBER(4) 表示一个雇员的领导编号
hiredate DATE 表示雇佣日期
sal NUMBER(7,2) 表示月薪，工资
comm NUMBER(7,2) 表示奖金，或者称为佣金
deptno NUMBER(2) 部门编号
如何分析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 auto space advisor 分析结果
oracle 数据库里查看表空间使用状况；
oracle表空间的事情状况要经常查看，一般空闲比例过低的时候就应该考虑增大表看空间了。查看方法如下SQL:
方法一：
select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
方法二：
SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name
当发现有的表空间不够的错误时，处理如下：
1：找出该表空间对应的数据文件及路径
select * from dba_data_files t
where t.tablespace_name = 'ARD'
2：增大数据文件
alter database datafile '全路径的数据文件名称' resize ***M
3：增加数据文件
alter tablespace 表空间名称
add datafile '全路径的数据文件名称' ***M
注解：表空间尽量让free百分比保持在10%以上，如果低于10%就增加datafile或者resizedatafile，一般数据文件不要超过2G
如何分析oracle数据库中的表结构？
去这里看看??
数据结构被称为物理（存储）的数据结构在计算机中的表示（图片）。它包括一个数据元素表示的关系的表示。
物理结构，也就是由Oracle数据库所使用的操作系统的文件结构。对于数据库的物理结构文件，不同版本的Oracle，不同的操作系统平台上的数据库文件存放的目录结构不同的物理结构
其作用可分为三类：
数据日志文件的文件
控制文件
数据文件，数据文件，用于存储数据库中的数据，如表，索引等。当读取数据时，系统首先读取的数据从数据库文件，并存储到数据缓冲器，SGA。
重做日志文件，重做日志文件
所有的记录在数据库中的信息。这是三种类型的文件，文件中最复杂的，而且要保证数据库的安全性和数据库的备份和恢复文件直接。
控制文件
控制文件是一个二进制文件，用来描述数据库的物理结构，数据库只需要一个控制文件，控制文件的内容，包括：
同步需要恢复的数据文件和日志文件的信息标识数据库和数据库的名称，唯一标识
数据库，检查点数量
/
a
如果有，请记得采纳为满意的答复，谢谢你！我祝你幸福的生活！
vaela
网站名称：如何查oracle表分析,oracle怎么做表分析
转载来源：http://scjbc.cn/article/hcdsig.html
