The PeopleSoft Tipster Blog

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…)

Updating existing data with the Rank May 2, 2007

In the post ‘Incrementing Counts in SQL’ I detailed a way of ranking rows within key values. This works fine in a select statement, so I was able to use that to insert data into an empty record.

The problem I faced today however, was that the data already existed in a table. I needed to update an existing column with the rank that each row had within the table (there had been multiple inserts into the table, and the rank needed recalculating).

I played around with the problem for a bit and couldn’t see a way of doing it (without a kludge like copying it out to another table and using the rank statement instead of selecting the column). Then it occurred to me that it could be achieved using the two step approach shown here. I wrote a quick App Engine to do it (a DoSelect with a SQL step inside) and it worked a treat. It took 4 minutes to run, and a smart colleague of mine with PL/SQL knowledge bettered me with this (which ran in 15 seconds):


%Execute(/)
DECLARE CURSOR c1 IS
SELECT x.keyname
, x.key1
, x.key2
, x.key3
, x.key4
, x.key5
, x.key9
, RANK () OVER (PARTITION BY x.keyname, x.key1 ORDER BY x.keyname, x.key1, x.key9, x.key2) rk
FROM recname x;
BEGIN FOR c1_rec IN c1 LOOP
UPDATE recname y
SET y.key3 = c1_rec.rk
WHERE y.keyname = c1_rec.keyname
AND y.key1 = c1_rec.key1
AND y.key2 = c1_rec.key2
AND y.key9 = c1_rec.key9;
END LOOP;
END;
/


The above code snippet includes the %Execute(/) command needed for running PL/SQL within an App Engine SQL Step.