视图上的查询运行速度比直接查询慢 - 在 Oracle 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20543286/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query on View is running slower than direct query - in Oracle
提问by Madhusudhan Dollu
I tried creating view with parameters to get the data dynamically.
(I cant use WHERE condition as the select statement itself changes based on the parameters)
我尝试使用参数创建视图以动态获取数据。
（我不能使用 WHERE 条件，因为 select 语句本身会根据参数发生变化）
For this, I wrote a procedure which returns me the required data as oracle object type.
为此，我编写了一个过程，它将所需的数据作为 oracle 对象类型返回给我。
FUNCTION get_data(p_pk_id NUMBER, p_tab_type VARCHAR2)
RETURN M_TYPE_DATA_TAB
AS
v_table_collection M_TYPE_DATA_TAB;
BEGIN
-- my sql query which will change based on the params
RETURN v_table_collection;
END;
and I run the select query as follows.
我按如下方式运行选择查询。
SELECT * FROM TABLE(get_data(12345, 'MYTAB'));
which gives me data in less than 1 sec.
这给了我不到 1 秒的数据。
for the same select statement I have created a view as
对于相同的选择语句，我创建了一个视图
CREATE OR REPLACE VIEW my_view
AS SELECT * FROM TABLE(get_data(12345, 'MYTAB'));
if I query the view
如果我查询视图
SELECT * FROM my_view
it takes more than 6 secto get the same data.
获取相同数据需要6 秒以上。
Any idea why there is that much big difference to query the same data.
Will the veiw take more time than a normal query?
知道为什么查询相同的数据会有这么大的差异。
veiw 会比普通查询花费更多时间吗？
采纳答案by Tomás
The execution plan on each statement will give you more detail on what is happening. Try using some of the provided oracle tools for investigating what exactly is happening in each case.
每个语句的执行计划将为您提供有关正在发生的事情的更多详细信息。尝试使用一些提供的 oracle 工具来调查每种情况下到底发生了什么。
Try doing a:
尝试做一个：
SELECT/*+gather_plan_statistics*/ * FROM TABLE(get_data(12345, 'MYTAB'));
then do a:
然后做一个：
SELECT/*+gather_plan_statistics*/ * FROM my_view
These will give you the actual execution plan for the statements.
这些将为您提供语句的实际执行计划。
By the way, you will need select on the V_$SQL_PLAN and V_$SQL views to use the gather_plan_statistics as above.
顺便说一下，您需要在 V_$SQL_PLAN 和 V_$SQL 视图上选择以使用上述的gather_plan_statistics。
回答by Guntram Blohm supports Monica
Google for "explain plan", and check the execution plan for both statements. Without the complete function, it's difficult to say anything. But i'd assume that the optimizer does some "generic optimizing" on the view, and when you select from the view, that generic optimizing is used. When you select directly, without the view, the optimizer considers the internals of your function as well.
谷歌搜索“解释计划”，并检查两个语句的执行计划。没有完整的功能，很难说什么。但我假设优化器对视图进行了一些“通用优化”，当您从视图中进行选择时，将使用通用优化。当您在没有视图的情况下直接选择时，优化器也会考虑您的函数的内部结构。
Try explain plan on different statements selecting from the view. Is the "internal part", that executes the function, the same in each case?
尝试对从视图中选择的不同语句进行解释计划。执行功能的“内部部分”在每种情况下都相同吗？
