This content has been marked as final. Show 3 replies
Why are you joing a self join on PS_PERS_DATA_EFFDT ?
SELECT MAX(P.EFFDT) FROM PS_PERS_DATA_EFFDT P , PS_PERS_DATA_EFFDT E WHERE P.EMPLID = E.EMPLID AND P.EFFDT <= :1;
I dont see any need. Cant you just do
select max(effdt) from ps_pers_data_effdt where effdt <= :1;
This is actually a subset of a seeded SQL of People Soft One Off Cycle Program. My focus is that I wanna create the function base index based on Oracle Tunning Set Recommendatoin so If you can help on that. Appreciate for your post and reviewing.
I suspect that the Tuning Advisor is wrong here, sys_op_undescend and sys_op_descend are internal Oracle functions that the optimizer typically applies to indexes that were created as descending:
so those functions are applied in order for the optimizer to be able to use that index.
create index i on t (col) descending
You should actually look at the explain plan for that query to see what is actually doing. Richard Foote has a good explanation here http://richardfoote.wordpress.com/category/descending-indexes/