oracle——SQL复习11

 

--随机数select trunc( dbms_random.value(1,3) ) from dual; --这个随机添加太有意思了 select decode(trunc( dbms_random.value(1,3) ),1,'M',2,'F') from dual; --收获,不止oracle 讲解索引的总页数: 136select 326 -190 from dual ;索引在应用主要有三类:B-TREE索引位图索引函数索引分区表的本地索引索引结构分为:根  分支  叶一般通过索引访问数据 至少经过3次IO以上索引有三个特点1.索引树的高度一般都比较低 500G的索引树高度也就6层2.索引由列值和rowid组成3.索引是有序的select * from RANGE_PART_TAB partition (PAR_LIST_09) -- 查看段数据select t.segment_name,       t.partition_name,       t.segment_type,       t.tablespace_name,       t.BYTES / 1024  from user_segments t   where t.segment_type = upper('TABLE PARTITION');-- 查看 索引高度的SQL语句select t.INDEX_NAME,       t.blevel,       t.LEAF_BLOCKS,       t.NUM_ROWS,       t.DISTINCT_KEYS,       t.CLUSTERING_FACTOR  from user_ind_statistics t ;    --100万数据  select count(1) from t;--创建索引所需时间: 156s create index t_x on t(x); drop index t_x ;--索引高度的应用create table t50w        as select rownum id, rownum+1 as col_name from dual connect by level <=500000; create table t500w        as select rownum id, rownum+1 as col_name from dual connect by level <=5000000 ;       create index ind_t50w on t50w(id);  create index ind_t500w on t500w(id); select t.INDEX_NAME,       t.blevel,       t.LEAF_BLOCKS,       t.NUM_ROWS,       t.DISTINCT_KEYS,       t.CLUSTERING_FACTOR  from user_ind_statistics t   where lower(t.TABLE_NAME) in('t50w','t500w')/*1IND_T50W2111350000050000010352IND_T500W2117055000000500000011462*/  --71  consistent gets   4   (0)| 00:00:01 |select * from t50w t where t.id = 123465 ;--73  consistent gets    4   (0)| 00:00:01 select * from t500w t where t.id =1237654 ;--结论 因为高度一样  所以50万数据和500万数据通过索引扫描 获取的数据的时间是一样的 --经过测试  索引只适合返回10%总记录的数据select (49970 / count(1)) *100 || '%' from t50w ; select (610000/count(1)) *100 || '%' from t500w ;--183  consistent gets  132   (2)| 00:00:02select count(*) from t50w t where t.id <= 49970;--1433  consistent gets   1280   (2)| 00:00:16select count(*) from t500w t where t.id <=610000 ;/*在cmd 中 sqlplus使用以下语句查看sql的执行计划  */sqlplus scott/tigerset autotrace on;set linesize 1000;set timing on;--set autotrace traceonly;/*==============分区索引的应用==================*/--如果设置了分区索引却用不到分区条件,性能将下降 select count(1) from RANGE_PART_TAB t ; --在RANGE_PART_TAB 上创建'分区索引 ' ,也就是要加上local  create index ind_range_tb_id on RANGE_PART_TAB(id) local;  select count(1) from norm_TAB t ; create index ind_norm_tb_id on norm_TAB(id) ; --性能测试select count(*) from RANGE_PART_TAB t where t.id = 5632;select count(*) from NORM_TAB t where t.id = 5632;select count(*)  from RANGE_PART_TAB t        where t.id = 5632 and t.deal_date = to_date('20150603','YYYYMMDD');--select t.segment_name,       t.partition_name,       t.segment_type,       t.tablespace_name,       t.BYTES / 1024  from user_segments t   where t.segment_name in ( upper('ind_range_tb_id'), upper('ind_norm_tb_id') ) ;    sum(sal) avg(sal) count(*) 扫描数据要走索引时, 必须指定所汇总的列值为非空值.而max() min() 走索引时,只扫最边上的索引叶 可以不指定所汇总的列值为非空值.create index ind_t_x on t(x) ;select min(t.x)  from t; select max , min from (select max(x) max from t ) a,(select min(x) min from t ) b ;/**= ==消除回表==消除 table access by index rowid 这个回表动作可改进性能的案例占比20%左右=*/比如一个应用展示的字段不多,可以通过组合索引进行回表的消除,注意:联合索引不宜列过多超过三个字段组成的联合索引都是不合适的,因为消减了回表动作,而导致索引块变多就可能遍历更多的BLOCK了,反而影响了性能create index idx_un_tb_id_name on tb(id,name);--聚合因子: 索引和表之间数据顺序的相似度越高,聚合因子的值就越低drop table t_order_1 purge;create table t_order_1(x number, col_x number);begin    for i in 1 .. 1000000     loop        insert into  t_order_1  values(i, dbms_random.value(0,10000));     end loop;     commit;end;/--添加主键alter table t_order_1 add constraint pk_t_order_1 primary key(x);drop table t_order_2 purge ;create table t_order_2 asselect * from t_order_1 t  order by t.col_x ;--添加主键alter table t_order_2 add constraint pk_t_order_2 primary key(x);select count(1) from t_order_1 ; select count(1) from t_order_2 ; select * from t_order_1 t where t.x > 2 order by  t. x ;select /*+index(t)*/ * from t_order_1 t where t.x > 2 order by  t. x ;/* 消除回表 table access by index rowid  */select t.x from t_order_1 t where t.x > 2 order by  t. x ;  select t.INDEX_NAME,       t.blevel,       t.LEAF_BLOCKS,       t.NUM_ROWS,       t.DISTINCT_KEYS,       t.CLUSTERING_FACTOR  from user_ind_statistics t    where t.INDEX_NAME  in ( upper('pk_t_order_1'), upper('pk_t_order_2') ) ; /**1PK_T_ORDER_1220871000000100000042732PK_T_ORDER_22208710000001000000999794*/--order by 排序优化--DISTINCT 排重优化--union 合并优化--主外键的作用  级联删除--位图索引select sysdate,trunc(sysdate) from dual ;drop table t_date_tst purge;create table t_date_tst(col_date date) ;begin     for x in 1 .. 120000   loop      insert into t_date_tst      select sysdate - dbms_random.value(0,13) from dual;    end loop;      commit; end;  select * from t_date_tst;CREATE INDEX IND_t_date_tst_col_date on t_date_tst(col_date) ;select count(*)  from t_date_tst t where trunc(t.col_date) >= to_date('20160402','YYYYMMDD') AND  trunc(t.col_date) <= to_date('20160403','YYYYMMDD');select count(*)  from t_date_tst t where  t.col_date  >= to_date('20160402','YYYYMMDD') AND   t.col_date  <  to_date('20160403','YYYYMMDD')+ 1