PLSQL:在plsql保存的过程中处理临时表是个好主意吗? [英] PLSQL : Is it a good idea to deal with temp tables in plsql saved procedure?

问题描述

打算进行讨论.

我有一个结果表，它是由一个稍微复杂的查询(几个Joins和WHERE子句)生成的，随着我们的进行，我将在过程中多次操作/引用.

I have a result table, that is generated from a slightly complex query(several Joins and WHERE clauses), That I will be manipulating/referencing multiple times in the procedure as we go on.

我唯一能想到的就是将查询结果存储在一个表中，并在过程结束时删除该表.这是可行的选择吗?有什么缺点?更好的选择?

The only thing that i can come up with, is storing the result of the query in a table and dropping the table at the end of the procedure. Is this a viable option? What are the drawbacks? Better alternatives?

推荐答案

在存储过程中创建和删除表几乎总是错误的解决方案.

Creating and dropping tables in stored procedures is almost always the wrong solution.

首先，您刚刚将查询转换为数据库更改；由于多种原因，这是有问题的，但特别是特权升级.其次，DDL可能由于多种原因而失败，因此这种方法引入了危险，而这在纯查询中是不存在的.第三，创建，填充和删除表会增加开销.

Firstly you have just turned a query into a database change; that is problematic for a variety of reasons, but especially privilege escalation. Secondly, DDL can fail for a variety of reasons, so this approach introduces jeopardy which doesn't exist in a pure query. Thirdly, creating, populating and dropping a table adds overhead.

最佳解决方案:只需编写高效的SELECT语句即可.

The best solution: just write an efficient SELECT statement.

其他解决方案，按性能从高到低的顺序:

Alternative solutions, in decreasing order of performance:

• 使用WITH子句来操纵查询流程
• 填充PL/SQL集合
• 在18c私人临时表中
• 全局临时表(是永久数据库表，只是临时的数据).

• using WITH clauses to manipulate the query flow
• populating PL/SQL collections
• in 18c private temporary tables
• global temporary tables (which are permanent database tables, it's just the data which is temporary).

您的问题提出了一种查询初始结果集，然后以编程方式对其进行处理的方案.我的第一个想法是，您应该尝试在纯SQL中执行此操作. Oracle具有广泛的功能库，尤其是分析功能库，您将对查询中可以执行的操作数量感到惊讶.但是，如果您真的需要以过程方式进行工作，则填充和操作数组是下一个最有效的方法.填充全局临时表非常昂贵，因为它需要写入磁盘和从磁盘读取数据.仅当您受会话内存限制或需要在多个过程之间共享数据时，才考虑使用GTT.

Your question poses a scenario of querying an initial result set, then programmatically manipulating it. My first thought is you should try to do this in pure SQL. Oracle has a vast arsenal of functions, not least the analytic functions, and you will be astonished at how much it is possible to do in a query. But if you really need to work in a procedural fashion populating and manipulating arrays is the next most performative approach. Populating a global temporary table is expensive, because it entails writing to and reading from disk. Only consider GTTs if you are constrained by session memory, or you need to share data across multiple procedures.

这篇关于PLSQL:在plsql保存的过程中处理临时表是个好主意吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！