如何降低 select 语句的成本？
我在 Oracle 10g 中有一个表，其中约有 51 列和 2500 万条记录。当我在表上执行一个简单的选择查询来提取 3 列时，我得到的成本太高了，大约 182k。所以我需要降低成本效应。有什么可能的方法来减少它吗？
查询：
select a,b,c
from X
a - char
b - varchar2
c - varchar2
TIA
I have a table in oracle 10g with around 51 columns and 25 Million number of records in it. When I execute a simple select query on the table to extract 3 columns I am getting the cost too high around 182k. So I need to reduce the cost effect. Is there any possible way to reduce it?
Query:
select a,b,c
from X
a - char
b - varchar2
c - varchar2
TIA
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在这种情况下，如果不知道为什么需要查询 2500 万条记录，就很难提供好的建议。正如@Ryan 所说，通常你会有一个 WHERE 子句；或者，也许您正在将结果提取到另一个表或其他表中？
覆盖索引（即在 a、b、c 上）可能是对性能产生影响的唯一方法 - 然后查询可以进行快速完整索引扫描，并且每个检索块都会获得更多记录。
In cases like this it's difficult to give good advice without knowing why you would need to query 25 million records. As @Ryan says, normally you'd have a WHERE clause; or, perhaps you're extracting the results into another table or something?
A covering index (i.e. over a,b,c) would probably be the only way to make any difference to the performance - the query could then do a fast full index scan, and would get many more records per block retrieved.
好吧...如果您知道您只需要这些值的子集，那么在其中添加 WHERE 子句显然会有所帮助。如果您确实需要全部 2500 万条记录，并且该表已正确建立索引，那么我想说您实际上无能为力。
Well...if you know you only need a subset of those values, throwing a WHERE clause on there would obviously help out quite a bit. If you truly need all 25 million records, and the table is properly indexed, then I'd say there's really not much you can do.
是的，正如杰弗里·坎普所说，更好地说明选择的目的。
如果正常选择，您只需要为您的字段提供索引，这通常是您可以做的，提供索引上的表统计信息（DBMS_STATS.GATHER_TABLE_STATS），检查每个字段的统计信息以确保您的索引是正确的（阅读：http://bit.ly/qR12Ul）。
如果需要加载到另一个表，可以使用游标，限制每次执行的记录，并通过批量插入（FORALL技术）加载到表中。
Yes, better telling the purpose of the select as jeffrey Kemp said.
If normal select, you just need to give index to your fields that mostly you can do, provide table statistic on index (DBMS_STATS.GATHER_TABLE_STATS), check the statistic of each field to be sure your index is right (Read: http://bit.ly/qR12Ul).
If need to load to another table, use cursor, limit the records of each executing and load to the table via the bulk insert (FORALL technique).