The PeopleSoft Tipster Blog

SQL Best Practises in less than 20 minutes January 29, 2008

A post on the blog of H.Tonguç Yılmaz drew my attention to a great video tutorial by Stephane Faroult on “SQL Best Practises in less than 20 minutes”.

http://www.roughsea.com/vids/SQL_Best_Practices.html

Stephane clearly knows his topic, but also possesses a wonderful dry wit. His comical example of the developer’s shopping algorithm and the “DBA doing the parameter dance” to tune it had me chuckling away and has made sure that the point won’t be forgotten.

Materialized Views January 10, 2008

A materialized view (aka Snapshot) is a sort of ‘summary table’, the use of which allows you to reduce the processing time and complexity of some queries.

It’s a view where the data is defined via a SQL statement, but the resulting dataset is actually stored in the database (which can then be indexed, analysed etc). Depending upon the parameters chosen Oracle can keep the data in your view in sync with that in the tables upon which it is based. They were originally designed for replication (i.e. holding local copies of remote tables) but they’ve been adapted for performance tuning and reporting use.

(more…)

Shared Pool December 13, 2007

Oracle Databases contain a ‘shared pool’, which is a repository for recently run SQL. If you attempt to execute the same piece of SQL repeatedly you’ll probably notice that the execution time decreases, this is because of how Oracle parses the SQL (i.e. it’s a soft parse, not a hard parse) and the execution plan is already calculated for the SQL. I’ve spoken about this before in an App Engine context here.

This cache (or to use the correct term the ‘Shared Pool’) is all very useful, but what if you’re trying to tune a piece of SQL? You want to get a ‘natural’ idea of how long it’ll take to run, not the artificially quick result you’ll get if it’s in the shared pool. (Execution Time is of course not the only measure to consider when tuning, examining the execution plan is critically important.)

We need to flush any mentions of our SQL from the shared pool. DBAs can clear it by issuing the ‘ALTER SYSTEM FLUSH SHARED_POOL’ command, but us un-privileged developers don’t have that luxury (and neither would we want to clear the entire cache).

Instead, re-calculating the statistics on a table means that “all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool”.

A fuller (and slightly more expertly worded) explanation is available here:

http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10743/memory.htm

Portal Navigation December 9, 2007

I would imagine many of you are familiar with the PSPRSMDEFN table as it is tremendously useful. I would gamble most have a SQL snippet tucked away somewhere that will query it to find the PIA navigation to any given component. However Jim Marion has posted some SQL on his blog that is the tidiest I’ve seen to date (it’s Oracle specific BTW).

Most SQL snippets use multiple joins to the table, I’ve seen one before that used connect_by_prior, but this one is the smallest script yet.

Thanks Jim!

Edit:

this slightly more succinct version may be even better.
select distinct rtrim(reverse
         (sys_connect_by_path(reverse
         (portal_label), ' > ')), ' > ') path
  from psprsmdefn
 where portal_name = 'EMPLOYEE'
   and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
 start with portal_uri_seg2 = :1
connect by prior
         portal_prntobjname = portal_objname

An Alternative way of writing Effdt clauses October 7, 2007

I’ve covered the RANK analytic function before but neglected to mention a useful application for it, and that is for rewriting Effdt and Effseq clauses more efficiently.

The usual way to return the job row (this could be any table, I’m just picking job as a common example) that’s effective for each Employee at a specific date is probably something similar to this:

SELECT emplid, empl_rcd, effdt, effseq
  FROM ps_job j
 WHERE j.effdt =
    (SELECT MAX (j1.effdt)
       FROM ps_job j1
      WHERE j1.emplid = j.emplid
        AND j1.empl_rcd = j.empl_rcd
        AND j1.effdt <= '10/AUG/2007')
   AND j.effseq =
    (SELECT MAX (j2.effseq)
       FROM ps_job j2
      WHERE j2.emplid = j.emplid
        AND j2.empl_rcd = j.empl_rcd
        AND j2.effdt = j.effdt)

Execution Cost = 3010, time = 1 sec.

SELECT emplid, empl_rcd, effdt, effseq
  FROM (
SELECT emplid, empl_rcd, effdt, effseq,
  RANK () OVER
       (PARTITION BY emplid, empl_rcd
            ORDER BY effdt DESC
                   , effseq DESC) rnk
           FROM ps_job
          WHERE effdt <= '10/AUG/2007') j
 WHERE rnk = 1

Execution Cost = 665, time = 78 msecs.

Not only is the code shorter and easier, the database execution cost is less than a quarter of the ‘standard’ method and the execution time is less than a tenth.

Explain plans and costs are obviously variable depending upon your environment, the tables and indexes and the volume of data; however it should be clear from the example above that there are sometimes quicker ways than the ‘standard’ method.

Lag and Lead September 21, 2007

I’ve previously looked at the RANK and DENSE_RANK analytic functions, so thought I’d expand a little by mentioning LAG (and LEAD).

Analytic Functions are extensions to Oracle SQL that enable common tasks to be accomplished faster than using large blocks of SQL or PL/SQL. They are apparently ‘under review by the ANSI SQL committee for inclusion in the SQL specification’, although whether this will ever happen or not is anyones guess.

The LAG function solves (at least for those of us on Oracle) the oft-encountered issue of retrieving data from a prior row.

(more…)