我有一个像这样的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';
我如何得到这是一组记录在内存过滤之前,使它运行得更快?
明白了。可以像这样将内存中的记录物化。
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数据库中创建临时表?很可能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
。如果您有任何排序,这可能需要稍微重做才能让谓词在视图中下推,但这是可行的。因为这个原因,直接查询表总是比直接查询视图要好。