create test as select * from DBA_objects;
主键列是oject_id
该列上的惟一索引是UNI_T_IDX
还有一个复合索引TEST_IDX在(object_id,object_name,data_object_id)上
收集统计信息后
select object_id from test;
走的竟然是全表扫描

SQL> select object_id from test;

71899 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 71899 |   351K|   289   (2)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TEST | 71899 |   351K|   289   (2)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_d7ctrd1n9v57y97bbe3d0" used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       5744  consistent gets
         34  physical reads
          0  redo size
    1312989  bytes sent via SQL*Net to client
      53246  bytes received via SQL*Net from client
       4795  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71899  rows processed


----加了HINT，才走索引

SQL> select /*+ index(test,uni_t_idx) */ object_id from test;

71899 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4280883380

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           | 71899 |   351K|   152   (2)| 00:00:02 |
|   1 |  INDEX FULL SCAN | UNI_T_IDX | 71899 |   351K|   152   (2)| 00:00:02 |
------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_ar6zhqbn2ybha4fb246d7" used for this statement


Statistics
----------------------------------------------------------
         27  recursive calls
         56  db block gets
       4958  consistent gets
          1  physical reads
      14012  redo size
    1312989  bytes sent via SQL*Net to client
      53246  bytes received via SQL*Net from client
       4795  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71899  rows processed



做了一个10053（select object_id from test;）

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST"."OBJECT_ID" "OBJECT_ID" FROM "OVSEE"."TEST" "TEST"
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=1008, alloc=16344), compile(in-use=56080, alloc=58792), execution(in-use=2472, alloc=4032)

kkoqbc-subheap (create addr=0x2a970efb08)
****************
QUERY BLOCK TEXT
****************
select object_id from test
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=73239 hint_alias="TEST"@"SEL$1"

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 630 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST  Alias: TEST
    #Rows: 71899  #Blks:  1050  AvgRowLen:  98.00
Index Stats::
  Index: TEST_IDX  Col#: 4 2 5
    LVLS: 1  #LB: 420  #DK: 71899  LB/K: 1.00  DB/K: 1.00  CLUF: 1066.00
  Index: UNI_T_IDX  Col#: 4
    LVLS: 1  #LB: 149  #DK: 71899  LB/K: 1.00  DB/K: 1.00  CLUF: 1066.00
Access path analysis for TEST
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TEST[TEST]
  Table: TEST  Alias: TEST
    Card: Original: 71899.000000  Rounded: 71899  Computed: 71899.00  Non Adjusted: 71899.00
  Access Path: TableScan
    Cost:  288.98  Resp: 288.98  Degree: 0
      Cost_io: 286.00  Cost_cpu: 22576302
      Resp_io: 286.00  Resp_cpu: 22576302
  Access Path: index (index (FFS))
    Index: TEST_IDX
    resc_io: 116.00  resc_cpu: 11618885
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000
  Access Path: index (FFS)
    Cost:  117.54  Resp: 117.54  Degree: 1
      Cost_io: 116.00  Cost_cpu: 11618885
      Resp_io: 116.00  Resp_cpu: 11618885
  Access Path: index (index (FFS))
    Index: UNI_T_IDX
    resc_io: 42.00  resc_cpu: 9688975
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000
  Access Path: index (FFS)
    Cost:  43.28  Resp: 43.28  Degree: 1
      Cost_io: 42.00  Cost_cpu: 9688975
      Resp_io: 42.00  Resp_cpu: 9688975
  Access Path: index (FullScan)
    Index: TEST_IDX
    resc_io: 421.00  resc_cpu: 17377926
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 423.30  Resp: 423.30  Degree: 1
  Access Path: index (FullScan)
    Index: UNI_T_IDX
    resc_io: 150.00  resc_cpu: 15448016
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 152.04  Resp: 152.04  Degree: 1
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: TEST_IDX
    resc_io: 421.00  resc_cpu: 17377926
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 423.30  Resp: 423.30  Degree: 0
  Access Path: index (FullScan)
    Index: UNI_T_IDX
    resc_io: 150.00  resc_cpu: 15448016
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 152.04  Resp: 152.04  Degree: 0
  ****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: TEST_IDX
    resc_io: 421.00  resc_cpu: 17377926
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 423.30  Resp: 423.30  Degree: 0
  Access Path: index (FullScan)
    Index: UNI_T_IDX
    resc_io: 150.00  resc_cpu: 15448016
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 152.04  Resp: 152.04  Degree: 0
  Access Path: index (FullScan)
    Index: UNI_T_IDX
    resc_io: 150.00  resc_cpu: 15448016
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 152.04  Resp: 152.04  Degree: 0
  Bitmap nodes:
    Used UNI_T_IDX
      Cost = 190.052466, sel = 1.000000
  ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexFFS              -----这里分析出来的是FFS最好,COST 43
     Index: UNI_T_IDX
         Cost: 43.28  Degree: 1  Resp: 43.28  Card: 71899.00  Bytes: 0




然而，后面又有继续分析

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("TEST") */ "TEST"."OBJECT_ID" "OBJECT_ID" FROM "OVSEE"."TEST" "TEST"  ---不知这里为什么加了FULL
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=1024, alloc=16344), compile(in-use=59400, alloc=60664), execution(in-use=3512, alloc=4032)

kkoqbc-subheap (create addr=0x2a971dbf60)
****************
QUERY BLOCK TEXT
****************
select object_id from test
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=73239 hint_alias="TEST"@"SEL$1"

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 630 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST  Alias: TEST
    #Rows: 71899  #Blks:  1050  AvgRowLen:  98.00
Index Stats::
  Index: TEST_IDX  Col#: 4 2 5
    LVLS: 1  #LB: 420  #DK: 71899  LB/K: 1.00  DB/K: 1.00  CLUF: 1066.00
  Index: UNI_T_IDX  Col#: 4
    LVLS: 1  #LB: 149  #DK: 71899  LB/K: 1.00  DB/K: 1.00  CLUF: 1066.00
Access path analysis for TEST
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TEST[TEST]
  Table: TEST  Alias: TEST
    Card: Original: 71899.000000  Rounded: 71899  Computed: 71899.00  Non Adjusted: 71899.00
  Access Path: TableScan
    Cost:  288.98  Resp: 288.98  Degree: 0
      Cost_io: 286.00  Cost_cpu: 22576302
      Resp_io: 286.00  Resp_cpu: 22576302
  Best:: AccessPath: TableScan               ---出来的结果是这个最好的 COST 288
         Cost: 288.98  Degree: 1  Resp: 288.98  Card: 71899.00  Bytes: 0






OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  TEST[TEST]#0
***********************
Best so far:  Table#: 0  cost: 288.9842  card: 71899.0000  bytes: 359495
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
Or-Expansion validity checks failed on query block SEL$1 (#0) because NO_EXPAND hint
Transfer Optimizer annotations for query block SEL$1 (#0)
Final cost for query block SEL$1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 288.9842  Degree: 1  Card: 71899.0000  Bytes: 359495
  Resc: 288.9842  Resc_io: 286.0000  Resc_cpu: 22576302
  Resp: 288.9842  Resp_io: 286.0000  Resc_cpu: 22576302
kkoqbc-subheap (delete addr=0x2a970efb08, in-use=11576, alloc=14408)
kkoqbc-end:
        :
    call(in-use=7384, alloc=32712), compile(in-use=68128, alloc=72504), execution(in-use=97024, alloc=97560)

kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
          :
    call(in-use=7384, alloc=32712), compile(in-use=69040, alloc=72504), execution(in-use=97024, alloc=97560)


Starting SQL statement dump

user_id=84 user_name=OVSEE module=SQL*Plus action=
sql_id=dywdy4jmt6vz9 plan_hash_value=1357081020 problem_type=3
----- Current SQL Statement for this session (sql_id=dywdy4jmt6vz9) -----
EXPLAIN PLAN SET STATEMENT_ID='PLUS120042' FOR select object_id from test
sql_text_length=74
sql=EXPLAIN PLAN SET STATEMENT_ID='PLUS120042' FOR select object_id from test
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |   289 |           |
| 1   |  TABLE ACCESS FULL | TEST    |   70K |  351K |   289 |  00:00:04 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------

最好选择了会表扫，但从加了HINT 结果来看，是走索引COST比较低的，IO读也是。一直没法弄懂

select object_id from test.你查询所有object_id当然是全表扫描好，为什么要走索引呢，走索引会扫更多的块。