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*/