三十五、in写法差异

迈不过友情╰ 2022-04-06 17:21 211阅读 0赞

drop table t purge;
    create table t as select * from dba_objects;
    create index idx_object_id on t(object_id,object_type);
    UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;
    UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;
    COMMIT;
    set linesize 266
    set pagesize 1
    alter session set statistics_level=all ;
    
    
    select  /*+index(t,idx_object_id)*/ * from t  where object_TYPE='TABLE'  AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |               |      1 |        |   2925 |00:00:00.03 |    1103 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T             |      1 |   2126 |   2925 |00:00:00.03 |    1103 |
    |*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |      1 |    320 |   2925 |00:00:00.02 |     730 |
    -------------------------------------------------------------------------------------------------------
    
    select  /*+index(t,idx_object_id)*/ * from t t where object_TYPE='TABLE'  AND  OBJECT_ID IN (20,21);
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------------
    |   1 |  INLIST ITERATOR             |                |      1 |        |   2920 |00:00:00.01 |     563 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| t              |      2 |   2592 |   2920 |00:00:00.01 |     563 |
    |*  3 |    INDEX RANGE SCAN          | IDX1_OBJECT_ID |      2 |      1 |   2920 |00:00:00.01 |     214 |
    --------------------------------------------------------------------------------------------------------

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220406/7a241a38338d4dc5b6822009563ae02d.png