Oracle SQL强制引擎选择子查询作为内存中记录的子集

Question

我有一个像这样的Oracle SQL查询，它需要44秒来运行。

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'

如果我去掉最后一行，像这样做。运行只需要0.43秒，并返回大约100条记录。

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

我想，如果我试图使上面的一个cte或100条记录的子查询，然后对发货流列进行过滤，它应该运行得很快，如下所示。但是oracle引擎仍然尝试从原始表中重新选择并过滤所有记录，所以它仍然需要超过40秒

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';

我如何得到这是一组记录在内存过滤之前，使它运行得更快?

Answer 1

明白了。可以像这样将内存中的记录物化。

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';

以这种方式运行明显更快，0.73秒。

从这个问题的答案中找到了答案，尽管最初的问题与我的问题略有不同。

如何在Oracle数据库中创建临时表?

Answer 2

很可能Oracle在计算SHIPMENT_STATE谓词之前执行了该函数，而您的函数相当慢。通过实现CTE，您首先强制对SHIPMENT_STATE谓词进行求值，这一定大大减少了用于函数计算的候选行。您可以使用具有NO_MERGE提示的常规内联查询块完成相同的操作。或者，如果这是一个视图，在单个块中增加或减少一个谓词可能会显著改变视图的执行计划，甚至以您意想不到的方式。

然而，最快的方法不是这两种方法。相反，可以使用该函数创建虚拟列，然后对其建立索引。如果shipment_line_ovw是表:

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)
/

那么简单:

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'

但是，如果，正如我猜测它可能是从名称，shipment_line_ovw实际上是一个视图，您将希望将虚拟列添加到底层基表并在视图中公开next_step。如果您有任何排序，这可能需要稍微重做才能让谓词在视图中下推，但这是可行的。因为这个原因，直接查询表总是比直接查询视图要好。