Oracle - Limit Row_number () in percentage
I wrote a query that gives n% of the total result set. I'm not sure if this is an efficient way to query.
Below is my query (modified for understanding) that gives 20% of the total result set:
SELECT *
FROM (
WITH RS AS (
SELECT ID
,ROW_NUMBER() OVER (
ORDER BY ID
) r_num
,count(1) OVER () AS n_rows
FROM TAB1
LEFT OUTER JOIN TAB2 ON TAB1.ID = TAB2.ID
INNER JOIN TAB4 ON TAB4.ID = TAB4.ID
INNER JOIN TAB3 ON TAB3.ID = TAB4.ID
WHERE TAB2.ID_ IS NULL
AND TAB3.C_ID = 'JP'
)
SELECT *
FROM RS
WHERE r_num <= round(n_rows * 0.2)
ORDER BY ID
)
Do you have any suggestions for efficiently using the first / last / random n% of the result set?
source to share
Since you are on 11g
(as it seems from the tag), I would suggest an analytic function NTILE
. Read more about NTILE in the documentation. NTILE will split the ordered dataset into multiple buckets. For example, in your case, the top 10% of the lines would look something like this:
NTILE (10) OVER (ORDER BY id)
The request can be rewritten as:
WITH RS AS (
SELECT ID
,NTILE (10) OVER (ORDER BY id) r_num
FROM TAB1
LEFT OUTER JOIN TAB2 ON TAB1.ID = TAB2.ID
INNER JOIN TAB4 ON TAB4.ID = TAB4.ID
INNER JOIN TAB3 ON TAB3.ID = TAB4.ID
WHERE TAB2.ID_ IS NULL
AND TAB3.C_ID = 'JP'
)
SELECT id
FROM rs
WHERE r_num = 10
ORDER BY id
Note. Since you haven't provided a test case, I don't have your tables and data to recreate the test case.
That was about the previous one 12c
, now 12c
you have a new ROW LIMITING CLAUSE
one where you just need to add this to the sentence:
FETCH FIRST 10 PERCENT ROWS ONLY
For example:
SELECT col
FROM table
ORDER BY col
FETCH FIRST 10 PERCENT ROWS ONLY;
This is an advantage over the old one ROWNUM
, which has always been a problem when an ordered result set is expected. With the new 12c string limit suggestion, in my example above, you will always get a result set ORDERED
. You can find more examples here ROW LIMITING clause
source to share