SQL> select bd_accsubj.pk_accsubj,
2 bd_accsubj.subjcode,
3 bd_accsubj.subjname,
4 bd_accsubj.endflag
5from bd_accsubj
6 where bd_accsubj.pk_glorgbook = '0001A8100000000012H0'
7 and sealflag is null
8 and exists
9 (select subj1.Pk_accsubj
10
from bd_accsubj subj1, gl_verifyObj
11
where gl_verifyObj.Pk_accsubj = subj1.Pk_accsubj
12
and (gl_verifyObj.pk_glorgbook = '0001A810000000001NHP' and
13
bd_accsubj.subjcode like subj1.subjcode || '%' and
14
gl_verifyObj.userFlag = 'Y' and bd_accsubj.endflag = 'Y'))
15
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 720873777
-------------------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |66 |1983 (1)| 00:00:24 |
|*1 |FILTER
|
| | |
|
|
|*2 | TABLE ACCESS BY INDEX ROWID| BD_ACCSUBJ | 430 | 28380 |44 (0)| 00:00:01 |
|*3 |INDEX RANGE SCAN
| I_BD_ACCSUBJ_3 | 430 | | 2 (0)| 00:00:01 |
|*4 | FILTER
|
| | |
|
|
| 5 |NESTED LOOPS
|
| 1 |83 | 9 (0)| 00:00:01 |
|*6 | TABLE ACCESS FULL
| GL_VERIFYOBJ | 1 |53 | 8 (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 "GL_VERIFYOBJ" "GL_VERIFYOBJ","BD_ACCSUBJ"
"SUBJ1" WHERE :B1='Y' AND "GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ" AND :B2 LIKE
"SUBJ1"."SUBJCODE"||'%' AND "GL_VERIFYOBJ"."USERFLAG"='Y' AND
"GL_VERIFYOBJ"."PK_GLORGBOOK"='0001A810000000001NHP'))
2 - filter("SEALFLAG" IS NULL)
3 - access("BD_ACCSUBJ"."PK_GLORGBOOK"='0001A8100000000012H0')
4 - filter(:B1='Y')
6 - filter("GL_VERIFYOBJ"."USERFLAG"='Y' AND
"GL_VERIFYOBJ"."PK_GLORGBOOK"='0001A810000000001NHP')
7 - filter(:B1 LIKE "SUBJ1"."SUBJCODE"||'%')
8 - access("GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ")
Statistics
----------------------------------------------------------
1recursive calls
0db block gets
10918consistent gets
142physical reads
0redo size
406bytes sent via SQL*Net to client
339bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed
SQL>
SQL>
SQL> /
no rows selected
select * fromgl_verifyObj---这个表里面只有3条记录
create index IDX_GL_VERIFYOBJ8on gl_verifyObj(pk_glorgbook,userFlag) online compute statisticstablespace nnc_index02 ;
建了个索引后，为何逻辑读下降那么厉害？
见过索引后的执行计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 3972076677
----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |66 | 474 (0)| 00:00:06 |
|*1 |FILTER
|
| | |
|
|
|*2 | TABLE ACCESS BY INDEX ROWID| BD_ACCSUBJ| 430 | 28380 |44 (0)| 00:00:01 |
|*3 |INDEX RANGE SCAN
| I_BD_ACCSUBJ_3| 430 | | 2 (0)| 00:00:01 |
|*4 | FILTER
|
| | |
|
|
| 5 |NESTED LOOPS
|
| 1 |83 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| GL_VERIFYOBJ| 1 |53 | 1 (0)| 00:00:01 |
|*7 |INDEX RANGE SCAN
| IDX_GL_VERIFYOBJ8 | 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 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "GL_VERIFYOBJ" "GL_VERIFYOBJ","BD_ACCSUBJ"
"SUBJ1" WHERE :B1='Y' AND "GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ" AND :B2 LIKE
"SUBJ1"."SUBJCODE"||'%' AND "GL_VERIFYOBJ"."USERFLAG"='Y' AND
"GL_VERIFYOBJ"."PK_GLORGBOOK"='0001A810000000001NHP'))
2 - filter("SEALFLAG" IS NULL)
3 - access("BD_ACCSUBJ"."PK_GLORGBOOK"='0001A8100000000012H0')
4 - filter(:B1='Y')
7 - access("GL_VERIFYOBJ"."PK_GLORGBOOK"='0001A810000000001NHP' AND
"GL_VERIFYOBJ"."USERFLAG"='Y')
8 - filter(:B1 LIKE "SUBJ1"."SUBJCODE"||'%')
9 - access("GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ")
Statistics
----------------------------------------------------------
1recursive calls
0db block gets
622consistent gets
0physical reads
0redo size
406bytes sent via SQL*Net to client
339bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed
|
