复合索引和INDEXSKIPSCAN(三)
ce 1 Diag Alert /opt/app/oracle/diag/rdbms/rhys/RHYS/alert 1 Diag Incident /opt/app/oracle/diag/rdbms/rhys/RHYS/incident 1 Diag Cdump /opt/app/oracle/diag/rdbms/rhys/RHYS/cdump 1 Health Monitor /opt/app/oracle/diag/rdbms/rhys/RHYS/hm 1 Default Trace File /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_4694.trc 1 Active Problem Count 1 1 Active Incident Count 1 11 rows selected. SQL> 看一下执行计划： [oracle@oracle-one script]$ tkprof RHYS_ora_4694.trc tkprof_4694.txt sys=no aggregate=yes explain=amy/rhys record=record_sql.sql waits=yes TKPROF: Release 11.2.0.4.0 - Development on Tue Jan 21 15:31:42 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. [oracle@oracle-one script]$ [oracle@oracle-one script]$ vi tkprof_4694.txt TKPROF: Release 11.2.0.4.0 - Development on Tue Jan 21 15:31:42 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Trace file: RHYS_ora_4694.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** SQL ID: ajqsk3f0nk06d Plan Hash: 954130750 select * from emp where sal=1250 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.03 0 4 0 2 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 90 (AMY) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 2 2 2 TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=41 us cost=2 size=32 card=1) 2 2 2 INDEX SKIP SCAN EMP_IDX1 (cr=2 pr=0 pw=0 time=42 us cost=1 size=0 card=1)(object id 88000) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'EMP' (TABLE) 2 INDEX MODE: ANALYZED (SKIP SCAN) OF 'EMP_IDX1' (INDEX) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 14.93 14.93 ******************************************************************************** SQL>