Utilize a function-based index while selecting additional columns-CodePudding

I have an Oracle 18c function-based index that works as expected:

1.Create a custom function that takes a user-defined type object, and returns a list of coordinates as text:

create or replace function endpoint_list(shape in sde.st_geometry) return varchar2 
deterministic is
    coord_list varchar2(4000);
begin
    coord_list := 
      --sde.st_geometry functions are notoriously slow.  
      sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryn_f(shape,1))) || ',' || 
            sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryn_f(shape,1))) || ',' || 
                sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_endpoint_f(  sde.st_geometry_operators.st_geometryn_f(shape,1))) || ',' ||
                    sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_endpoint_f(  sde.st_geometry_operators.st_geometryn_f(shape,1)));
return coord_list; 
end;

2.Create a function-based index (FBI) on the custom function:

create index atn_endpoint_list_idx on my_owner.active_transportation(my_owner.endpoint_list(shape));

3.Run a query that uses the FBI in the SELECT clause:

select  
    endpoint_list(shape) as list
from
    active_transportation --15,000 rows
where
    endpoint_list(shape) is not null


----------------------------------------------------------------------------------------------
| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       |   727 |   299K|    50  (10)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| ATN_ENDPOINT_LIST_IDX |   727 |   299K|    50  (10)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("my_owner"."ENDPOINT_LIST"("SHAPE") IS NOT NULL)

That FBI works as expected. When I select endpoint_list(shape) as a column, the query uses the index, cutting the run time down from 65 seconds to .09 seconds. Good.

In addition to selecting endpoint_list(shape), I also want to select other columns from the table, such as the OBJECTID column:

select  
    objectid,  --Note: OBJECTID has a index of it's own (unique).
    endpoint_list(shape) as list
from
    active_transportation
where
    endpoint_list(shape) is not null

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |   727 |   299K|   181   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ACTIVE_TRANSPORTATION |   727 |   299K|   181   (4)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("my_owner"."ENDPOINT_LIST"("SHAPE") IS NOT NULL)

Now, the FBI is not being used. The query does a full table scan instead, which isn't what I want.

Question:

How can I utilize a function-based index while selecting additional columns?

CodePudding user response：

create index atn_endpoint_list_idx on infrastr.active_transportation(infrastr.endpoint_list(shape),OBJECTID);

CodePudding user response：

The solution seems to be to add the additional column (OBJECTID) to the index — as a composite index:

create index atn_endpoint_list_idx on
    my_owner.active_transportation(my_owner.endpoint_list(shape),OBJECTID);
--