请教一个SQL优化问题

Question

我有两个表:student与stu_organise，其中student存放学生学号及学生相关信息，stu_organise存放学生参加的社团的组织；表结构脚本如下：CREATE TABLE student (NO VARCHAR2(10), NAME VARCHAR2(10));ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY (NO);
CREATE TABLE stu_organise (org_id VARCHAR2(10), no_s VARCHAR2(10), no_e VARCHAR(10));ALTER TABLE stu_organise ADD CONSTRAINT pk_stu_organise PRIMARY KEY (org_id, no_s);
stu_organise中org_id 表示社团ID，no_s、no_e表示参加该社团的起始学号、结束学号
如：社团ID为00001的学号有1, 2, 3, 5, 6，该表存放数据如下：
org_id  no_s  no_e
00001    1      3
00001    5      6注：不要问我为什么这样组织表结构，我只说一个原因：数据量非常大。在此，我要查询某社团ID的所有学生信息，我用如下语句：SELECT * FROM student a
WHERE
 NOT EXISTS 
  (SELECT 1 FROM stu_organise z
     WHERE z.Org_Id = '00001' AND z.No_s <= a.No AND z.No_e >= a.No);
但该语句执行效率低下，explain结果如下：------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 | 99450 |  3399K|    76  (28)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI           |                 | 99450 |  3399K|    76  (28)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | STUDENT         | 99699 |  1363K|    58   (6)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| STU_ORGANISE    |     1 |    21 |     0   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | PK_STU_ORGANISE |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------请问高手们，有没有好的方法，让语句效率高一些?在此不胜感激！

Answer 1

这种方式比子查询要快一点
SELECT a.* 
FROM student a ,stu_organise z 
WHERE z.No_s <= a.No AND z.No_e >= a.No and z.Org_Id = '00001'; 

Answer 2

一些优化的技巧
http://blog.csdn.net/bw555/archive/2009/05/19/4200670.aspx

Answer 3

请问bw555 ,如果一个学生会参加多个社团,我要查询z.Org_Id 为'00001'与'00002'的所有学生,但查询结果不能有重复的学生记录,该如何实现?

Answer 4

用INTERSECT取交集
SELECT a.* 
FROM student a ,stu_organise z 
WHERE z.No_s <= a.No AND z.No_e >= a.No and z.Org_Id = '00001'
INTERSECT
SELECT a.* 
FROM student a ,stu_organise z 
WHERE z.No_s <= a.No AND z.No_e >= a.No and z.Org_Id = '00002'

Answer 5

多谢bw555 支持,可能刚才我没有说明白,我要查询z.Org_Id 为'00001'或'00002'或'00003'(还可能有其它社团)的所有学生(只有学生在其中一个社团),这个该如何写呢? 

Answer 6

SELECT DISTINCT a.* 
FROM student a ,stu_organise z 
WHERE z.No_s <= a.No AND z.No_e >= a.No and z.Org_Id IN ('00001','00002','00003'); 

Answer 7

计划的顺序应该是先查询stu_organise，org_id过滤大量数据，然后no关联到student表两表执行顺序相反的话，要扫描student全表，肯定很慢