Oracle SQL 优化：SQL 查询需要很长时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/288007/
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
Oracle SQL Optimization: SQL Query taking very long time
提问by David Aldridge
SELECT DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
This seems to be the primary culprit in why I cannot export these records to a textfile in my development environment. Is there any way I can get this query to run quicker. It returns roughly 2000+ lines of text.
这似乎是我无法在开发环境中将这些记录导出到文本文件的罪魁祸首。有什么办法可以让这个查询运行得更快。它返回大约 2000 多行文本。
回答by user34850
The solution is simple.
解决方法很简单。
Create an index on (code, licensing_no) and an index on (l_code, licensing_no) to fetch records faster. Do the 'beautification' piece later in the application or simply in external wrapper like this:
在 (code, license_no) 上创建索引并在 (l_code, license_no) 上创建索引以更快地获取记录。稍后在应用程序中或简单地在外部包装器中执行“美化”部分，如下所示：
SELECT 'LRS-TECH 1'
|| RPAD (code, 7)
|| RPAD ('APPTYPE', 30)
|| RPAD (licensing_no, 30)
|| RPAD (' ', 300) AS RECORD
FROM (SELECT DISTINCT code, licensing_no
FROM apps
WHERE l_code = '1000' AND licensing_no IS NOT NULL)
回答by David Aldridge
You cannot diagnose this problem unless you know how the query is being optimised.
除非您知道如何优化查询，否则您无法诊断此问题。
Try this:
尝试这个：
explain plan for SELECT DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
/
select * from table(dbms_xplan.display)
/
Now, try this also ... it will help you detect a statistics problem:
现在，也试试这个……它会帮助你检测统计问题：
explain plan for SELECT /*+ dynamic_sampling(4) */ DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
/
select * from table(dbms_xplan.display)
/
Please update your original post with the results of those.
请使用这些结果更新您的原始帖子。
回答by digitalsanctum
If you don't already have indexes on L_code and licensing_no columns, I would try that.
如果您还没有关于 L_code 和 license_no 列的索引，我会尝试这样做。
回答by dkretz
If there are many records with L_code = '1000' and the only additional test is for NOT NULL, you probably have a cardinality problem. Indexes have a hard time selecting on NULL or not.
如果有很多 L_code = '1000' 的记录并且唯一的附加测试是 NOT NULL，则您可能有基数问题。索引很难选择是否为 NULL。
The number of rows returned is unimportant - it's the number of rows examined that's the question.
返回的行数并不重要——问题在于检查的行数。
What indexes are there?
有哪些指标？
回答by Leigh Riffel
As most of the answers here have indicated, your question sounds like an optimization question. Your later answer changes the nature of the question significantly. I suggest posting it as a new question or modifying the original question to ask what you really want to know.
正如这里的大多数答案所表明的那样，您的问题听起来像是一个优化问题。您稍后的回答显着改变了问题的性质。我建议将其作为新问题发布或修改原始问题以询问您真正想知道的内容。
I can't help you on the ASP/ASPX issue, but if this were an optimization question I'd suggest creating a function based index for a new WHERE clause as follows:
我无法帮助您解决 ASP/ASPX 问题，但如果这是一个优化问题，我建议为新的 WHERE 子句创建一个基于函数的索引，如下所示：
SELECT DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE DECODE(L_code,'1000',licensing_no,NULL) IS NOT NULL;
A function based index on DECODE(L_code,'1000',licensing_no,NULL) would include all the records you want to return. If you needed even more speed you could create a materialized view on the results of the query, but that would be more of a last ditch effort.
DECODE(L_code,'1000',licensing_no,NULL) 上基于函数的索引将包括您要返回的所有记录。如果您需要更高的速度，您可以在查询结果上创建一个物化视图，但这更像是最后的努力。
回答by hamishmcn
I wonder if it is because the oracle is using a different index (or not at all) for the query from the aspx page.
I would suggest updating the statistics on the table to see if that makes any difference.
See this questionfor how to do it (and the comments that 'calculate statistics' is obsolete, replaced by a package instead)
我想知道是不是因为 oracle 对来自 aspx 页面的查询使用了不同的索引（或根本没有）。
我建议更新表格上的统计数据，看看这是否有什么不同。
请参阅此问题以了解如何执行此操作（以及“计算统计数据”的注释已过时，取而代之的是一个包）
回答by hamishmcn
Hmmm... getting rid of DISTINCT may help considering that code is the PRIMARY KEY. I don't think it is what is causing the major processing problems. If believe the RPAD, etc. is causing most of the query delay.
嗯......摆脱 DISTINCT 可能有助于考虑代码是主键。我不认为这是导致主要处理问题的原因。如果认为 RPAD 等导致了大部分查询延迟。
The indexes mainly ASCEND the CODE field. That's the only relevant indexes on the table.
索引主要是 ASCEND CODE 字段。这是表中唯一相关的索引。
回答by James
You could prebuild the RECORD derived value in a secondary table, view or column using a trigger and query that instead of building it on the fly if the table is frequently queried.
您可以使用触发器和查询在辅助表、视图或列中预先构建 RECORD 派生值，而不是在频繁查询表时即时构建它。
It might help to know the size of the table. If you've got a large column in there, or a lot of records, it could be something IO or cache related.
了解桌子的大小可能会有所帮助。如果你有一个大列，或者很多记录，它可能与 IO 或缓存相关。
回答by James
I'm sorry to everyone looking at this SQL, but this is a mindboggling server problem or something. The scenario seems to have drawn itself out,and I believe it's a data availability problem as to where the DB resides, but somebody may be able to give me some insight.
我很抱歉看到这个 SQL 的每个人，但这是一个令人难以置信的服务器问题或其他什么。该场景似乎已经引出了自己，我相信这是关于数据库所在位置的数据可用性问题，但有人可能会给我一些见解。
On my Localhost, I run the code, works instantaneously. I export the data it gives me from a datatable to a textfile in less than a second... done.
在我的本地主机上，我运行代码，立即工作。我在不到一秒钟的时间内将它给我的数据从数据表导出到文本文件......完成。
On our development environment, the same page is in old ASP. Half our site is in classic ASP as we convert to .NET. The problem seems to be that on the DEV site, the classic ASP page works perfectly, quickly and done in less than a second. When I uploaded the newly converted ASPX file, it hung for about 30 seconds on that query.
在我们的开发环境中，相同的页面在旧的 ASP 中。当我们转换为 .NET 时，我们的网站有一半是经典的 ASP。问题似乎是在 DEV 站点上，经典的 ASP 页面可以完美、快速且在不到一秒钟的时间内完成。当我上传新转换的 ASPX 文件时，它在该查询上挂了大约 30 秒。
On Localhost, the old classic ASP hangs for about 30 seconds.
在 Localhost 上，旧的经典 ASP 挂起大约 30 秒。
So, I have a vice versa problem here in that the classic ASP doesn't hang on the DEV site, but on my machine while my own ASPX page hangs on the DEV site, but NOT on my machine. The difference is that I believe the data is being pulled in my own code on the DEV site, while the ASP page is pulling the data from code that resides on an old DEV site server that ports the results to the DEV site. So, technically, the code isn't being run on the same server. The classic ASP code is on our old site server.
所以，我有一个反之亦然的问题，因为经典的 ASP 不会挂在 DEV 站点上，而是挂在我的机器上，而我自己的 ASPX 页面挂在 DEV 站点上，但不在我的机器上。不同之处在于我相信数据是在我自己的 DEV 站点上的代码中提取的，而 ASP 页面是从驻留在旧的 DEV 站点服务器上的代码中提取数据，该服务器将结果移植到 DEV 站点。因此，从技术上讲，代码不是在同一台服务器上运行的。经典的 ASP 代码位于我们的旧站点服务器上。
I'm assuming there is some sort of speed issue or server issue between the two sites.
我假设两个站点之间存在某种速度问题或服务器问题。
回答by JK.
Get rid of the DISTINCT.
摆脱 DISTINCT。
