Oracle

一  关于索引:

表分析,索引分析

二 关于表分区:

三 关于分库:

四 关于负载:

五 关于灾备:

一、        关于索引 


要写出运行效率高的sql，需要对索引的机制有一定了解，下面对索引的基本知识做一介绍。 

1、        索引的优点和局限 

索引可以提高查询的效率，但会降低dml操作的效率。 


所以建立索引时需要权衡。对于dml操作比较频繁的表，索引的个数不宜太多。 

2、        什么样的列需要建索引？ 


经常用于查询、排序和分组的列（即经常在where、order或group by子句中出现的列）。 

3、        主键索引和复合索引 

对于一张表的主键，系统会自动为其建立索引。 

如果一张表的几列经常同时作为查询条件，可为其建立复合索引。 

4、        建立索引的语句 

create  index  i_staff  on  staff  (empno); 


create  index  i_agent  on  agent  (empno, start_date); 

5、        删除索引的语句 

drop  index  I_staff; 


drop  index  I_agent; 

6、        查询索引的语句 
法一：利用数据字典 


表一：all_indexes  查看一张表有哪些索引以及索引状态是否有效 


主要字段： index_name,  table_name,  status 


例如：select   index_name,  status  


from  all_indexes 


        where  table_name=’STAFF_INFO’; 


      INDEX_NAME        STATUS 


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


      I_STAFF             VALID  


            表二：all_ind_columns  查看一张表在哪些字段上建了索引 


              主要字段： table_name,  index_name,  column_name,  column_position 


例如： select  index_name,  column_name,  column_position 


from  all_ind_columns 


         where  table_name=’AGENT’ 


      INDEX_NAME        COLUMN_NAME     COLUMN_POSITON 


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


     I_AGENT             EMPNO              1 


     I_AGENT             START_DATE         2 


由此可见，agent表中有一个复合索引（empno, start_date ） 


     法二：利用toad工具 


toad用户界面比sql*plus友好，并且功能强大。你可以在toad编辑器中键入表名，按F4，便可见到这张表的表结构以及所有索引列等基本信息。 

7、        索引的一些特点 


1): 不同值较多的列上可建立检索，不同值少的列上则不要建。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。 


2): 如果在索引列上加表达式，则索引不能正常使用 


   例如：b1,c1分别是表b,c的索引列 


select  *  from  b  where  b1/30  1000 ; 


select  *  from  c  where  to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’; 


以上都是不正确的写法 


        3): where子句中如果使用in、or、like、!=，均会导致索引不能正常使用 


           例如：select  *  from  b  where  b1=30  or  b1=40; 


      4): 使用复合索引进行查询时必须使用前置列 


         例如表a上有一个复合索引（c1,c2,c3）,则c1为其前置列 


         如果用c1或c1+c2或c1+c2+c3为条件进行查询，则该复合索引可以发挥作用，反之，用c2或c3或c2+c3进行查询，则该索引不能起作用。 

二. 书写sql注意事项： 

1、        避免给sql语句中引用的索引列添加表达式： 

典型实例： 


b1,c1分别是表b,c的索引列： 


1）        select  *  from  b  where  b1/30  1000 ; 


2）        select  *  from  c  where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’; 


替代方案： 


1)        select  *  from  b where  b1   30000; 


2)        select * from c  where c1 = to_date(‘20020301 14:01:01’, ‘YYYYMMDD HH24:MI:SS’); 


注：在lbs中有两个重要字段，pol_info中的undwrt_date和prem_info中的payment_date，这两个日期是带时分秒的，所以经常有同事用to_char 来查询某一时间段的数据。 


例如：select  count(*)  from  pol_info  where  to_char(undwrt_date,’YYYYMMDD’)=’20020416’; 


      select  count(*)  from  prem_info  where  to_char(undwrt_date,’YYYYMM’)=’200203’; 


替代方案： 


select  count(*)  from  pol_info  


where  undwrt_date =to_date(’20020416’,’YYYYMMDD’)  and 


           undwrt_date to_date(’20020417’,’YYYYMMDD’); 


select  count(*)  from  prem_info  


where  payment_date =to_date(’20020301’,’YYYYMMDD’)  and 


         payment_date to_date(’20020401’,’YYYYMMDD’); 


2、        避免在where子句中使用in、or、like、!= 


典型实例： 


a1是a表上的索引列： 


1) select  *  from  a 


   where  ( a1 = ‘0’ and ...)  or  (a1 = ‘1’ and ...); 


2) select  count(*)  from  a  where  a1  in  (‘0’,’1’) ; 


替代方案： 


1）        select  *  from  a  where  a1 = ‘0’  and ... 


union 


select  *  from  a  where  a1 = ‘1’  and ... 


2) select  count(*)  from  a  where  a1 = ‘0’; 


  select  count(*)  from  a  where a1 = ‘1’; 


   然后做一次加法运算；或者直接用存储过程来实现； 


小结： 


对字段使用了 ‘in，or，like’ 做条件、对字段使用了不等号 ‘!=’，均会使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引，或者使用union连结符代替。另一种方式是使用存储过程，它使SQL变得更加灵活和高效。 


3、        建立适当的索引 


曾经接过开发的一个统计sql， select …  from  tablea  where  cola=…  and  … 


运行效率非常慢，经查tablea数据量巨大，再查all_ind_columns，发现cola是tablea的一个复合索引中的一列，但不是前置列。象这种情况，就需要与开发商量，是否针对cola建一个索引。 

4、        like和substr 


对于‘like’和‘substr’，其效率并没有多大分别。但是，当所搜索的值不存在时，使用‘like’的速度明显大于‘substr’。 


所以：select  *  from  a  where  substr(a1,1,4) = '5378'  可以用like替代 


select  *  from  a  where  a1  like  ‘5378%’; 

5、 写where条件时，有索引字段的判断在前，其它字段的判断在后；如果where条件中用到复合索引，按照索引列在复合索引中出现的顺序来依次写where条件； 

6、使用多表连接时，在from子句中，将记录数少的表放在后面，可提高执行效率； 

7、避免使用not in 

not  in 是效率极低的写法，尽量使用minus或外连接加以替代 

典型实例： 

1) select col1 from tab1 where col1 not in (select col1 from tab2); 


2) select sum(col2) from tab1 where col1 not in (select col1 from tab2); 

替代方案 

select col1 from tab1 minus  select col1 from tab2; 


    select  sum(a.col2)  from  tab1 a, tab2  b 


where a.col1=b.col2(+) and b.col1 is null; 

8、多表查询时，如果其中一个表的记录数量明显大于其他表，则可以先对此表进行查询后，再与其他小表进行表连接。 

典型实例： 

select  a.plan_code,  b.dno,  c,tno,  sum(a.tot_modal_prem), 


from  prem_info a,  dept_ref b,  plan_type c 


where  substr(a.deptno,1,7) = substr(b.deptno,1,7) 


and a.plan_code = c.plan_code 


group by b.dno,  c.tno,  a.plan_code; 


替代方案： 


select  b.dno,  c.tno,  a.plan_code,  a.tot_amount 


from  (select  plan_code,  deptno,  sum(tot_modal_prem)  tot_amount 


from  prem_info 


group  by  deptno,  plan_code) a 


dept_ref  b, 


plan_type  c 


  where  substr(a.deptno,1,7) = substr(b.deptno,1,7) 


       and  a.plan_code = c.plan_code 


group  by  b.dno,  c.tno,  a.plan_code; 


小结： 


由于prem_info表的记录数远远大于dept_ref表和plan_type表中的记录数， 所以首先从prem_info表中查询需要的记录，此时记录数已经被大量缩小，然后再和其他两个表连接，速度会得到很大改善！ 

9、查询数量较大时，使用表连接代替IN，EXISTS，NOT IN，NOT EXISTS等。 


典型实例： 


a、使用IN： 

select sum(col2) from tab1 where col1 in (select col1 from tab2); 

使用EXISTS:： 


select sum(col2) from tab1 a 


where exists ( select * from tab2 where col1=a.col1); 


b、使用NOT IN： 


select sum(col2) from tab1 where col1 not in (select col1 from tab2); 


使用NOT EXISTS： 

select sum(col2) from tab1 a 


where not exists ( select * from tab2 where col1=a.col1); 


          替代方案： 


a、使用连接： 


select sum(a.col2) from tab1 a,tab2 b where a.col1=b.col2; 

b、使用外连接： 


select sum(a.col2) from tab1 a,tab2 b 


where a.col1=b.col2(+) and b.col1 is null;