一、
关于索引的知识
要写出运行效率高的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;
要写出运行效率高的sql,需要对索引的机制有一定了解,下面对索引的基本知识做一介绍。
1、
索引可以提高查询的效率,但会降低dml操作的效率。
所以建立索引时需要权衡。对于dml操作比较频繁的表,索引的个数不宜太多。
2、
经常用于查询、排序和分组的列(即经常在where、order或group by子句中出现的列)。
3、
对于一张表的主键,系统会自动为其建立索引。
如果一张表的几列经常同时作为查询条件,可为其建立复合索引。
4、
create
create
5、
drop
drop
6、
法一:利用数据字典
表一:all_indexes
主要字段: index_name,
例如:select
from
例如: select
from
由此可见,agent表中有一个复合索引(empno, start_date )
toad用户界面比sql*plus友好,并且功能强大。你可以在toad编辑器中键入表名,按F4,便可见到这张表的表结构以及所有索引列等基本信息。
7、
1): 不同值较多的列上可建立检索,不同值少的列上则不要建。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就没必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
2): 如果在索引列上加表达式,则索引不能正常使用
select
select
以上都是不正确的写法
二. 书写sql注意事项:
1、
典型实例:
b1,c1分别是表b,c的索引列:
1)
2)
替代方案:
1)
2)
注:在lbs中有两个重要字段,pol_info中的undwrt_date和prem_info中的payment_date,这两个日期是带时分秒的,所以经常有同事用to_char 来查询某一时间段的数据。
例如:select
替代方案:
select
where
select
where
2、
典型实例:
a1是a表上的索引列:
1) select
2) select
替代方案:
1)
union
select
2) select
小结:
对字段使用了 ‘in,or,like’ 做条件、对字段使用了不等号 ‘!=’,均会使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引,或者使用union连结符代替。另一种方式是使用存储过程,它使SQL变得更加灵活和高效。
3、
曾经接过开发的一个统计sql, select …
运行效率非常慢,经查tablea数据量巨大,再查all_ind_columns,发现cola是tablea的一个复合索引中的一列,但不是前置列。象这种情况,就需要与开发商量,是否针对cola建一个索引。
4、
对于‘like’和‘substr’,其效率并没有多大分别。但是,当所搜索的值不存在时,使用‘like’的速度明显大于‘substr’。
所以:select
select
5、写where条件时,有索引字段的判断在前,其它字段的判断在后;如果where条件中用到复合索引,按照索引列在复合索引中出现的顺序来依次写where条件;
6、使用多表连接时,在from子句中,将记录数少的表放在后面,可提高执行效率;
7、避免使用not in
not
典型实例:
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
where a.col1=b.col2(+) and b.col1 is null;
8、多表查询时,如果其中一个表的记录数量明显大于其他表,则可以先对此表进行查询后,再与其他小表进行表连接。
典型实例:
select
from
where
and a.plan_code = c.plan_code
group by b.dno,
替代方案:
select
from
from
group
dept_ref
plan_type
group
小结:
由于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;