PeopleTools 8.54: %SelectDummyTable Meta-SQL

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
  
PeopleTools simply evaluates this meta-SQL as 'DUAL' on Oracle.

In Oracle, DUAL is just a convenience table.  You don't need to use it, you can use anything you want. PeopleSoft applications often use PS_INSTALLATION when they needs a single row source in a query.  This was another example of platform agnosticism.  PS_INSTALLATION is available on every platform and every PeopleSoft installation, DUAL is not.

Instead of coding this (the example is taken from ESPP_REF_REVMAIN.PSHUP.Do When)

%Select(IF_FLAG) 
 SELECT 'X' 
  FROM PS_INSTALLATION 
 WHERE %Bind(ST_SEND_SRC) = 'N'

You can now code this instead:

%Select(IF_FLAG) 
 SELECT 'X' 
  FROM %SelectDummyTable
 WHERE %Bind(ST_SEND_SRC) = 'N'

Which resolves to:

%Select(IF_FLAG)  
 SELECT 'X'  
  FROM DUAL  
 WHERE %Bind(ST_SEND_SRC) = 'N'

There are two advantages to using DUAL.

• In the database, the Oracle optimizer knows that DUAL is a special one row, one column table.  When you use it in queries, it uses this knowledge when generating the execution plan.
• If you used a real table, there was a risk that it could have no rows, or more than one row.  Either could cause application problems.  In previous versions of Oracle, when it was a real physical table, that problem could also occur with DUAL.  However, since Oracle 10g it is just a memory structure in the database, and accessing it involves neither a logical nor a physical read. 

Conclusion

PeopleSoft have created a Meta-SQL that evaluates as DUAL so that this Oracle optimization is implemented in a PeopleSoft platform generic manner. 
I would not bother to go back and change existing code to use this, unless perhaps I was visiting the code anyway, but I would certainly recommend its use going forward.