ORA600[13011]表与索引数据逻辑错误分析及解决(3)

--SQL执行计划通过索引扫描查询的数据
 
SQL> alter session set statistics_level=all; 
Session altered.
SQL> select e.*,rownum,rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.AX_ID=**** and e.BX_ID=**;
    AX_ID      BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T    ROWNUM ROWID
---------- ---------- -------------- ---------------- -------------- --------- ---------- ------------------
    ****      **    **********              *            * *********          * AABOd5AAIAAAGcaABR
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cy48jvzrnuv22, child number 1
-------------------------------------
select e.*,rownum,rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.AX_ID=**** and e.BX_ID=**
Plan hash value: 1022151449 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |  A-Time  | Buffers |
--------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                    |      1 |        |      1 |00:00:00.01 |      3 |
|  1 |  COUNT                      |                    |      1 |        |      1 |00:00:00.01 |      3 |
|  2 |  TABLE ACCESS BY INDEX ROWID| XXX_XX_XX_XXX_OLD |      1 |      1 |      1 |00:00:00.01 |      3 |
|*  3 |    INDEX UNIQUE SCAN        | PK_XXX_XX_XX_XXX  |      1 |      1 |      1 |00:00:00.01 |      2 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("E"."AX_ID"=**** AND "E"."BX_ID"=**)
21 rows selected.
 
--SQL执行计划通过全表查询的数据
 
SQL> select /*+ full(e) */ e.*,rownum,rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.AX_ID=**** and e.BX_ID=**;
    AX_ID      BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T    ROWNUM ROWID
---------- ---------- -------------- ---------------- -------------- --------- ---------- ------------------
    ****      **    **********              *            * *********          *  AABOd5AAIAABFRCACk
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  14vbv6bu472ty, child number 1
-------------------------------------
select /*+ full(e) */ e.*,rownum,rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.AX_ID=**** and e.BX_ID=**
 
Plan hash value: 3364144674
----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | A-Rows |  A-Time  | Buffers |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |                    |      1 |        |      1 |00:00:00.01 |      68 |
|  1 |  COUNT            |                    |      1 |        |      1 |00:00:00.01 |      68 |
|*  2 |  TABLE ACCESS FULL| XXX_XX_XX_XXX_OLD |      1 |      1 |      1 |00:00:00.01 |      68 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter(("E"."AX_ID"=**** AND "E"."BX_ID"=**))
20 rows selected.

通过对比走全表扫描和索引扫描时存在差异

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/931f73a3ca3fd30d7d2d082adc47c80e.html