Oracle查看性能与rownum [英] Oracle view performance with rownum

问题描述

我使用的是Oracle 10g，我有一个视图，连接两个大表（百万记录）。我想为用户选择有限的示例数据，例如：

I am using Oracle 10g, and I have a view that joins two large tables (millions of records). I am trying to select a limited "sample" of the data for the user like this:

select * from VIEW_NAME where ROWNUM < 5; 

这是非常慢，我认为不应该，因为我只需要几行结果，所以Oracle不应该计算完整的连接。

It is very slow, and I think it should not be, because I just need a few rows of the result, so Oracle should not calculate the full join.

要求是用户应该能够以交互方式指定返回的行数（从结果中确切地说，哪些行不重要）。有什么办法实现这个吗？ （使用rownum或其他方法）

The requirement is that the user should be able to specify interactively the number of returned rows (it doesn't matter exactly which rows from the result). Is there any way to achieve this? (with rownum or with another method)

（我可以更改视图定义或最终SQL的构建方式，但据我所知，关于期望的行数动态到视图）

(I can change the view definition or the way the final SQL is built, but as far as I know, I cannot pass information about the desired number of rows dynamically to the view)

编辑：视图定义很简单，像这样：

The view definition is very simple, something like this:

CREATE OR REPLACE VIEW VIEW_NAME AS
(
    select
    e.id as ID,
    e.somefield as something,
    ... (some similar selects from e)
    c.field as anotherthing,
   ... (lots of other fields from c)
    from SCHEMA.TABLE1 e
    inner join SCHEMA.TABLE2 c on e.key = c.key
)

说明计划提到了两个表的完整表访问，这是不奇怪的，因为只返回前几行不应该花费很长时间。

Explain plan mentions a full table access for both tables which is not surprising, because just returning the first few rows should not take a long time.

EDIT2：以下是完整计划

here's the full plan

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2644394598

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |     4 |  1252 |       | 43546   (1)| 00:08:43 |       |       |        |      |            |
|*  1 |  COUNT STOPKEY            |             |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |             |       |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002    |   696K|   207M|       | 43546   (1)| 00:08:43 |       |       |  Q1,02 | P->S | QC (RAND)  |
|*  4 |     COUNT STOPKEY         |             |       |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|*  5 |      HASH JOIN BUFFERED   |             |   696K|   207M|    49M| 43546   (1)| 00:08:43 |       |       |  Q1,02 | PCWP |            |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|   6 |       BUFFER SORT         |             |       |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|   7 |        PX RECEIVE         |             |   696K|    90M|       |  5137   (1)| 00:01:02 |       |       |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH      | :TQ10000    |   696K|    90M|       |  5137   (1)| 00:01:02 |       |       |        | S->P | HASH       |
|   9 |          TABLE ACCESS FULL| TABLE1      |   696K|    90M|       |  5137   (1)| 00:01:02 |       |       |        |      |            |
|  10 |       PX RECEIVE          |             |   892K|   149M|       |  5260   (1)| 00:01:04 |       |       |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001    |   892K|   149M|       |  5260   (1)| 00:01:04 |       |       |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |             |   892K|   149M|       |  5260   (1)| 00:01:04 |     1 |   140 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL| TABLE2      |   892K|   149M|       |  5260   (1)| 00:01:04 |     1 |   140 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<5)
   4 - filter(ROWNUM<5)
   5 - access("E"."KEY"="C"."KEY")

27 rows selected.

推荐答案

我会看到 / * + NOPARALLEL * / 提示符合GuiGi的答案。另一件事要考虑为此生成的计划：

I'd see what the /*+ NOPARALLEL */ hint does as per GuiGi's answer. Another thing to try is look at the plan generated for this:

select /*+ FIRST_ROWS(10)*/ * from VIEW_NAME where ROWNUM < 5;

这篇关于Oracle查看性能与rownum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！