oracle中rowup,如何加速Oracle中的row_number？

I have a SQL query that looks something like this:

我有一個SQL查詢，看起來像這樣：

SELECT * FROM(

SELECT

...,

row_number() OVER(ORDER BY ID) rn

FROM

...

) WHERE rn between :start and :end

Essentially, it's the ORDER BY part that's slowing things down. If I were to remove it, the EXPLAIN cost goes down by an order of magnitude (over 1000x). I've tried this:

從本質上講，正是ORDER BY部分減慢了速度。如果我要刪除它，EXPLAIN成本會下降一個數量級(超過1000倍)。我試過這個：

SELECT

...

FROM

...

WHERE

rownum between :start and :end

But this doesn't give correct results. Is there any easy way to speed this up? Or will I have to spend some more time with the EXPLAIN tool?

但這並沒有給出正確的結果。有什么簡單的方法可以加快速度嗎？或者我將不得不花更多時間使用EXPLAIN工具？

5 个解决方案

#1

12

ROW_NUMBER is quite inefficient in Oracle.

ROW_NUMBER在Oracle中效率很低。

See the article in my blog for performance details:

有關性能詳細信息，請參閱我博客中的文章：

For your specific query, I'd recommend you to replace it with ROWNUM and make sure that the index is used:

對於您的特定查詢，我建議您將其替換為ROWNUM並確保使用索引：

SELECT *

FROM (

SELECT /*+ INDEX_ASC(t index_on_column) NOPARALLEL_INDEX(t index_on_column) */

t.*, ROWNUM AS rn

FROM table t

ORDER BY

column

)

WHERE rn >= :start

AND rownum <= :end - :start + 1

This query will use COUNT STOPKEY

此查詢將使用COUNT STOPKEY

Also either make sure you column is not nullable, or add WHERE column IS NOT NULL condition.

還要么確保列不可為空，要么添加WHERE列IS NOT NULL條件。

Otherwise the index cannot be used to retrieve all values.

否則，索引不能用於檢索所有值。

Note that you cannot use ROWNUM BETWEEN :start and :end without a subquery.

請注意，您不能使用ROWNUM BETWEEN：start和：end而不使用子查詢。

ROWNUM is always assigned last and checked last, that's way ROWNUM's always come in order without gaps.

ROWNUM總是最后分配並最后檢查，這樣ROWNUM始終按順序排列。

If you use ROWNUM BETWEEN 10 and 20, the first row that satisifies all other conditions will become a candidate for returning, temporarily assigned with ROWNUM = 1 and fail the test of ROWNUM BETWEEN 10 AND 20.

如果你使用ROWNUM BETWEEN 10和20，那么滿足所有其他條件的第一行將成為返回的候選者，暫時分配ROWNUM = 1並且未通過10和20之間的ROWNUM測試。

Then the next row will be a candidate, assigned with ROWNUM = 1 and fail, etc., so, finally, no rows will be returned at all.

然后下一行將是候選者，分配ROWNUM = 1並失敗等，因此，最后，根本不會返回任何行。

This should be worked around by putting ROWNUM's into the subquery.

這應該通過將ROWNUM放入子查詢來解決。

#2

5

Looks like a pagination query to me.

看起來像是一個分頁查詢給我。

From this ASKTOM article (about 90% down the page):

從這篇ASKTOM文章(約90％的頁面)：

您需要按照這些分頁查詢的唯一內容進行排序，以便每次都確定性地為行分配ROW_NUMBER。

Also your queries are no where near the same so I'm not sure what the benefit of comparing the costs of one to the other is.

你的查詢也不在同一個地方，所以我不確定比較一個和另一個的成本有什么好處。

#3

1

Is your ORDER BY column indexed? If not that's a good place to start.

您的ORDER BY列是否已編入索引？如果不是那個開始的好地方。

#4

1

Part of the problem is how big is the 'start' to 'end' span and where they 'live'. Say you have a million rows in the table, and you want rows 567,890 to 567,900 then you are going to have to live with the fact that it is going to need to go through the entire table, sort pretty much all of that by id, and work out what rows fall into that range.

部分問題是“開始”到“結束”跨度有多大以及它們“存在”的位置。假設您在表中有一百萬行，並且您想要行567,890到567,900那么您將不得不忍受這樣一個事實：它需要遍歷整個表，通過id排序幾乎所有這些行，並確定哪些行屬於該范圍。

In short, that's a lot of work, which is why the optimizer gives it a high cost.

簡而言之，這是很多工作，這就是優化器給它帶來高成本的原因。

It is also not something an index can help with much. An index would give the order, but at best, that gives you somewhere to start and then you keep reading on until you get to the 567,900th entry.

它也不是索引可以幫助的東西。索引會給出訂單，但充其量，這會讓您在某個地方開始，然后繼續閱讀，直到您到達第567,900條。

If you are showing your end user 10 items at a time, it may be worth actually grabbing the top 100 from the DB, then having the app break that 100 into ten chunks.

如果您一次向最終用戶顯示10個項目，那么實際上可能值得從數據庫中獲取前100名，然后讓應用程序將100個項目分成10個塊。

#5

0

Spend more time with the EXPLAIN PLAN tool. If you see a TABLE SCAN you need to change your query.

花更多時間使用EXPLAIN PLAN工具。如果您看到TABLE SCAN，則需要更改查詢。

Your query makes little sense to me. Querying over a ROWID seems like asking for trouble. There's no relational info in that query. Is it the real query that you're having trouble with or an example that you made up to illustrate your problem?

你的查詢對我來說沒什么意義。查詢ROWID似乎是在尋找麻煩。該查詢中沒有關系信息。是您遇到問題的真實查詢還是您說明問題的示例？