关于oracle table()函数引起的全表扫描问题

客官°小女子只卖身不卖艺 2022-08-03 11:38 219阅读 0赞

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>