我不确定我的 SQL 是否有问题或问题出在 Oracle 上。我们在一个 Web 应用程序上有一个函数,它需要显示一个包含大量条目(以百万计)的表格。但是,显示一次只能获取 500 行。我的猜测是这应该非常快。
这个想法是做这样的事情:
SELECT TOP 500 <tablename> WHERE <where-clause>
WHERE 子句完全被索引覆盖。但是,Oracle 不提供TOP
or LIMIT
,因此使用的替代方法似乎是ROW_NUMBER()
or OFFSET/FETCH FIRST。
但是,在检查执行计划时,似乎即使对于最简单的查询,Oracle 也假装它需要在应用ROW_NUMBER()
和过滤之前读取完整的表。FETCH FIRST 也是如此,因为 Oracle 似乎将 FETCH FIRST 转换为一个ROW_NUMBER()
构造:
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=500)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=500)
例子:
SELECT ID FROM <tablename> FETCH FIRST 500 ROWS ONLY;
执行计划:
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1643K| 5581M| 34399 (1)| 00:00:03 |
|* 1 | VIEW | | 1643K| 5581M| 34399 (1)| 00:00:03 |
|* 2 | WINDOW NOSORT STOPKEY| | 1643K| 1234M| 34399 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL | TABLENAME | 1643K| 1234M| 34399 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------
只有一行会变得更加极端,因为执行计划是相同的。这些成本对于导致行数非常少的操作来说是残酷的。
有没有办法阻止 Oracle 在返回任何内容之前读取所有记录?
编辑1:
由于下面的@Balasz-Papp 得到完全不同的执行计划、基数和成本,我将在此处记录一个完整的示例。
这是一个刚刚组合在一起的查询(有一个仅覆盖 id 字段的索引):
SELECT * FROM <tablename> ORDER BY id FETCH FIRST 500 ROWS ONLY;
执行计划如下所示(使用 SQL Developer 和 TOAD 收集):
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1643K| 5601M| | 204K (1)| 00:00:16 |
|* 1 | VIEW | | 1643K| 5601M| | 204K (1)| 00:00:16 |
|* 2 | WINDOW SORT PUSHED RANK| | 1643K| 1234M| 1351M| 204K (1)| 00:00:16 |
| 3 | TABLE ACCESS FULL | TABLE_NAME | 1643K| 1234M| | 34399 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=500)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "EVENT"."ID")<=500)
执行此语句需要 14 分钟多一点。这台机器的性能不是很好,但是选择并返回 500 行与选择近 6GB 中的 160 万行并返回前 500 行之间的区别似乎是正在发生的事情。
编辑2:
我尝试复制另一种方式来生成执行计划,在我的示例中使用这些语句:
select /*+ gather_plan_statistics */ * from TABLE_NAME order by id fetch first 500 rows only;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
生成的计划如下所示:
Plan hash value: 4184280406
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 500 |00:00:40.11 | 92135 | | | |
|* 1 | VIEW | | 1 | 1643K| 500 |00:00:40.11 | 92135 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 1643K| 500 |00:00:40.11 | 92135 | 549K| 408K| 487K (0)|
| 3 | TABLE ACCESS FULL | MTR_EVENTPROTOCOL_TA | 1 | 1643K| 1645K|00:00:36.65 | 92135 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=500)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "EVENT"."ID")<=500)
编辑 3:按要求添加按 ID 进行简单提取的执行计划:
Plan hash value: 612488378
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 840 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MTR_EVENTPROTOCOL_TA | 1 | 840 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | MTR_EVENTPROTOCOL_PK | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=23417479)
操作的要点
WINDOW NOSORT STOPKEY
是在达到所需的行数(不排序)后停止子操作。操作与此COUNT STOPKEY
类似。所以不,数据库不会读取整个表,上面的计划并不意味着。
创建一个包含大量行的表:
选择它的 10 行:
如您所见,数据库没有读取整个表,它估计正好有 10 行,并且在检索到正好 10 行后停止读取表,如请求。
好的,这是一个更新,因为您已经更改了示例。
再一次,数据库没有读取整个表,甚至没有读取整个索引,这只是前 10 行所需要的。请注意,我在
NOT NULL
索引列上创建了一个约束。默认情况下,索引不包含空值,因此如果索引列中有空值,或者您只是没有NOT NULL
约束,则不会使用索引并且将读取整个表:但是如果我“帮助”数据库并告诉它我不需要空值(
where object_id is not null
),则可以再次使用索引: