Skip to Main Content
  • Questions
  • Subquery vs Shared List of Values, performance


Question and Answer

Connor McDonald

Thanks for the question, Chad.

Asked: September 06, 2023 - 10:40 pm UTC

Last updated: September 08, 2023 - 7:28 am UTC

Version: APEX 19.2 DB 12C

Viewed 1000+ times

You Asked

We have report grids showing 2 or more (maybe 6) columns of "Who" did what.
To fetch and show their real name based on username:
Is it better performance to have a subquery for each "Who" column or
just return the username in the query column, and
in that column Settings, select Based On 'Display Value List of Values'
and have a Shared Component fetching all of the users?

Grid query:
select <other things>,       
 (select first_name || ' ' || last_name 
   from security_users
   where user_name = vpd.Added_By
  ) as ADDED_BY,
 (select first_name || ' ' || last_name 
   from security_users
   where user_name = vpd.Submitted_By
from VPD

select <other things>,       
from VPD

with each "Who" column using a Shared Component:
 select First_Name||'  '||Last_Name as Display_Val, USER_NAME AS Return_Val
 From security_users

When using Shared LOV, one thing I notice is that selecting the grid header shows a list of every user name returned from the Shared LOV for Filtering, even though they are not all in the grid results.

Currently the number of users (the lookup table) are about 300.
Same question but with a lookup table of say 10,000 rows.

and Connor said...

I'd probably do neither and just use a join in my grid query

select <other things>,       
a.first_name || ' ' ||a. last_name 
s.first_name || ' ' || s.last_name 
from VPD
 security_users a,
 security_users s,
   where a.user_name(+) = vpd.Added_By
   and s.user_name(+) = vpd.Submitted_By

There are some other options you might be able to explore, eg

More to Explore


The Oracle documentation contains a complete SQL reference.