CODEWITHSUNDEEP
oracle-databaseoracle11gmaxquery-optimization
FTK
FTK

Reputation: 195

[Oracle]Optimization a query contain a max subquery

I'm wondering if there is a way to optimize a view base on that request :

SELECT product ,
  price ,
  quantity_in_stock ,
  location
FROM mytableA x
WHERE x.price =
  (SELECT MAX( z.price )
  FROM mytableA z
  WHERE (z.quantity_in_stock > 0)
  AND z.product          = x.product
  );

Thank you.

Upvotes: 0

Views: 56

Answers (1)

MT0
MT0

Reputation: 167962

Use an analytical function (single table scan) rather than a correllated sub-query (two table scans):

SELECT product,
       price,
       quantity_in_stock,
       location
FROM   (
  SELECT product,
         price,
         quantity_in_stock,
         location,
         MAX( CASE WHEN quantity_in_stock > 0 THEN price END ) OVER ( PARTITION BY product ) AS max_price_in_stock
  FROM   MyTable
)
WHERE price = max_price_in_stock;

Upvotes: 1

Related Questions