ROWID走索引之判决

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: 1898483634
--------------------------------------------------------------------------------
----
| 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 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12ORA-12519错误的解决方案ROWID走索引之嫌疑犯抓获相关资讯 Oracle存储过程

Oracle存储过程拼接in语句 & 自定（今 09:21）
【PL/SQL系列】Oracle存储过程使用（04月23日）
Oracle存储过程及Java调用（05/28/2015 20:29:33）

Java调用Oracle存储过程返回多条结（04月29日）
Oracle中的存储过程简单应用（04月10日）
判断点是否落在面中的Oracle存储过（05/09/2015 09:39:30）

本文评论查看全部评论（0）

评论声明

尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任