cf调优完整版本

--20161023--cf调优实验
drop table TEST7;
create table TEST7(deptno PRIMARY KEY ,dname)as
select object_id,a.OBJECT_NAME from all_objects a where a.OBJECT_ID<1000 order by a.OBJECT_NAME;


drop table TEST8
create table TEST8(deptno PRIMARY KEY ,dname)as
select object_id,a.OBJECT_NAME from all_objects a where a.OBJECT_ID<1000 order by a.OBJECT_ID desc;


--查询
select t.deptno,t.dname
from (select a.deptno,a.dname,
row_number() over(partition by a.dname order by rownum) rn
from TEST7 a --where a.deptno=8
) t
where t.rn = 1;

/* 统计信息
----------------------------------------------------------
31 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
25390 bytes sent via SQL*Net to client
1054 bytes received via SQL*Net from client
60 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
878 rows processed*/

select a.deptno,a.dname
from TEST7 a
where a.rowid =
(select min(rowid) from TEST7 b where b.dname = a.dname);
/* 统计信息
----------------------------------------------------------
57 recursive calls
0 db block gets
131 consistent gets
0 physical reads
0 redo size
25390 bytes sent via SQL*Net to client
1054 bytes received via SQL*Net from client
60 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
878 rows processed */




-- EXEC DBMS_STATS.gather_index_stats(USER, 'DEPTNO');
EXEC dbms_stats.gather_table_stats(USER,'TEST7');
EXEC dbms_stats.gather_table_stats(USER,'TEST8');


--查看聚簇因子：
select b.table_name,
a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from user_indexes a, user_tables b
where b.table_name in ('TEST7', 'TEST8')
and a.table_name = b.table_name;

TABLE_NAME INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
TEST7 SYS_C0010510 883 3 406
TEST8 SYS_C0010513 883 3 3
-- TEST7 cf值与nr值相近，计划很差
-- cf调优
-- tuncate原表
truncate table TEST7;


--按序临时表
drop table TEST7_temp;
create table TEST7_temp(deptno PRIMARY KEY ,dname) as
select object_id,a.OBJECT_NAME from all_objects a where a.OBJECT_ID<1000 order by a.object_id desc;

--重新插入值
insert into TEST7 select * from TEST7_temp;

--查看聚簇因子：
EXEC dbms_stats.gather_table_stats(USER,'TEST7');
EXEC dbms_stats.gather_table_stats(USER,'TEST8');
select b.table_name,
a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from sys.user_indexes a, sys.user_tables b
where b.table_name in ('TEST7', 'TEST8')
and a.table_name = b.table_name;

TABLE_NAME INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
TEST7 SYS_C0010525 883 3 3
TEST8 SYS_C0010528 883 3 3

--查询
select t.deptno,t.dname
from (select a.deptno,a.dname,
row_number() over(partition by a.dname order by rownum) rn
from TEST7 a --where a.deptno=8
) t
where t.rn = 1;
/*
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
25390 bytes sent via SQL*Net to client
1054 bytes received via SQL*Net from client
60 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
878 rows processed
*/


select a.deptno,a.dname
from TEST7 a
where a.rowid =
(select min(rowid) from TEST7 b where b.dname = a.dname);
/* 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
25390 bytes sent via SQL*Net to client
1054 bytes received via SQL*Net from client
60 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
878 rows processed*/