Here is an example named MANVW :
SELECT manager_id, manager_sign FROM ( SELECT manager_id, manager_sign, effdt MAX(effdt) OVER (PARTITION BY manager_id) AS max_dt FROM managers) WHERE effdt = max_dt;
Here is my request:
SELECT * from MANVW where manager_sign = 'ABC';
Unfortunately, when I request this view, it builds the entire inline view (where the analytic function is) and does not use the index on manager_sign .
If I used the nested selection for MANVW , it uses the index:
SELECT m.manager_id, m.manager_sign FROM managers m where m.effdt = (select max(mi.effdt) from managers mi where mi.manager_id = m.manger_id)
The trade-off is that using analytic functions, I can quickly get all the records in the view, but it's slow if I only retrieve a subset of the records filtered by the indexed column. I would like the analytic function to use the predicate so that it is fast when using this method.
Is it possible to have an idea that uses analytic functions and, if possible, will pursue predicates?
source share