Oracle选择查询性能
我正在开发一个应用程序。由于处于初始阶段，表中的记录数量并不多，但后期同一张表中的记录将达到100万条左右。
我想知道在编写选择查询时应该考虑哪些要点，该查询将从表中获取大量数据，这样就不会降低性能。
I am working on a application. It is in its initial stage so the number of records in table is not large, but later on it will have around 1 million records in the same table.
I want to know what points I should consider while writing select query which will fetch a huge amount of data from table so it does not slow down performance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
第一条规则：
除非您要显示大量数据中的每一项，否则不要获取它。 DBMS 和应用程序之间的通信（相对）较慢，因此请尽可能避免这种情况。它并没有慢到您不应该使用 DBMS 或类似的东西，但是如果您可以减少 DBMS 和应用程序之间流动的数据量，总体性能通常会提高。
通常，一种简单的方法是仅列出应用程序中实际需要的列，而不是在仅使用现有 24 列中的 4 列时使用“SELECT *”来检索所有列。
第二条规则：
尽可能减少 DBMS 必须完成的工作。它很忙，并且通常在任何给定时间代表许多人都很忙。如果您可以减少 DBMS 处理查询所需的工作量，那么每个人都会更高兴。
考虑诸如确保表上有适当的索引之类的事情 - 不要太少，也不要太多。如果设计得当，索引可以极大地提高许多查询的性能。但请始终记住，每个索引都必须维护，因此当给定表上有更多索引需要管理时，插入、删除和更新会变慢。
（我应该提到：这些建议都不是特定于 Oracle 的 - 您可以将其应用于任何 DBMS。）
First rule:
Unless you are going to display every single one of the items in the huge amount of data, do not fetch it. Communication between the DBMS and the application is (relatively) slow, so avoid it when possible. It isn't so slow that you shouldn't use the DBMS or anything like that, but if you can reduce the amount of data flowing between DBMS and application, the overall performance will usually improve.
Often, one easy way to do this is to list only those columns you actually need in the application, rather than using 'SELECT *' to retrieve all columns when you'll only use 4 of the 24 that exist.
Second rule:
To the extent possible, minimize the work that the DBMS has to do. It is busy, and typically it is busy on behalf of many people at any given time. If you can reduce the amount of work that the DBMS has to do to process your query, everyone will be happier.
Consider things like ensuring you have appropriate indexes on the table - not too few, not too many. Designed judiciously, indexes can greatly improve the performance of many queries. Always remember, though, that each index has to be maintained, so inserts, deletes and updates are slower when there are more indexes to manage on a given table.
(I should mention: none of this advice is specific to Oracle - you can apply it to any DBMS.)
为了获得良好的数据库性能，您需要牢记很多事情。首先是设计，这里您应该主要考虑规范化和非规范化（拆分表，但仍然不需要那么多性能繁重的联接）。
在性能方面经常需要进行大量的调整。然而，80% 的性能是由 SQL 代码决定的。以下是一些可能对您有帮助的链接。
http://www.smart-soft.co。 uk/Oracle/oracle-performance-tuning-part7.htm
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ
To get good performance with a database there is a lot of things you need to have in mind. At first, it is the design, and here you should primary think about normalization and denormalization (split up tables but still not as much as performance heavy joins are required).
There are often a big bunch of tuning when it comes to performance. However, 80% of the performance is determined from the SQL-code. Below are some links that might help you.
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part7.htm
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ
需要记住的几点：
贾森
A few points to remember:
Jason