1.sql
SQL> set linesize 200
SQL> set pagesize 200
SQL> select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from klnl_dkkhmx
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null; 2 3 4 5
LMAXXUHAO
----------
2
SQL> select table_name,index_name from user_indexes a where a.table_name=upper('klnl_dkkhmx')
2 ;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
KLNL_DKKHMX KLNL_DKKHMX_IDX3
KLNL_DKKHMX KLNL_DKKHMX_IDX1
create unique index KLNL_DKKHMX_IDX1 on KLNL_DKKHMX (MINGXIBH, DKJIEJUH, FARENDMA);
create index KLNL_DKKHMX_IDX3 on KLNL_DKKHMX (DKJIEJUH, FARENDMA);
SQL> select count(*) from klnl_dkkhmx a where a.dkjiejuh = '20151020000935';
COUNT(*)
----------
6
SQL> select count(*) from klnl_dkkhmx a;
COUNT(*)
----------
228259
SQL> select count(*) from klnl_dkkhmx a where farendma = '9999';
COUNT(*)
----------
228259
SQL> alter system flush shared_pool;
System altered.
1. 走 KLNL_DKKHMX_IDX1 时的情况:
SQL> set autot trace
SQL> select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from klnl_dkkhmx
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null; 2 3 4 5
Execution Plan
----------------------------------------------------------
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 481 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 1 | 46 | 481 (1)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999')
Statistics
----------------------------------------------------------
127 recursive calls
0 db block gets
2032 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
30 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set linesize 200
SQL> set pagesize 200
SQL> select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_text like '%klnl_dkkhmx%'; 2 3 4 5 6 7 8 9 10 11
SQL_ID SQL_TEXT VERSION_COUNT LOADS HASH_VALUE ADDRESS PLAN_HASH_VALUE OLD_HASH_VALUE LAST_ACTIVE_CHIL
------------- -------------------------------------------------------------------------------- ------------- ---------- ---------- ---------------- --------------- -------------- ----------------
67wvhym3apr0k select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where d 2 8 3333086226 00000000E0220A88 2367693466 1481984114 00000000E0722260
kjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxi
bh, '0123456789', ' ')) is null
4sawk3nuxk59t select * from v$sqlarea a where a.sql_text like '%klnl_dkkhmx%' 1 1 903419193 00000000F2450E98 232555890 793758345 00000000E8EACC28
select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11 ;
SQL_ID SQL_TEXT VERSION_COUNT LOADS HASH_VALUE ADDRESS PLAN_HASH_VALUE OLD_HASH_VALUE LAST_ACTIVE_CHIL
------------- -------------------------------------------------------------------------------- ------------- ---------- ---------- ---------------- --------------- -------------- ----------------
67wvhym3apr0k select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where d 2 7 3333086226 00000000E0220A88 2367693466 1481984114 00000000E0722260
kjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxi
bh, '0123456789', ' ')) is null
SQL> select sql_id,
a.sql_text,
a.LOADED_VERSIONS,
a.hash_value,
a.ADDRESS,
a.OLD_HASH_VALUE,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id = '67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11 ;
SQL_ID SQL_TEXT LOADED_VERSIONS HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
------------- -------------------------------------------------------------------------------- --------------- ---------- ---------------- -------------- --------------- ------------ ----------------
67wvhym3apr0k select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where d 1 3333086226 00000000E0220A88 1481984114 2367693466 0 00000000E0722260
kjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxi
bh, '0123456789', ' ')) is null
SQL> select table_name,index_name from user_indexes a where a.table_name=upper('klnl_dkkhmx');
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
KLNL_DKKHMX KLNL_DKKHMX_IDX1
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('67wvhym3apr0k',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 67wvhym3apr0k, child number 0
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 481 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 1 | 46 | 481 (1)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
22 rows selected.
2.走KLNL_DKKHMX_IDX3 索引
create index KLNL_DKKHMX_IDX3 on KLNL_DKKHMX (DKJIEJUH, FARENDMA);
SQL> select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from klnl_dkkhmx
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null; 2 3 4 5
Execution Plan
----------------------------------------------------------
Plan hash value: 898418289
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| KLNL_DKKHMX | 1 | 46 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX3 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"='20151020000935' AND "FARENDMA"='9999')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
2 physical reads
0 redo size
535 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> col sql_text format a30
SQL> select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11 ;
SQL_ID SQL_TEXT VERSION_COUNT LOADS HASH_VALUE ADDRESS PLAN_HASH_VALUE OLD_HASH_VALUE LAST_ACTIVE_CHIL
------------- ------------------------------ ------------- ---------- ---------- ---------------- --------------- -------------- ----------------
67wvhym3apr0k select nvl(max(to_number(mingx 2 10 3333086226 00000000E0220A88 898418289 1481984114 00000000E8EF91C8
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
SQL> select sql_id,
a.sql_text,
a.LOADED_VERSIONS,
a.hash_value,
a.ADDRESS,
a.OLD_HASH_VALUE,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id = '67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11 ;
SQL_ID SQL_TEXT LOADED_VERSIONS HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
------------- ------------------------------ --------------- ---------- ---------------- -------------- --------------- ------------ ----------------
67wvhym3apr0k select nvl(max(to_number(mingx 1 3333086226 00000000E0220A88 1481984114 898418289 0 00000000E0722260
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
67wvhym3apr0k select nvl(max(to_number(mingx 1 3333086226 00000000E0220A88 1481984114 898418289 1 00000000E8EF91C8
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('67wvhym3apr0k',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 67wvhym3apr0k, child number 0
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 898418289
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| KLNL_DKKHMX | 1 | 46 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX3 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"='20151020000935' AND "FARENDMA"='9999')
23 rows selected.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('67wvhym3apr0k',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 67wvhym3apr0k, child number 1
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 481 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 1 | 46 | 481 (1)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
22 rows selected.
此时查看v$sqlarea 和v$sql
SQL> select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11 ;
SQL_ID SQL_TEXT VERSION_COUNT LOADS HASH_VALUE ADDRESS PLAN_HASH_VALUE OLD_HASH_VALUE LAST_ACTIVE_CHIL
------------- ------------------------------ ------------- ---------- ---------- ---------------- --------------- -------------- ----------------
67wvhym3apr0k select nvl(max(to_number(mingx 2 11 3333086226 00000000E0220A88 2367693466 1481984114 00000000E8EF91C8
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
SQL> select sql_id,
a.sql_text,
a.LOADED_VERSIONS,
a.hash_value,
a.ADDRESS,
a.OLD_HASH_VALUE,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id = '67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11 ;
SQL_ID SQL_TEXT LOADED_VERSIONS HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
------------- ------------------------------ --------------- ---------- ---------------- -------------- --------------- ------------ ----------------
67wvhym3apr0k select nvl(max(to_number(mingx 1 3333086226 00000000E0220A88 1481984114 898418289 0 00000000E0722260
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
67wvhym3apr0k select nvl(max(to_number(mingx 1 3333086226 00000000E0220A88 1481984114 2367693466 1 00000000E8EF91C8
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
当前的执行计划:
SQL> select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from klnl_dkkhmx
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null 2 3 4 5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 481 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 1 | 46 | 481 (1)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1780 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
当前激活的执行计划为00000000E8EF91C8
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('67wvhym3apr0k',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 67wvhym3apr0k, child number 1
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 481 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 1 | 46 | 481 (1)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
22 rows selected.
把索引加上去；
SQL> select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11
12 ;
SQL_ID SQL_TEXT VERSION_COUNT LOADS HASH_VALUE ADDRESS PLAN_HASH_VALUE OLD_HASH_VALUE LAST_ACTIVE_CHIL
------------- ------------------------------ ------------- ---------- ---------- ---------------- --------------- -------------- ----------------
67wvhym3apr0k select nvl(max(to_number(mingx 3 13 3333086226 00000000E0220A88 898418289 1481984114 00000000F25C6A30
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
SQL> select sql_id,
a.sql_text,
a.LOADED_VERSIONS,
a.hash_value,
a.ADDRESS,
a.OLD_HASH_VALUE,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id = '67wvhym3apr0k' 2 3 4 5 6 7 8 9 10 11
12 ;
SQL_ID SQL_TEXT LOADED_VERSIONS HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
------------- ------------------------------ --------------- ---------- ---------------- -------------- --------------- ------------ ----------------
67wvhym3apr0k select nvl(max(to_number(mingx 1 3333086226 00000000E0220A88 1481984114 898418289 0 00000000E0722260
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
67wvhym3apr0k select nvl(max(to_number(mingx 1 3333086226 00000000E0220A88 1481984114 898418289 1 00000000E8EF91C8
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
67wvhym3apr0k select nvl(max(to_number(mingx 1 3333086226 00000000E0220A88 1481984114 898418289 2 00000000F25C6A30
ibh)), 0) as lMaxXuhao from
klnl_dkkhmx where dkjiejuh =
'20151020000935' and farend
ma = '9999' and trim(transl
ate(mingxibh, '0123456789', '
')) is null
查看当前执行计划:
SQL> select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from klnl_dkkhmx
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null 2 3 4 5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 898418289
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| KLNL_DKKHMX | 1 | 46 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX3 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('67wvhym3apr0k',2));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 67wvhym3apr0k, child number 2
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 898418289
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| KLNL_DKKHMX | 1 | 46 | 9 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX3 | 6 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"='20151020000935' AND "FARENDMA"='9999')
Note
-----
- cardinality feedback used for this statement
27 rows selected.
删除索引
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('67wvhym3apr0k',2));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 67wvhym3apr0k, child number 2
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 481 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 1 | 46 | 481 (1)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))