query optimization (nested subqueries)

Question

I try to simplify below subqueries to improve select statement. I have table with 3 basic columns as ID, GRAGE and AGE. To select all records which have GRADE same as GRADE of Maximum ID Might somebody have better way that create nested subqueries, welcome all your suggestions?

Note: My apologise for formatting the table

ID    GRADE     AGE
10      A           30
12      B           45
13      A           15
09      B           14
20      A           12

SELECT
    *
FROM
    TABLE
WHERE
    GRADE = (
        SELECT
            grade
        FROM
            TABLE
        WHERE
            id = (SELECT MAX(id) FROM TABLE)
    );

If you ask for perfromance optimization of this query, then please run EXPLAIN PLAN FOR your_query, then SELECT * FROM table( DBMS_XPLAN.Display ), and finally copy a result of the latter query as a text - not a bitmap and append it to the question. — krokodilko
– krokodilko, Commented Dec 4, 2016 at 8:57
@krokodilko And I would add that the answer might also depend on how big the table is. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 4, 2016 at 8:58

Tim Biegeleisen · Accepted Answer · 2016-12-04 08:54:24Z

1

You could use a CTE to make the query easier to read:

WITH cte AS
(
    SELECT GRADE,
           ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID DESC) RowNum
    FROM yourTable
)

SELECT *
FROM yourTable
WHERE GRADE = (SELECT t.GRADE FROM cte t WHERE t.RowNum = 1)

However, I don't have a problem with your original approach because the subqueries are not correlated to anything. What I mean by this is that

SELECT MAX(id) FROM yourTable

should effectively only be executed once, and afterwards sort of be treated as a constant. Similarly, the query

SELECT grade FROM TABLE WHERE id = (max from above query)

should also be executed only once. This assumes that the query optimizer is smart enough to figure this out, which it probably is.

edited Dec 4, 2016 at 8:54

answered Dec 4, 2016 at 8:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user881703 Over a year ago

Thanks you, MAX vs PARTITION which will give better perfomace?.

Tim Biegeleisen Over a year ago

Both your approach and mine should require 2 full table scans. Please run EXPLAIN on both queries and see for yourself which is faster.

Faisal · Accepted Answer · 2016-12-04 10:46:44Z

0

You can do the following (not much simpler though):

SELECT
    *
FROM
    TABLE
WHERE
    GRADE IN (
        SELECT
            first_value (GRADE) over (ORDER BY id DESC)
        FROM
            TABLE
    )

edited Dec 4, 2016 at 10:46

Faisal

4,7854 gold badges43 silver badges51 bronze badges

answered Dec 4, 2016 at 8:56

Grisha Weintraub

7,9961 gold badge28 silver badges47 bronze badges

Collectives™ on Stack Overflow

query optimization (nested subqueries)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related