使用临时表的sql优化案例二－临时表的统计信息
对于基于成本优化器的数据库来说，准确的统计信息对数据库是否选择最优化的执行方式尤其重要。然而对于临时表，利用这种方式统计信息却有可能造成明显的效率故障。因为临时表平时都是空表，如果对其进行分析，其统计信息记录数为0行，与实际使用时的情况相差甚远。
那么对于临时表，我们建议自己来指定统计信息：
1，估算临时表行数
根据实际业务估算临时表在计算过程中可能产生多少数据，
2，计算临时表block数
简单的方法就是建一个与临时表结构相同的固定表，插入上面估算出的数据，例如估算出2000条，则根据临时表数据生成情况插
入2000条数据。然后分析表得到其block数
insert into xx_test1 select pk_voucher from gl_voucher where pk_manager is null and rownum<=2000;
已创建2000行。
SQL> analyze table xx_test1 compute statistics;
表已分析。
SQL> select NUM_ROWS, BLOCKS,AVG_SPACE from user_tables where table_name='XX_TEST1';
NUM_ROWS BLOCKS AVG_SPACE
---------- ---------- ----------
2000 13 4072
3，将这些信息写入数据库
exec dbms_stats.set_table_stats(ownname=>'SD',tabname=>'GLTMP_VOUCHERPKORA',numrows=>2000,numblks=>13);
其中ownname参数设置为oracle用户，tabname设置为要指定统计信息的表，numrows设置其行数，numblks设置block数。
附：各种统计信息情况下对临时表操作的执行情况比较试验
SQL> analyze table GLTMP_ASSORA delete statistics;
表已分析。
已用时间: 00: 00: 00.00
SQL> analyze table GLTMP_SUBJORA delete statistics;
表已分析。
未选定行
已用时间: 00: 00: 04.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=376 Card=1 Bytes=165
)
1 0 SORT (GROUP BY) (Cost=376 Card=1 Bytes=165)
2 1 HASH JOIN (Cost=73 Card=23312 Bytes=3846480)
3 2 NESTED LOOPS (Cost=65 Card=1650 Bytes=235950)
4 3 NESTED LOOPS (Cost=19 Card=153 Bytes=18513)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_VOUCHER' (Cos
t=3 Card=9 Bytes=450)
6 5 INDEX (RANGE SCAN) OF 'I_GL_VOUCHER_003' (NON-UN
IQUE) (Cost=3 Card=28)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_DETAIL' (Cost
=2 Card=18 Bytes=1278)
8 7 INDEX (RANGE SCAN) OF 'I_GL_DETAIL_1' (NON-UNIQU
E) (Cost=2 Card=25)
9 3 INDEX (RANGE SCAN) OF 'IND_GLTMP_SUBJORA' (NON-UNIQU
E) (Cost=1 Card=11 Bytes=242)
10 2 INDEX (FAST FULL SCAN) OF 'IND_GLTMP_ASSORA' (NON-UNIQ
UE) (Cost=4 Card=8168 Bytes=179696)
SQL>analyze table GLTMP_ASSORA compute statistics
SQL>analyze table GLTMP_SUBJORA compute statistics
SQL> /
未选定行
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=165)
1 0 SORT (GROUP BY) (Cost=8 Card=1 Bytes=165)
2 1 NESTED LOOPS (Cost=5 Card=1 Bytes=165)
3 2 NESTED LOOPS (Cost=4 Card=1 Bytes=115)
4 3 MERGE JOIN (CARTESIAN) (Cost=3 Card=1 Bytes=44)
5 4 INDEX (FULL SCAN) OF 'IND_GLTMP_SUBJORA' (NON-UNIQ
UE)
6 4 BUFFER (SORT) (Cost=1 Card=1 Bytes=22)
7 6 INDEX (FULL SCAN) OF 'IND_GLTMP_ASSORA' (NON-UNI
QUE)
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'GL_DETAIL' (Cost=4
Card=1 Bytes=71)
9 8 AND-EQUAL
10 9 INDEX (RANGE SCAN) OF 'I_GL_DETAIL_2' (NON-UNIQU
E)
11 9 INDEX (RANGE SCAN) OF 'I_GL_DETAIL_3' (NON-UNIQU
E) (Cost=2 Card=449)
12 2 TABLE ACCESS (BY INDEX ROWID) OF 'GL_VOUCHER' (Cost=2
Card=1 Bytes=50)
13 12 INDEX (UNIQUE SCAN) OF 'PK_GL_VOUCHER' (UNIQUE) (Cos
t=1 Card=14137)
SQL> exec dbms_stats.set_table_stats(ownname=>'SD',tabname=>'GLTMP_SUBJORA',numrows=>4000,numblks=>15);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.06
SQL> exec dbms_stats.set_table_stats(ownname=>'SD',tabname=>'GLTMP_SUBJORA',numrows=>200,numblks=>2);
PL/SQL 过程已成功完成。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=165)
1 0 SORT (GROUP BY) (Cost=22 Card=1 Bytes=165)
2 1 NESTED LOOPS (Cost=19 Card=1 Bytes=165)
3 2 NESTED LOOPS (Cost=18 Card=1 Bytes=143)
4 3 NESTED LOOPS (Cost=11 Card=23 Bytes=2783)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_VOUCHER' (Cos
t=3 Card=9 Bytes=450)
6 5 INDEX (RANGE SCAN) OF 'I_GL_VOUCHER_003' (NON-UN
IQUE) (Cost=3 Card=28)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_DETAIL' (Cost
=2 Card=3 Bytes=213)
8 7 INDEX (RANGE SCAN) OF 'I_GL_DETAIL_1' (NON-UNIQU
E) (Cost=2 Card=4)
9 3 INDEX (RANGE SCAN) OF 'IND_GLTMP_SUBJORA' (NON-UNIQU
E) (Cost=1 Card=1 Bytes=22)
10 2 INDEX (RANGE SCAN) OF 'IND_GLTMP_ASSORA' (NON-UNIQUE)
(Cost=1 Card=1 Bytes=22)
SQL> create table xx_test1 as select * from gltmp_voucherpkora ;
表已创建。
已用时间: 00: 00: 00.09
SQL> set autotrace off
SQL> desc xx_test1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
PK_VOUCHER VARCHAR2(20)
SQL> insert into xx_test1 select pk_voucher from gl_voucher where pk_manager is null and rownum<=2000;
已创建2000行。
已用时间: 00: 00: 00.01
SQL> analyze table xx_test1 compute statistics;
表已分析。
已用时间: 00: 00: 00.04
SQL> select NUM_ROWS, BLOCKS,AVG_SPACE from user_tables where table_name='XX_TEST1';
NUM_ROWS BLOCKS AVG_SPACE
---------- ---------- ----------
2000 13 4072
已用时间: 00: 00: 00.00
SQL> select NUM_ROWS, BLOCKS,AVG_SPACE from user_tables where table_name='GLTMP_VOUCHERPKORA';
NUM_ROWS BLOCKS AVG_SPACE
---------- ---------- ----------
已用时间: 00: 00: 00.01
SQL> insert into GLTMP_VOUCHERPKORA select pk_voucher from gl_voucher where pk_manager is null and rownum<=2000;
已创建2000行。
已用时间: 00: 00: 00.01
SQL> ANALYZE TABLE GLTMP_VOUCHERPKORA COMPUTE STATISTICS;
表已分析。
已用时间: 00: 00: 00.01
SQL> select NUM_ROWS, BLOCKS,AVG_SPACE from user_tables where table_name='GLTMP_VOUCHERPKORA';
NUM_ROWS BLOCKS AVG_SPACE
---------- ---------- ----------
0 0 0
已用时间: 00: 00: 00.00
SQL> exec dbms_stats.set_table_stats(ownname=>'SD',tabname=>'GLTMP_VOUCHERPKORA',numrows=>2000,numblks=>13);
PL/SQL 过程已成功完成。
SQL> select NUM_ROWS, BLOCKS,AVG_SPACE from user_tables where table_name='GLTMP_VOUCHERPKORA';
NUM_ROWS BLOCKS AVG_SPACE
---------- ---------- ----------
2000 13 0
SQL> SELECT gl_voucher.pk_corp, gl_voucher.YEAR, gl_voucher.period,
2 gl_detail.pk_accsubj, gl_detail.pk_currtype, gl_detail.assid,
3 gl_voucher.free1, SUM (gl_detail.debitquantity) debitquantitysum,
4 SUM (gl_detail.creditquantity) creditquantitysum,
5 SUM (gl_detail.debitamount) debitamountsum,
6 SUM (gl_detail.creditamount) creditamountsum,
7 SUM (gl_detail.fracdebitamount) fracdebitamountsum,
8 SUM (gl_detail.fraccreditamount) fraccreditamountsum,
9 SUM (gl_detail.localdebitamount) localdebitamountsum,
10 SUM (gl_detail.localcreditamount) localcreditamountsum
11 FROM gl_detail gl_detail, gl_voucher gl_voucher, bd_accsubj subj
12 WHERE gl_detail.pk_voucher = gl_voucher.pk_voucher
13 AND gl_detail.pk_accsubj = subj.pk_accsubj
14 AND gl_voucher.pk_manager IS NULL
15 AND gl_voucher.discardflag = 'N'
16 AND gl_voucher.dr = 0
17 AND gl_voucher.pk_voucher IN (SELECT pk_voucher
18 FROM gltmp_voucherpkora)
19 GROUP BY gl_voucher.pk_corp,
20 gl_voucher.YEAR,
21 gl_voucher.period,
22 gl_detail.pk_accsubj,
23 gl_detail.pk_currtype,
24 gl_detail.assid,
25 gl_voucher.free1
26 ORDER BY gl_voucher.pk_corp,
27 gl_voucher.YEAR,
28 gl_voucher.period,
29 gl_detail.pk_accsubj,
30 gl_detail.pk_currtype,
31 gl_detail.assid,
32 gl_voucher.free1
33 ;
已选择2554行。
已用时间: 00: 00: 03.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=996 Card=5755 Bytes=
995615)
1 0 SORT (GROUP BY) (Cost=996 Card=5755 Bytes=995615)
2 1 NESTED LOOPS (Cost=846 Card=5755 Bytes=995615)
3 2 NESTED LOOPS (Cost=845 Card=5755 Bytes=874760)
4 3 NESTED LOOPS (Cost=23 Card=457 Bytes=27877)
5 4 SORT (UNIQUE)
6 5 INDEX (FULL SCAN) OF 'IND_GLTMP_VOUCHERPKORA' (NON-UNIQUE)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_VOUCHER' (Cost=2 Card=1 Bytes=49)
8 7 INDEX (RANGE SCAN) OF 'PK_GL_VOUCHER' (UNIQUE) (Cost=1 Card=1)
9 3 TABLE ACCESS (BY INDEX ROWID) OF 'GL_DETAIL' (Cost=2 Card=13 Bytes=1183)
10 9 INDEX (RANGE SCAN) OF 'I_GL_DETAIL_1' (NON-UNIQUE)(Cost=2 Card=25)
11 2 INDEX (UNIQUE SCAN) OF 'PK_BD_ACCSUBJ' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19482 consistent gets
769 physical reads
0 redo size
184422 bytes sent via SQL*Net to client
2365 bytes received via SQL*Net from client
172 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2554 rows processed
SQL> analyze table GLTMP_VOUCHERPKORa delete statistics;
表已分析。
已用时间: 00: 00: 00.04
SQL> alter system flush shared_pool;
系统已更改。
SQL> /
已选择2554行。
已用时间: 00: 00: 05.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4053 Card=23502 Byte
s=4065846)
1 0 SORT (GROUP BY) (Cost=4053 Card=23502 Bytes=4065846)
2 1 NESTED LOOPS (Cost=3444 Card=23502 Bytes=4065846)
3 2 NESTED LOOPS (Cost=3443 Card=23502 Bytes=3572304)
4 3 NESTED LOOPS (Cost=81 Card=1868 Bytes=113948)
5 4 SORT (UNIQUE)
6 5 INDEX (FAST FULL SCAN) OF 'IND_GLTMP_VOUCHERPKOR
A' (NON-UNIQUE) (Cost=4 Card=8168 Bytes=98016)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_VOUCHER' (Cos
t=2 Card=1 Bytes=49)
8 7 INDEX (RANGE SCAN) OF 'PK_GL_VOUCHER' (UNIQUE) (
Cost=1 Card=1)
9 3 TABLE ACCESS (BY INDEX ROWID) OF 'GL_DETAIL' (Cost=2
Card=13 Bytes=1183)
10 9 INDEX (RANGE SCAN) OF 'I_GL_DETAIL_1' (NON-UNIQUE)
(Cost=2 Card=25)
11 2 INDEX (UNIQUE SCAN) OF 'PK_BD_ACCSUBJ' (UNIQUE)
Statistics
----------------------------------------------------------
2435 recursive calls
0 db block gets
20018 consistent gets
7 physical reads
0 redo size
184422 bytes sent via SQL*Net to client
2365 bytes received via SQL*Net from client
172 SQL*Net roundtrips to/from client
37 sorts (memory)
0 sorts (disk)
2554 rows processed
ORACLE CBO 将估算GLTMP_VOUCHERPKORa的信息，可以看到执行计划同上但因为此时的成本为估算成本所以显示的成本不同