--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.
通过对比走全表扫描和索引扫描时存在差异