oracle table()在某些场景下，为开发提供了便利之处。但如下sql的会造成全表扫描，导致系统负载变大
SQL> SELECT *
2 FROM (select a.bill_code, sum(a.piece_num)
3 from TB_WAYBILL a
4 group by a.bill_code) c,
5 TABLE(FN_STR_TO_TBL('200013011384,200013011409,1111', ',')) B
6 WHERE c.BILL_CODE = B.CODE;
当出现oracle先分组统计在直接关联table()函数，会导致聚合函数所统计的列，所在表走不上索引。其它情况下关联table()出现全表扫描的情况暂未发现，如有后期将添加。欢迎指正探讨。
如下10053文件中给出的信息
----------------------------------------------trace文件开始（注这里篇幅有限，并没有给出全部）
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (TABLE "KOKBF$" ROWS=3.000000 ) */ "C"."BILL_CODE" "BILL_CODE","C"."SUM(A.PIECE_NUM)" "SUM(A.PIECE_NUM)",SYS_OP_ATG(VALUE(KOKBF$),1,2,2) "ID",SYS_OP_ATG(VALUE(KOKBF$),2,3,2) "CODE" FROM (SELECT "A"."BILL_CODE" "BILL_CODE",SUM("A"."PIECE_NUM") "SUM(A.PIECE_NUM)" FROM "ZDADMIN"."TB_WAYBILL" "A" GROUP BY "A"."BILL_CODE") "C",TABLE("ZDADMIN"."FN_STR_TO_TBL"('200013011384,200013011409,1111',',')) "KOKBF$" WHERE "C"."BILL_CODE"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2)
kkoqbc: optimizing query block SEL$2 (#0)
:
call(in-use=5656, alloc=314384), compile(in-use=84240, alloc=87648), execution(in-use=12384, alloc=16200)
kkoqbc-subheap (create addr=0x2ab39e63de28)
****************
QUERY BLOCK TEXT
****************
select a.bill_code, sum(a.piece_num)
from TB_WAYBILL a
group by a.bill_code
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$2 nbfros=1 flg=0
fro(0): flg=0 objn=80120 hint_alias="A"@"SEL$2"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2657 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 64)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TB_WAYBILL Alias: A (Using composite stats)
#Rows: 1889543 #Blks: 61170 AvgRowLen: 217.00
Index Stats::
Index: I_WAYBILL_1 Col#: 6
LVLS: 2 #LB: 7009 #DK: 397 LB/K: 17.00 DB/K: 614.00 CLUF: 243976.00
Index: PK_TB_WAYBILL Col#: 1
LVLS: 2 #LB: 8238 #DK: 1868565 LB/K: 1.00 DB/K: 1.00 CLUF: 1418270.00
Access path analysis for TB_WAYBILL
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TB_WAYBILL[A]
Table: TB_WAYBILL Alias: A
Card: Original: 1889543.000000 Rounded: 1889543 Computed: 1889543.00 Non Adjusted: 1889543.00
Access Path: TableScan
Cost: 11019.11 Resp: 11019.11 Degree: 0
Cost_io: 10993.00 Cost_cpu: 832422515
Resp_io: 10993.00 Resp_cpu: 832422515
Best:: AccessPath: TableScan
Cost: 11019.11 Degree: 1 Resp: 11019.11 Card: 1889543.00 Bytes: 0
Grouping column cardinality [ BILL_CODE] 1889543
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: TB_WAYBILL[A]#0
GROUP BY sort
GROUP BY adjustment factor: 1.000000
GROUP BY cardinality: 1889543.000000, TABLE cardinality: 1889543.000000
SORT ressource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073735680
Degree: 1
Blocks to Sort: 6709 Row size: 29 Total Rows: 1889543
Initial runs: 2 Merge passes: 1 IO Cost / pass: 3636
Total IO sort cost: 10345 Total CPU sort cost: 1972079915
Total Temp space used: 53208000
***********************
Best so far: Table#: 0 cost: 21425.9592 card: 1889543.0000 bytes: 32122231
***********************
****** Recost for ORDER BY (using index) ************
Access path analysis for TB_WAYBILL
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TB_WAYBILL[A]
Table: TB_WAYBILL Alias: A
Card: Original: 1889543.000000 Rounded: 1889543 Computed: 1889543.00 Non Adjusted: 1889543.00
Access Path: TableScan
Cost: 11019.11 Resp: 11019.11 Degree: 0
Cost_io: 10993.00 Cost_cpu: 832422515
Resp_io: 10993.00 Resp_cpu: 832422515
Access Path: index (FullScan)
Index: PK_TB_WAYBILL
resc_io: 1426510.00 resc_cpu: 10924917024
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 142685.26 Resp: 142685.26 Degree: 1
Best:: AccessPath: IndexRange
Index: PK_TB_WAYBILL
Cost: 142685.26 Degree: 1 Resp: 142685.26 Card: 1889543.00 Bytes: 17
Join order[1]: TB_WAYBILL[A]#0
Join order aborted: cost > best plan cost
--------------省略部分信息
注：上文里的cbo认为Card: Original: 1889543.000000 Rounded: 1889543 此处rounded输出行也是1889543（这个地方没有搞明白，实际输出只有两行）所以在关联前认为走全表扫描cost更少，下文里cbo将a表的分组后结果做为一个独立视图（下文中的$_subquery$_001 Alias: C ）去关联 table()函数
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: KOKBF$ Alias: KOKBF$ (NOT ANALYZED)
#Rows: 8168 #Blks: 100 AvgRowLen: 100.00
***********************
Table Stats::
Table: from$_subquery$_001 Alias: C (NOT ANALYZED)
#Rows: 0 #Blks: 0 AvgRowLen: 0.00
Access path analysis for from$_subquery$_001
Access path analysis for KOKBF$
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for KOKBF$[KOKBF$]
Table: KOKBF$ Alias: KOKBF$
Card: Original: 8168.000000 >> Single Tab Card adjusted from:8168.000000 to:3.000000
Rounded: 3 Computed: 3.00 Non Adjusted: 8168.00
Access Path: TableScan
Cost: 20.06 Resp: 20.06 Degree: 0
Cost_io: 20.00 Cost_cpu: 1774044
Resp_io: 20.00 Resp_cpu: 1774044
Best:: AccessPath: TableScan
Cost: 20.06 Degree: 1 Resp: 20.06 Card: 3.00 Bytes: 0
----------------------------------------------trace文件结束（注这里篇幅有限，并没有给出全部）
如下演示实际强制表a使用使用索引以验证上面trace文件里的cost
SQL> set autotrace trace exp stat
SQL> set linesize 500
SQL> SELECT *
2 FROM (select /*+index(a,PK_TB_WAYBILL)+*/ a.bill_code, sum(a.piece_num)
3 from TB_WAYBILL a
4 group by a.bill_code) c,
5 TABLE(FN_STR_TO_TBL('200013011384,200013011409,1111', ',')) B
6 WHERE c.BILL_CODE = B.CODE;
Execution Plan
----------------------------------------------------------
Plan hash value: 1808457990
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 215K| 148K (1)| 00:29:37 | | |
| 1 | MERGE JOIN | | 8168 | 215K| 148K (1)| 00:29:37 | | |
| 2 | SORT JOIN | | 1986K| 47M| 147K (1)| 00:29:36 | | |
| 3 | VIEW | | 1986K| 47M| 147K (1)| 00:29:36 | | |
| 4 | HASH GROUP BY | | 1986K| 32M| 147K (1)| 00:29:36 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_WAYBILL | 1986K| 32M| 147K (1)| 00:29:36 | ROWID | ROWID |
| 6 | INDEX FULL SCAN | PK_TB_WAYBILL | 1986K| | 876 (1)| 00:00:11 | | |
|* 7 | SORT JOIN | | 8168 | 16336 | 21 (5)| 00:00:01 | | |
| 8 | COLLECTION ITERATOR PICKLER FETCH | FN_STR_TO_TBL | 8168 | 16336 | 20 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("C"."BILL_CODE"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
filter("C"."BILL_CODE"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
Statistics
----------------------------------------------------------
26 recursive calls
72 db block gets
1525827 consistent gets
1705 physical reads
72 redo size
847 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
-----------------------------------------------------------------------------------------本案例给出的优化建议如下
select distinct sid from v$mystat;
select b.*
from V$session a, v$process b
where a.sid = ??
and a.paddr = b.addr;
alter session set events '10053 trace name context forever , level 1';
SELECT *
FROM (select a.bill_code, sum(a.piece_num)
from TB_WAYBILL a
group by a.bill_code) c
WHERE exists
(select 1
from TABLE(FN_STR_TO_TBL('200013011384,200013011409,1111', ',')) B
where c.bill_code = b.code);
alter session set events '10053 trace name context off';
将上面对应的trace文件找到，会发现cbo最终转换后的sql如下
----------------------------------------------trace文件开始（注这里篇幅有限，并没有给出全部）
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."BILL_CODE" "BILL_CODE",SUM("A"."PIECE_NUM") "SUM(A.PIECE_NUM)" FROM TABLE("ZDADMIN"."FN_STR_TO_TBL"('200013011384,200013011409,1111',',')) "KOKBF$","ZDADMIN"."TB_WAYBILL" "A" WHERE "A"."BILL_CODE"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) GROUP BY "A"."BILL_CODE"
---
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TB_WAYBILL Alias: A (Using composite stats)
#Rows: 1889543 #Blks: 61170 AvgRowLen: 217.00
Index Stats::
Index: I_WAYBILL_1 Col#: 6
LVLS: 2 #LB: 7009 #DK: 397 LB/K: 17.00 DB/K: 614.00 CLUF: 243976.00
Index: PK_TB_WAYBILL Col#: 1
LVLS: 2 #LB: 8238 #DK: 1868565 LB/K: 1.00 DB/K: 1.00 CLUF: 1418270.00
***********************
Table Stats::
Table: KOKBF$ Alias: KOKBF$ (NOT ANALYZED)
#Rows: 8168 #Blks: 100 AvgRowLen: 100.00
Access path analysis for KOKBF$
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for KOKBF$[KOKBF$]
Table: KOKBF$ Alias: KOKBF$
Card: Original: 8168.000000 Rounded: 8168 Computed: 8168.00 Non Adjusted: 8168.00
Access Path: TableScan
Cost: 20.06 Resp: 20.06 Degree: 0
Cost_io: 20.00 Cost_cpu: 1937344
Resp_io: 20.00 Resp_cpu: 1937344
Best:: AccessPath: TableScan
Cost: 20.06 Degree: 1 Resp: 20.06 Card: 8168.00 Bytes: 0
Access path analysis for TB_WAYBILL
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TB_WAYBILL[A]
Table: TB_WAYBILL Alias: A
Card: Original: 1889543.000000 Rounded: 1889543 Computed: 1889543.00 Non Adjusted: 1889543.00
Access Path: TableScan
Cost: 11019.11 Resp: 11019.11 Degree: 0
Cost_io: 10993.00 Cost_cpu: 832422515
Resp_io: 10993.00 Resp_cpu: 832422515
Best:: AccessPath: TableScan
Cost: 11019.11 Degree: 1 Resp: 11019.11 Card: 1889543.00 Bytes: 0
Grouping column cardinality [ BILL_CODE] 1889543
注：可能还有由于上面Card: Original: 1889543.000000 Rounded: 1889543 这个rounded输出记录数的问题，在关联前cbo依旧认定全表扫描的方式最优
Join order[1]: TB_WAYBILL[A]#0 KOKBF$[KOKBF$]#1
Join order aborted: cost > best plan cost
***********************
Join order[2]: KOKBF$[KOKBF$]#1 TB_WAYBILL[A]#0
SORT ressource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073735680
Degree: 1
Blocks to Sort: 13 Row size: 13 Total Rows: 8168
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 36666663
Total Temp space used: 0
***************
Now joining: TB_WAYBILL[A]#0
***************
NL Join
Outer table: Card: 255.25 Cost: 21.21 Resp: 21.21 Degree: 1 Bytes: 2
Access path analysis for TB_WAYBILL
Inner table: TB_WAYBILL Alias: A
Access Path: TableScan
NL Join: Cost: 2809508.68 Resp: 2809508.68 Degree: 1
Cost_io: 2802850.00 Cost_cpu: 212306345281
Resp_io: 2802850.00 Resp_cpu: 212306345281
OPTIMIZER PERCENT INDEX CACHING = 85
Access Path: index (UniqueScan)
Index: PK_TB_WAYBILL
resc_io: 1.00 resc_cpu: 8381
ix_sel: 0.000001 ix_sel_with_filters: 0.000001
NL Join : Cost: 46.72 Resp: 46.72 Degree: 1
Cost_io: 45.50 Cost_cpu: 38817733
Resp_io: 45.50 Resp_cpu: 38817733
OPTIMIZER PERCENT INDEX CACHING = 85
Access Path: index (AllEqUnique)
Index: PK_TB_WAYBILL
resc_io: 1.00 resc_cpu: 8381
ix_sel: 0.000001 ix_sel_with_filters: 0.000001
NL Join : Cost: 46.72 Resp: 46.72 Degree: 1
Cost_io: 45.50 Cost_cpu: 38817733
Resp_io: 45.50 Resp_cpu: 38817733
注：在关联后，cbo在此处认定表a走索引的cost更少
Best NL cost: 46.72
resc: 46.72 resc_io: 45.50 resc_cpu: 38817733
resp: 46.72 resp_io: 45.50 resc_cpu: 38817733
Join Card: 255.250000 = = outer (255.250000) * inner (1889543.000000) * sel (0.000001)
Join Card - Rounded: 255 Computed: 255.25
Grouping column cardinality [ BILL_CODE] 1
Outer table: KOKBF$ Alias: KOKBF$
resc: 21.21 card 255.25 bytes: 2 deg: 1 resp: 21.21
Inner table: TB_WAYBILL Alias: A
resc: 11019.11 card: 1889543.00 bytes: 17 deg: 1 resp: 11019.11
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073735680
Degree: 1
Blocks to Sort: 1 Row size: 13 Total Rows: 255
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 31975993
Total Temp space used: 0
SORT ressource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073735680
Degree: 1
Blocks to Sort: 6709 Row size: 29 Total Rows: 1889543
Initial runs: 2 Merge passes: 1 IO Cost / pass: 3636
Total IO sort cost: 10345 Total CPU sort cost: 1972079915
Total Temp space used: 106406000
SM join: Resc: 21448.17 Resp: 21448.17 [multiMatchCost=0.00]
SM Join
SM cost: 21448.17
resc: 21448.17 resc_io: 21358.00 resc_cpu: 2875082430
resp: 21448.17 resp_io: 21358.00 resp_cpu: 2875082430
Outer table: KOKBF$ Alias: KOKBF$
resc: 21.21 card 255.25 bytes: 2 deg: 1 resp: 21.21
Inner table: TB_WAYBILL Alias: A
resc: 11019.11 card: 1889543.00 bytes: 17 deg: 1 resp: 11019.11
using dmeth: 2 #groups: 1
Cost per ptn: 6.43 #ptns: 1
hash_area: 256 (max=262143) buildfrag: 1 probefrag: 6690 ppasses: 1
Hash join: Resc: 11046.75 Resp: 11046.75 [multiMatchCost=0.00]
HA Join
HA cost: 11046.75
resc: 11046.75 resc_io: 11013.00 resc_cpu: 1075961145
resp: 11046.75 resp_io: 11013.00 resp_cpu: 1075961145
GROUP BY sort
GROUP BY adjustment factor: 1.000000
GROUP BY cardinality: 1.000000, TABLE cardinality: 255.000000
SORT ressource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073735680
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 255
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 31975993
Total Temp space used: 0
Plan cardinality mismatch: best card= 1.00000000000 curr card= 255.25000000000
Best:: JoinMethod: NestedLoop
Cost: 47.72 Degree: 1 Resp: 47.72 Card: 255.25 Bytes: 19
***********************
Best so far: Table#: 1 cost: 21.2108 card: 255.2500 bytes: 510
Table#: 0 cost: 47.7203 card: 255.2500 bytes: 4845
----------------------------------------------trace文件结束（注这里篇幅有限，并没有给出全部）
被废止的第一种jion方式实际连接方式如下
SQL> SET AUTOTRACE TRACE EXP
SQL> SELECT *
2 FROM (select /*+index(a,PK_TB_WAYBILL) leading(a)+*/ a.bill_code, sum(a.piece_num)
3 from TB_WAYBILL a
4 group by a.bill_code) c
5 WHERE exists
6 (select 1
7 from TABLE(FN_STR_TO_TBL('200013011384,200013011409,1111', ',')) B
8 where c.bill_code = b.code);
Execution Plan
----------------------------------------------------------
Plan hash value: 642200668
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 148K (1)| 00:29:37 | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 19 | 148K (1)| 00:29:37 | | |
| 2 | MERGE JOIN SEMI | | 1 | 19 | 148K (1)| 00:29:37 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_WAYBILL | 1986K| 32M| 147K (1)| 00:29:36 | ROWID | ROWID |
| 4 | INDEX FULL SCAN | PK_TB_WAYBILL | 1986K| | 876 (1)| 00:00:11 | | |
|* 5 | SORT UNIQUE | | 8168 | 16336 | 21 (5)| 00:00:01 | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| FN_STR_TO_TBL | 8168 | 16336 | 20 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."BILL_CODE"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
filter("A"."BILL_CODE"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
SQL>
1238
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
