SQL> conn system/yang as sysdba
已连接。
SQL> deac t
SP2-0042: 未知命令 "deac t" - 其余行忽略。
SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
SQL> select count(owner) from t;
COUNT(OWNER)
------------
68272
SQL> show arraysize
arraysize 15
SQL> set autot traceonly
SQL> create index i_t_id on t(object_id);
索引已创建。
SQL> set timing on
SQL> update t set object_id=1 where object_id is null;
已更新0行。
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1357257176
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 4 | 0 (0)| |
| 1 | UPDATE | T | | | | |
|* 2 | FILTER | | | | | |
| 3 | INDEX FULL SCAN| I_T_ID | 68272 | 266K| 153 (1)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
统计信息
----------------------------------------------------------
89 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
675 bytes sent via SQL*Net to client
618 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autot traceonly
SQL> select /*+ index(t)*/* from t;
已选择68272行。
已用时间: 00: 00: 03.46
执行计划
----------------------------------------------------------
Plan hash value: 4247898483
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 68272 | 6200K| 1196 (1)| 00:0
0:15 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 68272 | 6200K| 1196 (1)| 00:0
0:15 |
| 2 | INDEX FULL SCAN | I_T_ID | 68272 | | 153 (1)| 00:0
0:02 |
--------------------------------------------------------------------------------
------
统计信息
----------------------------------------------------------
284 recursive calls
0 db block gets
10257 consistent gets
151 physical reads
0 redo size
7804835 bytes sent via SQL*Net to client
50477 bytes received via SQL*Net from client
4553 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
68272 rows processed
SQL> select a.index_name,
2 a.index_type,
3 a.blevel,
4 a.leaf_blocks,
5 a.distinct_keys,
6 a.clustering_factor,
7 a.num_rows
8 from dba_indexes a
9 where a.table_name='T';
INDEX_NAME INDEX_TYPE BLEVEL
------------------------------ --------------------------- ----------
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
----------- ------------- ----------------- ----------
SYS_C0010490 NORMAL 0
1 2 2 2
SYS_C0010487 NORMAL 0
1 2 2 2
T_ENAME NORMAL 0
1 14 1 14
INDEX_NAME INDEX_TYPE BLEVEL
------------------------------ --------------------------- ----------
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
----------- ------------- ----------------- ----------
EMPNO_I NORMAL 0
1 14 1 14
I_T1 NORMAL 2
392 66333 34935 68272
I_T_ID NORMAL 1
151 68272 1042 68272
已选择6行。
已用时间: 00: 00: 00.00
SQL> select blocks,table_name from user_tables where table_name='T';
BLOCKS TABLE_NAME
---------- ------------------------------
1004 T
已用时间: 00: 00: 00.03
SQL> select (68272/15)*2+1004+151 from dual;
(68272/15)*2+1004+151
---------------------
10257.9333 -------------- 10257 consistent gets 基本一致
已用时间: 00: 00: 00.00
1004是表的块数，151是leaf block数，index full scan是扫描全部的叶块。
总行数/sqlplus一次返回的行数 ，算一次逻辑读
