Oracle 19c
优化器参数均为默认，未修改。
表结构：sample schema hr 中employees表，无任何约束，表数据量为21w，均为employees表基础数据反复插入生成。
表统计信息：为表收集统计信息，并且为employee_id收集直方图。
索引：employee_id建立普通b-tree索引

sql语句：
SELECT /*+ gather_plan_statistics */ salary from test_ffs where employee_id < 100;

执行计划：
16:32:14 SYS@orcl2 > select * from table(dbms_xplan.display_cursor(‘c9qg9su5khysd’,null,‘allstats last’));

PLAN_TABLE_OUTPUT

SQL_ID c9qg9su5khysd, child number 0

SELECT /*+ gather_plan_statistics */ salary from test_ffs where
employee_id < 100

Plan hash value: 296244252

---

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.11 | 2265 | 2261 |
|* 1 | TABLE ACCESS FULL| TEST_FFS | 1 | 1024 | 0 |00:00:00.11 | 2265 | 2261 |

Predicate Information (identified by operation id):

1 - filter(“EMPLOYEE_ID”<100)

19 rows selected.

下面为10053部分摘要信息：

---

BASE STATISTICAL INFORMATION

---

Table Stats::
Table: TEST_FFS Alias: TEST_FFSonline table stats for conventional DML (block count: 2263 row count: 219029) used on (TEST_FFS) block count: 5 -> 2263, row count: 107 -> 219136
#Rows: 219136 SSZ: 0 LGR: 0 #Blks: 2263 AvgRowLen: 69.00 NEB: 0 ChainCnt: 0.00 ScanRate: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 193
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Index Stats::
Index: IDX_TEST_FFS Col#: 1
LVLS: 1 #LB: 458 #DK: 107 LB/K: 4.00 DB/K: 1524.00 CLUF: 163174.00 NRW: 219136.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
try to generate single-table filter predicates from ORs for query block SEL$1 (#0)
finally: “TEST_FFS”.“EMPLOYEE_ID”<100

=======================================
SPD: BEGIN context at query block level

Query Block SEL$1 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX

SPD: END context at query block level

Access path analysis for TEST_FFS

---

SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST_FFS[TEST_FFS]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

kkecdn: Single Table Predicate:“TEST_FFS”.“EMPLOYEE_ID”<100
online column stats for conventional DML used on (TEST_FFS.EMPLOYEE_ID) min: 100.00 -> 100.00, max: 206.00 -> 206.00, nnl: 0 -> 0, acl: 4 -> 0
Column (#1): EMPLOYEE_ID(NUMBER)
AvgLen: 22 NDV: 107 Nulls: 0 Density: 0.009346 Min: 100.000000 Max: 206.000000
Using density: 0.009346 of col #1 as selectivity of unpopular value pred
Table: TEST_FFS Alias: TEST_FFS
Card: Original: 219136.000000 Rounded: 2048 Computed: 2048.000000 Non Adjusted: 2048.000000
Scan IO Cost (Disk) = 615.000000
Scan CPU Cost (Disk) = 49272938.720000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.009346 flag = 2048 (“TEST_FFS”.“EMPLOYEE_ID”<100)
Total Scan IO Cost = 615.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 219136.000000 (#rows))
= 615.000000
Total Scan CPU Cost = 49272938.720000 (scan (Disk))
+ 10956800.000000 (cpu filter eval) (= 50.000000 (per row) * 219136.000000 (#rows))
= 60229738.720000
Access Path: TableScan
Cost: 621.167026 Resp: 621.167026 Degree: 0
Cost_io: 615.000000 Cost_cpu: 60229739
Resp_io: 615.000000 Resp_cpu: 60229739
****** Costing Index IDX_TEST_FFS
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Using density: 0.009346 of col #1 as selectivity of unpopular value pred
Access Path: index (RangeScan)
Index: IDX_TEST_FFS
resc_io: 1531.000000 resc_cpu: 11906445
ix_sel: 0.009346 ix_sel_with_filters: 0.009346
Cost: 1532.219121 Resp: 1532.219121 Degree: 1
Best:: AccessPath: TableScan
Cost: 621.167026 Degree: 1 Resp: 621.167026 Card: 2048.000000 Bytes: 0.000000

online column stats for conventional DML used on (TEST_FFS.SALARY) min: 2100.00 -> 2100.00, max: 24000.00 -> 24000.00, nnl: 0 -> 0, acl: 4 -> 0

---

下面为使用hint走索引执行计划：
16:48:28 SYS@orcl2 > select * from table(dbms_xplan.display_cursor(‘4qn6rkkan9zqn’,null,‘allstats last’));

PLAN_TABLE_OUTPUT

SQL_ID 4qn6rkkan9zqn, child number 0

SELECT /+ gather_plan_statistics index(t idx_test_ffs)/ salary from
test_ffs t where employee_id < :v1

Plan hash value: 3349260958

---

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 2 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_FFS | 1 | 1024 | 0 |00:00:00.01 | 2 | 2 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_FFS | 1 | 1024 | 0 |00:00:00.01 | 2 | 2 |

Predicate Information (identified by operation id):

2 - access(“EMPLOYEE_ID”<:V1)

20 rows selected.

问题：
为什么优化器估算出来，走索引的成本会高于全表呢？employee_id < 100一条数据都没有，走索引的效率要远远高于全表扫描。