Oracle SQL force engine to select subquery as a subset of records in memory

Question

I have an Oracle SQL query like so, and it takes 44 seconds to run.

select shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) Shipment_flow
from shipment_line_ovw
where
SHIPMENT_STATE = 'Preliminary' 
and Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) = 'Report picking, Print pick list'

If I take out the last line and do it like this. It only take 0.43 seconds to run, and returns about 100 records.

select shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) Shipment_flow
from shipment_line_ovw
where
SHIPMENT_STATE = 'Preliminary' 

Was thinking that if I tried to make the above a cte or a subquery of 100 records, then filter on the shipment flow column after, it should run fast, like below. but the oracle engine still tries to reselect from the original table and filter by all the records, so it still takes over 40 seconds

with cte as (
select 
shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) Shipment_flow
from shipment_line_ovw
where
SHIPMENT_STATE = 'Preliminary' 
)

select * from cte
where Shipment_flow = 'Report picking, Print pick list';

How to I get this to be a set of records in memory first before filtering so that it runs faster?

Answer 1

Figured it out. Can materialize the records in memory like so.

with cte as (
select /*+ MATERIALIZE*/
shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) Shipment_flow
from shipment_line_ovw
where
SHIPMENT_STATE = 'Preliminary' 
)

select * from cte
where Shipment_flow = 'Report picking, Print pick list';

Runs significantly faster this way, 0.73 seconds.

Figured it out using help from the answer on this question, though the original question is slightly different than mine.

How do you create a temporary table in an Oracle database?

Answer 2

Most likely Oracle was executing the function before it evaluated the SHIPMENT_STATE predicate, and you're function is rather slow. By materializing a CTE you forced evaluation of the SHIPMENT_STATE predicate first, which must have greatly reduced your candidate rows for the function calculation. You could have done the same with a regular inline query block with a NO_MERGE hint. Or if this is a view, the addition or subtraction of one of the predicates in a single block could significantly change the execution plan of the view, even in ways you're not expecting.

The fastest method, however, is neither of these options. Instead, create virtual column using the function and then index it. If shipment_line_ovw is a table:

ALTER TABLE shipment_line_ovw ADD 
(next_step varchar2(50) AS (Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID)) VIRTUAL)
/
CREATE INDEX shipment_line_ovw_idx1 on shipment_line_ovw(shipment_state,next_step)
/

Then simply:

select shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) Shipment_flow
from shipment_line_ovw
where
SHIPMENT_STATE = 'Preliminary' 
and next_step = 'Report picking, Print pick list'

But if, as I'm guessing it might be from the name, shipment_line_ovw is actually a view, you would want to add the virtual column to the underlying base table and expose next_step in the view. That could require a bit of rework to get the predicate to push down in the view if you have any sorting, but it's doable. It's almost always best to query tables directly rather than views for this reason.