这样的sql查询如何优化

Question

 如题:create table tb
(
  taskid              number(20) not null,
  basictotaldealtimes number(20),
  basictaskid         number(20)
);--模拟数据
insert into tb (taskid, basictotaldealtimes, basictaskid)values (1, 1, 1);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (2, 1, 1);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (3, 1, 1);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (4, 1, 1);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (5, 1, 1);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (6, 2, 1);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (7, 2, 1);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (8, 3, 2);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (9, 3, 2);
insert into tb (taskid, basictotaldealtimes, basictaskid)values (10, 4, 2);需求: 根据taskid,找到basictaskid;然后再根据basictaskid找它下面所有的taskid,并且basictotaldealtimes次数要相等.--1.输入taskid = 1时
SQL> select t.*
  2    from tb t
  3   where exists (select 1
  4                   from tb a
  5                  where a.taskid = 1
  6                    and a.basictaskid = t.basictaskid
  7                    and a.basictotaldealtimes = t.basictotaldealtimes)
  8   order by t.taskid;
 
               TASKID   BASICTOTALDEALTIMES           BASICTASKID
--------------------- --------------------- ---------------------
                    1                     1                     1
                    2                     1                     1
                    3                     1                     1
                    4                     1                     1
                    5                     1                     1--2.输入taskid = 6时   
SQL> select t.*
  2    from tb t
  3   where exists (select 1
  4                   from tb a
  5                  where a.taskid = 6
  6                    and a.basictaskid = t.basictaskid
  7                    and a.basictotaldealtimes = t.basictotaldealtimes)
  8   order by t.taskid;
 
               TASKID   BASICTOTALDEALTIMES           BASICTASKID
--------------------- --------------------- ---------------------
                    6                     2                     1
                    7                     2                     1                 --3.输入taskid = 8时 
SQL> select t.*
  2    from tb t
  3   where exists (select 1
  4                   from tb a
  5                  where a.taskid = 8
  6                    and a.basictaskid = t.basictaskid
  7                    and a.basictotaldealtimes = t.basictotaldealtimes)
  8   order by t.taskid;
 
               TASKID   BASICTOTALDEALTIMES           BASICTASKID
--------------------- --------------------- ---------------------
                    8                     3                     2
                    9                     3                     2tb表是一个百万级的大表,以上只是10条模拟数据. taskid、basictaskid、basictotaldealtimes是3个主要字段,并且已建复合索引.我的问题是:
--1.exists
select t.*
  from tb t
 where exists (select 1
                 from tb a
                where a.taskid = n
                  and a.basictaskid = t.basictaskid
                  and a.basictotaldealtimes = t.basictotaldealtimes)
 order by t.taskid;
 
或
--2.自连接
select t.*
  from tb t,tb a
 where t.taskid = n
   and t.basictaskid = a.basictaskid
   and t.basictotaldealtimes = a.basictotaldealtimes
 order by t.taskid;
 
两种情况:表tb均被访问了2次，如何使表tb只被访问1次呢？能使用其他函数解决吗？(比如lag之类的分析函数)

Answer 1

这个问题不好整，最近也在看优化方面的东西，说白了，单纯的采用一些理论的优化方法是行不通的，就算你通过hints改变Oracle的执行计划，同样不见得高效。
说到底，你还是要彻底搞清楚Oracle底层是如何进行优化的，而要搞清楚Oracle底层东西，我看只有开发Oracle数据库的那几个NB人才真正能透彻哦。
慢慢来吧，我们也只有摸到石头过河，乱撞！

Answer 2

楼主，我前几天也优化了类似的语句，
参考下，希望对你有所有帮助http://www.itpub.net/thread-1338696-1-1.html

Answer 3

因为下面有个order by排序，所以查询还是很慢的！我自己也考虑过建一张临时表，存放：
select 1
 from tb a
where a.taskid = 6
  and a.basictaskid = t.basictaskid
  and a.basictotaldealtimes = t.basictotaldealtimes的结果，但是效率不明显。期待高手们帮忙解决下！

Answer 4

大表自关联并不可怕，关键是合理地使用索引，使得每一步查询的结果集尽量小。比如说在加了这两个索引以后：
alter table TB add constraint PK_TB primary key (TASKID) using index tablespace USERS;
create index IDX_TB on TB (BASICTASKID, BASICTOTALDEALTIMES) tablespace USERS;执行计划变得很好看：
SELECT STATEMENT, GOAL = ALL_ROWS 3 1 78
 SORT ORDER BY 3 1 78
  NESTED LOOPS 2 1 78
   TABLE ACCESS BY INDEX ROWID user TB 1 1 39
    INDEX UNIQUE SCAN user PK_TB 0 1
   TABLE ACCESS BY INDEX ROWID user TB 1 1 39
    INDEX RANGE SCAN user IDX_TB 0 2以我这个加200 0000万数据花了1004秒的机器来运行上面的sql语句，也只不过花了0.21秒的时间。