SQL> select bd_accsubj.pk_accsubj,
2 bd_accsubj.subjcode,
3 bd_accsubj.subjname,
4 bd_accsubj.endflag
5 from ufnc5610.bd_accsubj
6 where bd_accsubj.pk_glorgbook = '0001AI1000000003PENU'
7 and sealflag is null
9 and bd_accsubj.endflag = 'Y'
10 and exists
11 (select subj1.Pk_accsubj
12 from ufnc5610.bd_accsubj subj1, ufnc5610.gl_verifyObj
13 where gl_verifyObj.Pk_accsubj = subj1.Pk_accsubj
14 and (gl_verifyObj.pk_glorgbook = '0001AI1000000003PENU' and
15 bd_accsubj.subjcode like subj1.subjcode || '%' and
16 gl_verifyObj.userFlag = 'Y'
17 )
18 );
SQL> /
已选择86行。
执行计划
----------------------------------------------------------
Plan hash value: 3518202699
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 660 (0)| 00:00:08 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | BD_ACCSUBJ | 591 | 40188 | 69 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_BD_ACCSUBJ_3 | 746 | | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 83 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| GL_VERIFYOBJ | 8 | 424 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_GL_VERIFYOBJ | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| BD_ACCSUBJ | 1 | 30 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_BD_ACCSUBJ | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "UFNC5610"."GL_VERIFYOBJ"
"GL_VERIFYOBJ","UFNC5610"."BD_ACCSUBJ" "SUBJ1" WHERE
"GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ" AND :B1 LIKE "SUBJ1"."SUBJCODE"||'%'
AND "GL_VERIFYOBJ"."PK_GLORGBOOK"='0001AI1000000003PENU' AND
"GL_VERIFYOBJ"."USERFLAG"='Y'))
2 - filter("SEALFLAG" IS NULL AND "BD_ACCSUBJ"."ENDFLAG"='Y')
3 - access("BD_ACCSUBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
5 - filter("GL_VERIFYOBJ"."USERFLAG"='Y')
6 - access("GL_VERIFYOBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
7 - filter(:B1 LIKE "SUBJ1"."SUBJCODE"||'%')
8 - access("GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
16806 consistent gets
0 physical reads
0 redo size
5242 bytes sent via SQL*Net to client
278 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86 rows processed
like 连接无法被unnest，修改为表连接的方式：
SQL> select bd_accsubj.pk_accsubj,
2 bd_accsubj.subjcode,
3 bd_accsubj.subjname,
4 bd_accsubj.endflag
5 from ufnc5610.bd_accsubj , (select distinct subj1.subjcode
6 from ufnc5610.bd_accsubj subj1, ufnc5610.gl_verifyObj
7 where gl_verifyObj.Pk_accsubj = subj1.Pk_accsubj
8 and (gl_verifyObj.pk_glorgbook = '0001AI1000000003PENU' and
9 gl_verifyObj.userFlag = 'Y'
10 )
11 ) c
12 where bd_accsubj.pk_glorgbook = '0001AI1000000003PENU'
13 and sealflag is null
14 and bd_accsubj.endflag = 'Y'
15 and bd_accsubj.subjcode like c.subjcode || '%';
已选择86行。
执行计划
----------------------------------------------------------
Plan hash value: 2564690268
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 236 | 21240 | 63 (2)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | BD_ACCSUBJ | 30 | 2040 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 236 | 21240 | 63 (2)| 00:00:01 |
| 3 | VIEW | | 8 | 176 | 8 (13)| 00:00:01 |
| 4 | SORT UNIQUE | | 8 | 664 | 8 (13)| 00:00:01 |
| 5 | NESTED LOOPS | | 8 | 664 | 7 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| GL_VERIFYOBJ | 8 | 424 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_GL_VERIFYOBJ | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| BD_ACCSUBJ | 1 | 30 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_BD_ACCSUBJ | 1 | | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | I_BD_ACCSUBJ_2 | 7 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEALFLAG" IS NULL AND "BD_ACCSUBJ"."ENDFLAG"='Y')
6 - filter("GL_VERIFYOBJ"."USERFLAG"='Y')
7 - access("GL_VERIFYOBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
9 - access("GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ")
10 - access("BD_ACCSUBJ"."SUBJCODE" LIKE "C"."SUBJCODE"||'%' AND
"BD_ACCSUBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
filter("BD_ACCSUBJ"."PK_GLORGBOOK"='0001AI1000000003PENU' AND
"BD_ACCSUBJ"."SUBJCODE" LIKE "C"."SUBJCODE"||'%')
统计信息
----------------------------------------------------------
19 recursive calls
0 db block gets
454 consistent gets
86 physical reads
0 redo size
5242 bytes sent via SQL*Net to client
278 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
86 rows processed