主页 / dba / 问题 / 245405
Accepted
0xCAFEBABE
Asked: 2019-08-16 02:21:11 +0800 CST

快速获取少量行

  • 772

我不确定我的 SQL 是否有问题或问题出在 Oracle 上。我们在一个 Web 应用程序上有一个函数，它需要显示一个包含大量条目（以百万计）的表格。但是，显示一次只能获取 500 行。我的猜测是这应该非常快。

这个想法是做这样的事情：

SELECT TOP 500 <tablename> WHERE <where-clause>

WHERE 子句完全被索引覆盖。但是，Oracle 不提供TOPor 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)
oracle oracle-12c

1 个回答

  1. Best Answer

    操作的要点WINDOW NOSORT STOPKEY是在达到所需的行数（不排序）后停止子操作。操作与此COUNT STOPKEY类似。

    所以不，数据库不会读取整个表，上面的计划并不意味着。

    创建一个包含大量行的表：

    SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

23711 rows created.

SQL> /

47422 rows created.

SQL> /

94844 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
    189688

SQL> exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

    选择它的 10 行：

    SQL> select /*+ gather_plan_statistics */ owner, object_name from t1 fetch first 10 rows only;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            OBJ$
...
10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  5nv3z7ctvxamm, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner, object_name from t1 fetch
first 10 rows only

Plan hash value: 906235818

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  VIEW                  |      |      1 |     10 |     10 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY|      |      1 |     10 |     10 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |     10 |     10 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=10)

    如您所见，数据库没有读取整个表，它估计正好有 10 行，并且在检索到正好 10 行后停止读取表，如请求。

    好的，这是一个更新，因为您已经更改了示例。

    SQL> delete from t1 where object_id is null;

8 rows deleted.

SQL> alter table t1 modify (object_id not null);

Table altered.

SQL> create index i1 on t1(object_id);

Index created.

SQL> select /*+ gather_plan_statistics */ owner, object_name from t1 order by object_id fetch first 10 rows only;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            C_OBJ#
...
10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  9rj1zb98w8srh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner, object_name from t1 order
by object_id fetch first 10 rows only

Plan hash value: 4198281070

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |     10 |00:00:00.01 |      13 |
|*  1 |  VIEW                         |      |      1 |     10 |     10 |00:00:00.01 |      13 |
|*  2 |   WINDOW NOSORT STOPKEY       |      |      1 |     10 |     10 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |      1 |    189K|     10 |00:00:00.01 |      13 |
|   4 |     INDEX FULL SCAN           | I1   |      1 |     10 |     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=10)

    再一次，数据库没有读取整个表，甚至没有读取整个索引，这只是前 10 行所需要的。请注意，我在NOT NULL索引列上创建了一个约束。默认情况下，索引不包含空值，因此如果索引列中有空值，或者您只是没有NOT NULL约束，则不会使用索引并且将读取整个表：

    SQL> alter table t1 modify (object_id null);

Table altered.

SQL> select /*+ gather_plan_statistics */ owner, object_name from t1 order by object_id fetch first 10 rows only;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            C_OBJ#
...
10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  9rj1zb98w8srh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner, object_name from t1 order
by object_id fetch first 10 rows only

Plan hash value: 2433988517

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |     10 |00:00:00.05 |    3216 |       |       |          |
|*  1 |  VIEW                    |      |      1 |     10 |     10 |00:00:00.05 |    3216 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |    189K|     10 |00:00:00.05 |    3216 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL     | T1   |      1 |    189K|    189K|00:00:00.02 |    3216 |       |       |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=10)

    但是如果我“帮助”数据库并告诉它我不需要空值（where object_id is not null），则可以再次使用索引：

    SQL> select /*+ gather_plan_statistics */ owner, object_name from t1 where object_id is not null order by object_id fetch first 10 rows only;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            C_OBJ#
...
10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  24hf60f74stv3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner, object_name from t1 where
object_id is not null order by object_id fetch first 10 rows only

Plan hash value: 4198281070

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |     10 |00:00:00.01 |      13 |
|*  1 |  VIEW                         |      |      1 |     10 |     10 |00:00:00.01 |      13 |
|*  2 |   WINDOW NOSORT STOPKEY       |      |      1 |     10 |     10 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |      1 |    189K|     10 |00:00:00.01 |      13 |
|*  4 |     INDEX FULL SCAN           | I1   |      1 |     10 |     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=10)
   4 - filter("OBJECT_ID" IS NOT NULL)
    • 1

相关问题