CODEWITHSUNDEEP
sqloracle-databaseperformancequery-optimization
Shavkatjon Mamashokirov
Shavkatjon Mamashokirov

Reputation: 23

faster way of process return

I want to return last given passport from a database. In Table1 there is only one passport info. In Table2 there are all passport info belong to every person.

My comparing code extremely slowly working, it takes too much time. So, if there is any faster alternative code for my code please share it, Tanks befor.

  from table1 t
 where t.pass_date <
       (select max(tb_datebeg) from table2 where tb_inn = t.tin)

Upvotes: 0

Views: 51

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

We could phrase this as a join along with analytic functions:

WITH cte AS (
    SELECT t1.*, MAX(t2.tb_datebeg) OVER (PARTITION BY t2.tb_inn) max_tb_datebeg
    FROM table1 t1
    INNER JOIN table2 t2 ON t2.tb_inn = t1.tin
)

SELECT *
FROM cte
WHERE pass_date < max_tb_datebeg;

The above query would benefit from the following index on table2:

CREATE INDEX idx2 ON table2 (tb_inn, tb_datebeg);

Upvotes: 2

Related Questions