本文发表在 rolia.net 枫下论坛你可以用下面的sql生成几万条记录:
CREATE SEQUENCE sq1
INCREMENT BY 1
START WITH 1
MAXVALUE 1000
CYCLE
NOCACHE
ORDER;

CREATE SEQUENCE sq2
INCREMENT BY 1
START WITH 1
MAXVALUE 500
CYCLE
NOCACHE
ORDER;

declare
i INTEGER := NULL;
c varchar2(10);
begin
for i in 1..50000 loop
select 'B'||sq2.nextval into c from dual;
insert into t values(sq1.nextval,c);
end loop;
commit;
exception
when others then null;
end;

然后再分析:

我是用的cost based optimizer.
alter session set optimizer_mode=all_rows;
而且对table t 做了分析:
analyze table t estimate statistics;

结果仍是:

SQL> select t2.a, t1.b from t t1, t t2
2 where t1.a=t2.a group by t2.a, t1.b having count(t2.a)>1

997 B497
998 B498

A B
---------- ----------
999 B499
1000 B500

1003 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6271 Card=17678 Bytes=176780)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=6271 Card=17678 Bytes=176780)
3 2 HASH JOIN (Cost=67 Card=2500600 Bytes=25006000)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=8 Card=50006 Bytes=150018)
5 3 TABLE ACCESS (FULL) OF 'T' (Cost=8 Card=50006 Bytes=350042)





SQL> select * from t t1
2 where exists
3 (select 1 from t t2 where t2.a=t1.a having count(1)>1)
4 group by a, b;



A B
---------- ----------
997 B497
998 B498
999 B499
1000 B500

1003 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=2500 Bytes=17500)
1 0 SORT (GROUP BY) (Cost=12 Card=2500 Bytes=17500)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=8 Card=2500 Bytes=17500)
4 2 FILTER
5 4 SORT (AGGREGATE)
6 5 INDEX (RANGE SCAN) OF 'T_I' (NON-UNIQUE) (Cost=1 Card=50 Bytes=150)



1. 用explain plan 之前,必须生成plan_table, 用utlxplan.sql 生成. 在$ORACLE_HOME/rdbms/admin下.
2. 选择cost-based or rule based (Oracle 推荐cost based). 可以在parameter file 或session 或instance级设: 如:
session 级:
alter session set optimizer_mode=all_rows;
3.可以从table plan_table看结果或用: set autotrace on explain 设(我用的这种方式)更多精彩文章及讨论，请光临枫下论坛 rolia.net