oracle – Query performance in describing a race’s relative finishing positions

Is there a faster, and more parsimonious way to do this?

For each race (i.e. 1, 2, and 3), each row contains a race entry X (which is a horse), some details including it’s finishing position, and in the same row the best 3 other entries not including X (transposing relative non aggregate data from other rows into columns). So say X was came 1st in the race, the best relative finishing position would be 2nd and the next best would be 3rd, but if X’s came 2nd, the best relative finishing position would be 1st and the next best would be 3rd.

Note, I’ve tried putting case statements in joins – but the performance was bad. There are PKs and indexes on joins.

So here is my skeleton query (the actual query is huge with a lot more inline views in case statements, and > 1 million rows). The driver table is race_entry.. the rownum checks are there to guard against bad data (2 or more horses finishing 1st, who would have thunk). Many thanks in advance!

select 
      re.race_entry_code  -- pk / unique identifier
    , r.race_code -- (i.e. 1, 2 and 3)
    , h.horse_code -- (entry X)
    , h.name as horse_name
    , re.position_finishing_code

    -- relative best entries 1 
    , case when re.position_finishing_code = 1 
        then (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1)
        else (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=1  and rownum<=1) 
           end as BEST_HORSE_CODE
    , case when re.position_finishing_code = 1  
                then (select  hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
            else (select hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=1  and rownum<=1) 
      end as BEST_HORSE_NAME
    , case when re.position_finishing_code=1  then (select bh.position_finishing_code from race_entry bh    where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
            else (select bh.position_finishing_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=1  and rownum<=1) 
      end as BEST_HORSE_POSITION
      
    -- relative best entries 2 
    , case when re.position_finishing_code between 1 and 2 
        then (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1)
        else (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
           end as BEST_HORSE_CODE_2
    , case when re.position_finishing_code between 1 and 2  
                then (select  hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
            else (select hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
      end as BEST_HORSE_NAME_2
    , case when re.position_finishing_code between 1 and 2  then (select bh.position_finishing_code from race_entry bh  where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
            else (select bh.position_finishing_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=2  and rownum<=1) 
      end as BEST_HORSE_POSITION_2
      
    -- relative best entries 3 
    , case when re.position_finishing_code between 1 and 3 
        then (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=4  and rownum<=1)
        else (select bh.horse_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
           end as BEST_HORSE_CODE_3
    , case when re.position_finishing_code between 1 and 3  
                then (select  hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=4  and rownum<=1) 
            else (select hi.name from race_entry bh join horse hi on bh.horse_code=hi.horse_code where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
      end as BEST_HORSE_NAME_3
    , case when re.position_finishing_code between 1 and 3  then (select bh.position_finishing_code from race_entry bh  where bh.race_code=re.race_code and bh.position_finishing_code=4  and rownum<=1) 
            else (select bh.position_finishing_code from race_entry bh where bh.race_code=re.race_code and bh.position_finishing_code=3  and rownum<=1) 
      end as BEST_HORSE_POSITION_3

from race_entry re
join race r on re.race_code=r.race_code 
join horse h on re.horse_code=h.horse_code
--where re.race_code=1
order by re.race_code, re.position_finishing_code asc