ROWID走索引之判决,在NOT NULL 且数据位0的情况下 index的 block 为0，table的至少为1 那么index的cost就是0，CBO当然会选择cost
ROWID走索引之判决:
SQL> select rowid from a;
ROWID
------------------
AAAQ/LAACAAABacAAA
AAAQ/LAACAAABacAAB
SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(5)
NAME VARCHAR2(35)
CREATE_TIME DATE
SQL> select index_name from user_indexes where table_name='A';
INDEX_NAME
--------------------------------------------------------------------------------
IDX_A_ID
SQL> delete from a;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'A',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
SQL> delete from a where rowid='AAAQ/LAACAAABacAAA';
0 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 2233874139
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 25 | 0 (0)| 00:00:01 |
| 1 | DELETE | A | | | | |
|* 2 | INDEX FULL SCAN| IDX_A_ID | 1 | 25 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWID='AAAQ/LAACAAABacAAA')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
824 bytes sent via SQL*Net to client
739 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 autotrace off;
SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(5)
NAME VARCHAR2(35)
CREATE_TIME DATE
SQL> alter table a modify id null;
Table altered.
SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(5)
NAME VARCHAR2(35)
CREATE_TIME DATE
SQL> set autotrace traceonly;
SQL> delete from a where rowid='AAAQ/LAACAAABacAAB';
0 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 18483634
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | DELETE STATEMENT | | 1 | 25 | 1 (0)| 00:00:
01 |
| 1 | DELETE | A | | | |
|
| 2 | TABLE ACCESS BY USER ROWID| A | 1 | 25 | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
739 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
作了个10053,以下是trace 文件部分内容 走索引
***********************
Table Stats::
Table: T Alias: T
#Rows: 0 #Blks: 1 AvgRowLen: 0.00
Index Stats::
Index: I_T Col#: 1
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
***************************************
1-ROW TABLES: T[T]#0
***************************************
SINGLE TABLE ACCESS PATH
Column (#1001): ROWID(ROWID) NO STATISTICS (using defaults)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+000
Table: T Alias: T
Card: Original: 0 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 7121
Resp_io: 2.00 Resp_cpu: 7121
Access Path: Rowid
Cost: 1.00 Resp: 1.00 Degree: 0
Cost_io: 1.00 Cost_cpu: 7271
Resp_io: 1.00 Resp_cpu: 7271
kkofmx: index filter:"T".ROWID='AAAQ9uAACAAABacAAB'
Access Path: index (FullScan)
Index: I_T
resc_io: 0.00 resc_cpu: 200
ix_sel: 1 ix_sel_with_filters: 1
Cost: 0.00 Resp: 0.00 Degree: 1
Best:: AccessPath: IndexRange Index: I_T
Cost: 0.00 Degree: 1 Resp: 0.00 Card: 1.00 Bytes: 0
走表
INGLE TABLE ACCESS PATH
Column (#1001): ROWID(ROWID) NO STATISTICS (using defaults)
AvgLen: 10.00 NDV: 3 Nulls: 0 Density: 0.33333
Table: A Alias: A
Card: Original: 3 Rounded: 1 Computed: 0.03 Non Adjusted: 0.03
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 36167
Resp_io: 3.00 Resp_cpu: 36167
Access Path: Rowid
Cost: 1.00 Resp: 1.00 Degree: 0
Cost_io: 1.00 Cost_cpu: 7271
Resp_io: 1.00 Resp_cpu: 7271
kkofmx: index filter:"A".ROWID='AAAQ/LAACAAABacAAD'
Access Path: index (FullScan)
Index: IDX_A_ID
resc_io: 1.00 resc_cpu: 7721
ix_sel: 1 ix_sel_with_filters: 1
Cost: 1.00 Resp: 1.00 Degree: 1
One row Card: 1.00
Best:: AccessPath: RowId
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0
在 NULL 约束下并且PK的约束的话~是必须要table access full的 因为首先要保证的是结果的准确
在NOT NULL 且数据位0的情况下 index的 block 为0，，table的至少为1 那么index的cost就是0，CBO当然会选择cost低的来执行了
NOT NULL约束保证了结果准确性 才选择成本低的INDEX
说明一点要做好表信息分析收集工作
更多Oracle相关信息见Oracle 专题页面 ?tid=12
