SQL 在表中选择`n`最后插入的记录 - oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1827948/
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
Select `n` last inserted records in table - oracle
提问by zendar
Table has surrogate primary key generated from sequence. Unfortunately, this sequence is used for generating keys for some other tables (I did not designed it and I cannot change it).
表具有从序列生成的代理主键。不幸的是，这个序列用于为其他一些表生成键（我没有设计它，我不能改变它）。
What is the fastest way to select last ninserted records in Oracle, ordered by id in descending order (last inserted on top)?
n在 Oracle 中选择最后插入的记录的最快方法是什么，按 id 降序排序（最后插入在顶部）？
nis some relatively small number - number of records to display on page - probably not bigger than 50.
n是一些相对较小的数字 - 要在页面上显示的记录数 - 可能不大于 50。
Table now has 30.000.000 records with 10-15 thousands of new records daily.
表现在有 30.000.000 条记录，每天有 10-15 万条新记录。
Database is Oracle 10g.
数据库是Oracle 10g。
Edit:
In answer to one comment: This question was motivated with execution plan for query:
编辑：
回答一条评论：这个问题的动机是查询执行计划：
select * from MyTable order by primarykeyfield desc
Execution plan was:
执行计划是：
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | TABLE ACCESS FULL| MyTable |
---------------------------------------------
I was surprised that Oracle wants to perform full table scan and sorting when it has index on sort field.
我很惊讶 Oracle 在排序字段上有索引时想要执行全表扫描和排序。
Query from accepted answer uses index and avoids sort.
从接受的答案查询使用索引并避免排序。
Edit 2:
Re. APC's comment: Sorting was part that surprised me. I expected that Oracle would use index to retrieve rows in expected order. Execution plan for query:
编辑2：
重新。APC 的评论：排序是让我感到惊讶的部分。我预计 Oracle 会使用索引以预期的顺序检索行。查询的执行计划：
select * from (select * from arh_promjene order by promjena_id desc) x
where rownum < 50000000
uses index instead of full table access and sort (notice condition rownum < 50.000.000- this is way more than number of records in table and Oracle knows that it should retrieve all records from table). This query returns all rows as first query, but with following execution plan:
使用索引而不是全表访问和排序（注意条件rownum < 50.000.000- 这比表中的记录数多得多，Oracle 知道它应该从表中检索所有记录）。此查询将所有行作为第一个查询返回，但具有以下执行计划：
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
| 3 | TABLE ACCESS BY INDEX ROWID| MyTable |
| 4 | INDEX FULL SCAN DESCENDING| SYS_C008809 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<50000000)
It was unusual to me that Oracle is creating different execution plans for these two queries that essentially return same result set.
Oracle 为这两个基本上返回相同结果集的查询创建不同的执行计划对我来说很不寻常。
Edit 3:Re Amoq's comment:
编辑 3：Re Amoq 的评论：
Oracle doesn't know that 50M is greater than the number of rows. Sure, it has statistics, but they could be old and wrong - and Oracle would never allow itself to deliver an incorrect result only because the statistics are wrong.
Oracle 不知道 50M 大于行数。当然，它有统计数据，但它们可能是陈旧的和错误的——而且甲骨文绝不会允许自己仅仅因为统计数据是错误的而提供不正确的结果。
Are you sure? In Oracle versions up to 9 it was recommended to manually refresh statistics from time to time. Since version 10 Oracle automatically updates statistics. What's the use of statistics data if Oracle does not use it for query optimization?
你确定吗？在 Oracle 版本高达 9 中，建议不时手动刷新统计信息。从版本 10 Oracle 自动更新统计信息。如果 Oracle 不将其用于查询优化，那么统计数据有什么用？
回答by Donnie
Use ROWNUM:
使用ROWNUM：
select
*
from
(
select
*
from
foo
order by
bork
) x
where
ROWNUM <= n
Note that rownumis applied beforesorting for a subquery, that's why you need the two nested queries, otherwise you'll just get nrandom rows.
请注意，在对子查询进行排序之前rownum应用，这就是您需要两个嵌套查询的原因，否则您只会得到随机行。n
回答by Hans-Peter St?rr
In cases where you don't have a strictly increasing field, you could also use ORA_ROWSCN(system change number) as an approximation of this.
如果您没有严格递增的字段，您还可以使用ORA_ROWSCN（系统更改编号）作为近似值。
select * from (select * from student order by ORA_ROWSCN desc) where rownum<10
Caution: this is not exact, since Oracle records only one SCN per block, not per row. Also it seems to do a full table scan - probably oracle is not smart enough to optimize this kind of sort. So this might not be a good idea for production use.
注意：这并不准确，因为 Oracle 每个块只记录一个 SCN，而不是每行。它似乎也进行了全表扫描 - 可能 oracle 不够聪明，无法优化这种排序。所以这对于生产用途来说可能不是一个好主意。
回答by cjard
Will it be viewed many more times than it is updated? How about keeping another table of the IDs of the last N inserted rows (use a trigger to delete the smallest ID from this table and add a new row with the current-inserted).
浏览次数会比更新次数多很多吗？如何保留最后 N 个插入行的 ID 的另一个表（使用触发器从该表中删除最小的 ID，并添加一个带有当前插入的新行）。
You now have a table that records the IDs of the last N inserted rows. Any time you want the N, just join it to the main table. If N changes, pick the max it can be, and then filter it after... of course you may find it not so fast for your app (maintenance of this table may negate any performance gain)
您现在有一个记录最后 N 个插入行的 ID 的表。任何时候您想要 N，只需将其加入主表即可。如果 N 发生变化，请选择它可以达到的最大值，然后在之后对其进行过滤……当然，您可能会发现它对您的应用程序来说不是那么快（维护此表可能会抵消任何性能提升）
回答by vinoth arasan
This may help you if you don't know the name of the fields or anything other than table name....
如果您不知道字段名称或表名以外的任何内容，这可能会对您有所帮助....
select * from (
select * from(
select rownum r,student.* from student where rownum<=(
select max(rownum) from student
)
) order by r desc
) where r<=10;
回答by shank
Try doing a index_desc hint
尝试做一个 index_desc 提示
select /*+ index_desc(MyTable,<PK_index>) */ * from MyTable order by primarykeyfield desc
