A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Re-write exists with MAX or MIN subquery using RANK and Partition
A common task in Oracle is to find data from a table t1 based on where a column in that table is equal to the MIN or MAX of a column in a second table t2.
To illustrate this consider that we have a table called stock_dhist that contains the daily prices of stocks in our database going back a number of years. We also have a stock table that contains static data about our stocks. One of the fields in our stock table is called start_date. This is when the stock first enters our database and this start_date should equal to the minimum of the price_date in our stock_dhist table. If you want to identify the stocks in your database that didn’t mee this criteria you would traditionally have written a query similar to the one shown below.
from stock s
where exists (
select 'x' from stock_dhist
where stock_id = s.stock_id
having min(price_date) <> s.start_date )
With the introduction of analytics though another method becomes available which can be considerably more performant. The SQL below shows the above query rewritten to use the RANK analytic.
from stock s,(select * from
e.stock_id an> stock_id,
rank() over(partition by stock_id order by price_date asc) r
from stock_dhist e
where r = 1
and s_mnem is not null) x
where x.stock_id = s.stock_id
and s.start_date <> x.pdat
As always you need to check the performance of both queries as there can be significant difference depending on the structure of your tables but I think you’ll find the second version to be faster in most cases.