Sunday, February 22, 2015

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.

2 comments :

Anonymous said...

Is it possible to import a full 11g dumpfile into a new 12c database using full = y. I need to create a prod clone and this is what i have to work with. Can i do full=y or will i have to import all of the schemas, one at a time.

David Kurtz said...

Export/Import does not produce an exact clone. The data might be logically the same, but it will not be physically identical. The tables will be perfectly packed, and the indexes will be rebuilt.
This may or may not be a problem depending upon what you want to do with the clone. If you are testing 12c performance, it might well matter because it will affect the cost of index accesses.
You might prefer to restore an RMAN backup (so you really have a physical clone) and then upgrade the cloned database in place to 12c.
That said, I see no reason why export/import would fail (though I haven't tried it). Although, you might have a few problems with view dependencies.