大型数据库表的 sql 优化器
我有一个表，其中有数百万行，我需要加入这些行才能进行选择。响应时间不太好，我该如何提高其响应速度？我尝试过向我选择的列添加索引，有没有可以用来优化 sql 的工具，或者我如何诊断 sql 的瓶颈并改进它？任何建议将不胜感激。 我正在使用 oracle 服务器 10g 并使用 asp.net 作为我的客户端。 还有其他类型的索引对具有数百万行的表有帮助吗？
i have table that has millions of rows that i need to join to do selects. the response time is not so good, how can i improve its response? i have tried adding index to the columns i do select by, is there a tool i can use to optimize the sql or how can i diagnose on the bottlenecks of the sql and improve it? any suggestion would be very appreciated.
i am using oracle server 10g and using asp.net as my client.
is there any other kind of indexing helpful on tables with millions of rows?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可能应该从解释计划开始。
然后编辑你的问题，并发布 SQL 语句和 EXPLAIN PLAN 的输出。
稍后。 。 .
对于这个问题我不会给你提供太多帮助。 269 行，至少 29 个 SELECT、并行查询、远程数据库、外连接（旧式）等等。
我能给你的最好建议是
计划表的列数超过经常被张贴。 COST、CARDINALITY、BYTES 和 TIME 列可能有助于确定调整工作的优先级。
该查询中有 10 次全表扫描。 （查询计划中的“TABLE ACCESS FULL”。）这通常是一个不好的迹象；全表扫描通常需要相对较长的时间来运行。这并不总是一个坏兆头。对小表的完整扫描可能比索引扫描更快。
首先获取查询中 29 个 SELECT 语句中每一个的 EXPLAIN PLAN 输出。如果其中任何一个显示全表扫描，您可以使用 合适的索引。 （Oracle 支持多种不同类型的索引。不要忽视多列索引的机会。）无论如何，EXPLAIN PLAN 输出将帮助您识别 29 个 SELECT 中最慢的一个。
You should probably start with EXPLAIN PLAN.
Then edit your question, and post the SQL statement and the output of EXPLAIN PLAN.
Later . . .
I'm not going to be much help to you on that query. 269 lines, at least 29 SELECTs, parallel queries, remote databases, outer joins (old style), and so on.
The best advice I can give you is
The plan table has more columns than are commonly posted. The columns COST, CARDINALITY, BYTES, and TIME might be useful in prioritizing your tuning effort.
You've got 10 full table scans in that query. ("TABLE ACCESS FULL" in the query plan.) That's usually a bad sign; full table scans often take a relatively long time to run. It's not always a bad sign. A full scan of a tiny table might be faster than an index scan.
Start by getting EXPLAIN PLAN output for each of the 29 SELECT statements in your query. If any of them show a full table scan, you can probably improve their performance with suitable indexes. (Oracle supports many different kinds of indexes. Don't overlook opportunities for multi-column indexes.) In any case, EXPLAIN PLAN output will help you identify the slowest of the 29 SELECTs.
此查询和计划可能存在数以千计的问题，只有本地专家才能真正帮助您。
但无论如何，我注意到的第一件事是你的计划中只有 1/5 使用了并行性。通常，您希望所有步骤并行运行，或者都不并行运行。
如果您的查询仅返回少量数据，则并行性的开销可能不值得。 Oracle 可能需要额外几秒钟的时间来设置并行进程、协调它们并执行其他步骤来优化计划（例如增加动态采样）。串行索引读取可能比并行全表扫描效果更好。您可能需要更改表的 DEGREE，或使用 NOPARALLEL 提示。
如果您的查询返回大量数据，您可能希望使用尽可能多的并行哈希联接来有效地联接所有内容。对于非常大的查询，当 Oracle 低估基数并使用嵌套循环和索引时，通常会出现最差的性能。查看您的基数，并找到计划中估计值大大低于实际值的第一部分，这将使您更接近问题。
There are potentially thousands of problems that may exist with this query and plan, only a local expert can truly help you.
But for what it's worth, the first thing I noticed is that only 1/5th of your plan uses parallelism. Normally you want to have all of the steps run in parallel or none of them run in parallel.
If your query only returns a small amount of data the overhead of parallelism probably isn't worth it. It may take a few extra seconds for Oracle to set up the parallel processes, coordinate them, and perform additional steps to optimize the plan (e.g. increase dynamic sampling). A seriel index read would probably work much better than a parallel full table scan. You may need to change the DEGREE of a table, or use a NOPARALLEL hint.
If your query returns a large amount of data you'll probably want to use as many parallel hash joins as possible to efficiently join everything. For really large queries, the worst performance usually occurs when Oracle underestimates the cardinality and uses nested loops and indexes. Look at your cardinality, and find the first part in the plan where the estimate is drastically lower than the actual, that will get you closer to the problem.