Oracle查询非索引列时如何利用函数基索引预计算值

create table lines (id number(38,0), 
                    details1 varchar2(10), 
                    details2 varchar2(10), 
                    details3 varchar2(10), 
                    shape sdo_geometry);
begin
    insert into lines (id, details1, details2, details3, shape) values (1, 'a', 'b', 'c', sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574360, 4767080, 574200, 4766980)));
    insert into lines (id, details1, details2, details3, shape) values (2, 'a', 'b', 'c', sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(573650, 4769050, 573580, 4768870)));
    insert into lines (id, details1, details2, details3, shape) values (3, 'a', 'b', 'c', sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574290, 4767090, 574200, 4767070)));
    insert into lines (id, details1, details2, details3, shape) values (4, 'a', 'b', 'c', sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(571430, 4768160, 571260, 4768040)));
    ...
end;
/

核心需求：通过函数基索引（FBI）预计算派生列值，降低查询时的函数计算开销。

(1) 创建自定义确定性函数 #

从SDO_GEOMETRY类型列中提取线要素起点的X、Y坐标（数值类型）：

create function startpoint_x(shape in sdo_geometry) return number 
deterministic is
begin
    return shape.sdo_ordinates(1);
end; 
   
create function startpoint_y(shape in sdo_geometry) return number 
deterministic is
begin
    return shape.sdo_ordinates(2);
end;  

直接查询全表调用函数时会触发全表扫描，查询示例：

select
    id,
    details1,
    details2,
    details3,
    startpoint_x(shape) as startpoint_x,
    startpoint_y(shape) as startpoint_y
from
    lines

返回结果示例：

ID DETAILS1   DETAILS2   DETAILS3   STARTPOINT_X STARTPOINT_Y
---------- ---------- ---------- ---------- ------------ ------------
       177 a          b          c                574660      4766400
       178 a          b          c                574840      4765370
       179 a          b          c                573410      4768570
       180 a          b          c                573000      4767330
       ...

执行方式：full table scan（全表扫描）

(2) 创建复合函数基索引 #

索引中存储ID、起点X坐标、起点Y坐标三个值：

create index lines_fbi_idx on lines (id, startpoint_x(shape), startpoint_y(shape))

(3) 仅查询索引覆盖列的执行效果 #

仅查询被索引覆盖的列时，优化器会选择该FBI执行INDEX FAST FULL SCAN，无需全表扫描，符合预期：

select
    id,
    startpoint_x(shape) as startpoint_x,
    startpoint_y(shape) as startpoint_y
from
    lines
where
  id is not null
  and startpoint_x(shape) is not null
  and startpoint_y(shape) is not null

执行计划如下：

--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     3 |   117 |     4   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| LINES_FBI_IDX |     3 |   117 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID" IS NOT NULL AND "INFRASTR"."STARTPOINT_X"("SHAPE") IS NOT 
              NULL AND "INFRASTR"."STARTPOINT_Y"("SHAPE") IS NOT NULL)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

说明：上述为简化演示示例，实际场景中自定义函数逻辑更复杂、执行开销更高，因此需要通过索引预计算结果提升查询性能。 #

除了查询索引覆盖的列（ID、startpoint_x、startpoint_y）之外，还需要查询未被索引的details1、details2、details3列。如何实现在查询非索引列的同时，复用函数基索引中预计算的列值，避免重复执行自定义函数和不必要的全表扫描？

内容的提问来源于stack exchange，提问作者User1974