更新时间:2022-04-11 20:53:31
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
create table t as select rownum id ,lpad('x',100,'x') name from dual connect by level
create index i_t_id on t(id);
exec dbms_stats.gather_table_stats(USER,'T',cascade => true);
2.测试:
SQL> column name format a100
SQL> set autot traceonly
SQL> select /*+ index(t i_t_id) */ * from t where id=5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 105 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=5000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
691 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> set autot off
SQL> delete from t where id=5100;
9900 rows deleted.
SQL> commit ;
Commit complete.
SQL> set autot traceonly
SQL> select min(id) from t ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3363318368
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 4 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
525 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> select max(id) from t ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3363318368
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 4 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
526 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> select /*+ index(t i_t_id) */ * from t where id=5000;
.....
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
create table t1 as select * from all_objects ;
create index i_t1_owner_obj_name on t1(owner, object_name);
delete from t1;
commit;
exec dbms_stats.gather_table_stats(USER,'T1',cascade => true);
SQL> set autot traceonly
SQL> select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS';
31659 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1461409258
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29373 | 4532K| 19304 (1)| 00:03:52 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 29373 | 4532K| 19304 (1)| 00:03:52 |
|* 2 | INDEX RANGE SCAN | I_T1_OWNER_OBJ_NAME | 29373 | | 165 (1)| 00:00:02 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
23032 consistent gets
155 physical reads
0 redo size
3222326 bytes sent via SQL*Net to client
2258 bytes received via SQL*Net from client
160 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
31659 rows processed--通过索引查找,仅仅23032个逻辑读,31659条记录,记录数很多。如果删除owner='SYS'的记录
SQL> delete from t1 where wner='SYS';
31659 rows deleted.
SQL> commit ;
Commit complete.
SQL> select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1461409258
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30941 | 2930K| 21985 (1)| 00:04:24 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 30941 | 2930K| 21985 (1)| 00:04:24 |
|* 2 | INDEX RANGE SCAN | I_T1_OWNER_OBJ_NAME | 30941 | | 180 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
176 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select object_name,object_id,data_object_id,object_type from dba_objects where object_id=267947;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------- --------- -------------- -------------------
I_T1_OWNER_OBJ_NAME 267947 267947 INDEX
SQL> alter index i_t1_owner_obj_name rebuild ;
Index altered.
SQL> set autot traceonly
SQL> select /*+ index(t1 i_t1_owner_obj_name) */ * from t1 where wner='SYS';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1461409258
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30941 | 2930K| 12523 (1)| 00:02:31 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 30941 | 2930K| 12523 (1)| 00:02:31 |
|* 2 | INDEX RANGE SCAN | I_T1_OWNER_OBJ_NAME | 30941 | | 106 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed