CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Logan
Logan

Reputation: 1371

Oracle - Limit Row_number() by percentage

I have written a query that gives n% from the total result set. I am not sure whether it is an efficient way to query.

Following is my query (its a modified one for understanding) which gives 20% from 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 to take first/last/random n% from the result set in an efficient way ?

Upvotes: 3

Views: 1534

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

Since you are on 11g(as it seems from the tag), I would suggest NTILE analytic function. You can read more about NTILE in the documentation. NTILE would divide the ordered data set into a number of buckets. In your case, for example, the top 10% rows would be something like :

NTILE (10) OVER (ORDER BY id)

The query could 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 did not provide a test case, I don't have your tables and data to recreate a test case.

That was about prior 12c, now in 12c you have the new ROW LIMITING CLAUSE where you need to just add this to the clause :

FETCH FIRST 10 PERCENT ROWS ONLY

For example :

SELECT col
FROM   table
ORDER BY col
FETCH FIRST 10 PERCENT ROWS ONLY;

It gives the advantage over the old ROWNUM which has had always been an issue when an ordered result set is expected. With the new row limiting clause in 12c, in my example above, you will always get an ORDERED result set. You can look more examples here ROW LIMITING clause

Upvotes: 3

Related Questions