.so_type_id=so_type.so_type_id and so.PRIORITY=status.sts_id and status.table_name='SO'
AND STATUS.column_name ='PRIORITY' AND SO_HANDLE.WORK_AREA_ID= 300101
AND SO.STATE= 'B' AND SO.HALT ='N'
AND CTRL_ASGN.STATE = 'B'
AND CTRL_ASGN.STS = 'D';
该SQL语句执行时间要2分钟左右。
执行计划如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'STATUS'
6 5 INDEX (RANGE SCAN) OF 'PK_STATUS' (UNIQUE)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'CTRL_ASGN'
8 7 INDEX (RANGE SCAN) OF 'CTRL_ASGN_0002'br /> 9 3 TABLE ACCESS (BY INDEX ROWID) OF 'SO'
10 9 INDEX (UNIQUE SCAN) OF 'PK_SO' (UNIQUE)
11 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO_TYPE'
12 11 INDEX (UNIQUE SCAN) OF 'PK_SO_TYPE' (UNIQUE)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'SO_HANDLE'
14 13 INDEX (RANGE SCAN) OF 'PK_SO_HANDLE' (UNIQUE)
我们收集表格信息和结果集的信息:
SQL> select count(*) from CTRL_ASGN;
COUNT(*)
----------
1832469
SQL> select count(*) from status;
COUNT(*)
----------
1718
SQL> select count(*) from so;
COUNT(*)
----------
300296
SQL> select count(*) from so_type;
COUNT(*)
----------
265
SQL> select count(*) from so_handle;
COUNT(*)
----------
1296263
select count(*) from ctrl_asgn where CTRL_ASGN.STATE = 'B' AND CTRL_ASGN.STS = 'D';
COUNT(*)
----------
331490
select count(*) from so where SO.STATE= 'B' AND SO.HALT ='N';
COUNT(*)
----------
361
select count(*) from so_handle where SO_HANDLE.HANDLE_TYPE_ID=1017 and SO_HANDLE.WORK_AREA_ID= 300101;
COUNT(*)
----------
30086
通过对上面这些信息进行分析，我们可以发现这个问题也可以归结为表格之间的连接顺序上面。通过将SO表做柱状图分析后，该SQL语句只需1秒钟即可出来。
Analyze table so compute statistics for all indexed columns;
执行计划变成如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=273 Card=32 Bytes=3936)
1 0 NESTED LOOPS (Cost=273 Card=32 Bytes=3936)
2 1 NESTED LOOPS (Cost=153 Card=30 Bytes=2730)
3 2 HASH JOIN (Cost=33 Card=30 Bytes=2130)
4 3 NESTED LOOPS (Cost=31 Card=30 Bytes=1620)
5 4 TABLE ACCESS (FULL) OF 'STATUS' (Cost=2 Card=1 Bytes=25)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'SO' (Cost=29 Card=59 Bytes=1711)
7 6 INDEX (RANGE SCAN) OF 'SO_0003' (NON-UNIQUE) (Cost=2 Card=59)
8 3 TABLE ACCESS (FULL) OF 'SO_TYPE' (Cost=1 Card=128 Bytes=2176)
9 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO_HANDLE' (Cost=4 Card=280 Bytes=5600)
10 9 INDEX (RANGE SCAN) OF 'PK_SO_HANDLE' (UNIQUE) (Cost=3 Card=280)
11 1 TABLE ACCESS (BY INDEX ROWID) OF 'CTRL_ASGN' (Cost=4 Card=13620 Bytes=435840)
12 11 INDEX (RANGE SCAN) OF 'CTRL_ASGN_0003' (NON-UNIQUE) (Cost=2 Card=13620)
3． Not exists的使用
--停机保号用户数(除欠费)
select 'XJ'||1||'180','停机保号用户数',count(distinct serviceid),1,'200509',groupid from cbq_lch_usage0
where subsidiaryid=1 and subid<>'02' and subid<>'06' and status='7' and
serviceid not in (select serviceorderid from cbq_qf_usage1 where status<>'3' and status <> '8')
group by 'XJ'||1||'180','停机保号用户数',1,'200509',groupid ;
Execution Plan
--------------------------------- |