大型表的SQL优化器 [英] sql Optimizer for Large Db Table
问题描述
我有一个具有数百万行的表，我需要将这些行连接起来以进行选择.响应时间不是很好，如何改善响应?我尝试将索引添加到我选择的列中，是否可以使用某个工具来优化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?
推荐答案
您可能应该以
使用EXPLAIN PLAN语句确定执行计划Oracle
数据库随后执行指定的SQL语句.这个说法
将描述执行计划每个步骤的行插入到
指定表.您也可以发出EXPLAIN PLAN语句作为一部分
SQL跟踪工具.
Use the EXPLAIN PLAN statement to determine the execution plan Oracle
Database follows to execute a specified SQL statement. This statement
inserts a row describing each step of the execution plan into a
specified table. You can also issue the EXPLAIN PLAN statement as part
of the SQL trace facility. 此语句还确定执行该语句的成本.如果
在表上定义了所有域索引，然后定义了用户定义的CPU和
I/O成本也将被插入. This statement also determines the cost of executing the statement. If
any domain indexes are defined on the table, then user-defined CPU and
I/O costs will also be inserted. 然后编辑您的问题，并发布SQL语句和EXPLAIN PLAN的输出. Then edit your question, and post the SQL statement and the output of EXPLAIN PLAN. 稍后. . 在该查询上，我不会对您有太大帮助. 269行，至少29个SELECT，并行查询，远程数据库，外部联接(旧样式)等等. 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 计划表具有比通常发布的列更多. COST，CARDINALITY，BYTES和TIME列可能在确定调整工作的优先级时很有用. 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. 在该查询中，您进行了10次全表扫描. (查询计划中的"TABLE ACCESS FULL".)通常是一个不好的信号；全表扫描通常需要相对较长的时间才能运行.这不是总是的坏兆头.对小型表进行完全扫描可能比对索引进行扫描要快. 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. 首先获取查询中29条SELECT语句中每条的EXPLAIN PLAN输出.如果其中任何一个显示了全表扫描，则可以使用合适的索引. (Oracle支持许多不同类型的索引.不要忽略使用多列索引的机会.)无论如何，EXPLAIN PLAN输出将帮助您确定29个SELECT语句中最慢的一个. 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. 这篇关于大型表的SQL优化器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
