TABLE ACCESS BY INDEX ROWID前面的星号

<pre name="code" class="sql">SQL> create table  test as select * from dba_objects;Table created.select * from test where object_id=10 and OWNER='SYS';SQL> select count(*) from test where object_id=10;  COUNT(*)---------- 1SQL> select count(*) from test;  COUNT(*)----------     74443模拟插入object_id=10 and OWNER='SYS'的记录SQL> select object_id,owner,count(*) from test2 group by object_id,owner having object_id=10; OBJECT_ID OWNER                            COUNT(*)---------- ------------------------------ ----------        10 TEST                                72636        10 SYS                                 10003SQL> select count(*) from test2;  COUNT(*)----------   1578831SQL> create index test2_idx1 on test2(object_id);索引已创建。分析下表:BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',                                tabname          => 'TEST2',                                estimate_percent => 30,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  gmv066f6cfyhd, child number 0-------------------------------------select * from test2 where object_id=10 and owner='SYS'Plan hash value: 3497718064-------------------------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |-------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |      1 |        |  10003 |00:00:00.60 |    2628 |   1297 ||*  1 |  TABLE ACCESS BY INDEX ROWID| TEST2      |      1 |      1 |  10003 |00:00:00.60 |    2628 |   1297 ||*  2 |   INDEX RANGE SCAN          | TEST2_IDX1 |      1 |     22 |  82639 |00:00:00.15 |     830 |    163 |-------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OWNER"='SYS')   2 - access("OBJECT_ID"=10)已选择20行。可以看到object_id=10的记录数为82639行,回表后过滤OWNER='SYS'后为10003行,此时有10003个rowid需要回表逻辑读为:SQL> select * from test2 where object_id=10 and owner='SYS'  2  ;已选择10003行。执行计划----------------------------------------------------------Plan hash value: 3497718064------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |     1 |    96 |    24   (0)| 00:00:01 ||*  1 |  TABLE ACCESS BY INDEX ROWID| TEST2      |     1 |    96 |    24   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | TEST2_IDX1 |    22 |       |     3   (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OWNER"='SYS')   2 - access("OBJECT_ID"=10)统计信息----------------------------------------------------------          1  recursive calls          0  db block gets       2628  consistent gets          0  physical reads          0  redo size     393766  bytes sent via SQL*Net to client       7741  bytes received via SQL*Net from client        668  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      10003  rows processed创建组合索引后:SQL> create index test2_idx2 on test2(object_id,owner);SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  gmv066f6cfyhd, child number 0-------------------------------------select * from test2 where object_id=10 and owner='SYS'Plan hash value: 3385680830-------------------------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |-------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |      1 |        |  10003 |00:00:00.08 |    1488 |     27 ||   1 |  TABLE ACCESS BY INDEX ROWID| TEST2      |      1 |      1 |  10003 |00:00:00.08 |    1488 |     27 ||*  2 |   INDEX RANGE SCAN          | TEST2_IDX2 |      1 |     22 |  10003 |00:00:00.02 |     693 |     27 |-------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=10 AND "OWNER"='SYS')此时直接索引过滤后顺下10003行记录,不需要回表在过滤数据。结论:看到TABLE ACCESS BY INDEX ROWID前面有*,说明索引扫描返回rowid后,还要在表上进行过滤，比如上面索引扫描后返回了82639 个rowid,在表上过滤后只顺下10003个,这样就可以通过创建组合索引来减少回表的rowid.