Monday, June 03, 2002

How to Delete Duplicate Records Using Oracle's Rank Function

I've found a neat way to delete duplicates from my database tables using Oracle's RANK() function.
DELETE FROM __table__
WHERE ROWID IN (
  SELECT MyKey
  FROM (
    -- use the RANK() function to assign a sequential number to each set of
records.
    SELECT MyKey, display_name, RANK() OVER (PARTITION BY display_name ORDER BY MyKey) AS SeqNumber
    FROM (
      -- use the ROWID psuedo-column to get a unique id for each record.
      SELECT ROWID AS MyKey, display_name
      FROM __table__
      WHERE (display_name) IN (
        -- select the set of records that are duplicates.
        SELECT display_name FROM __table__ GROUP BY display_name HAVING COUNT(*) > 1
      )
    )
  )
  WHERE SeqNumber > 1
)

Sunday, June 02, 2002

I've downloaded the Solarmetric's JDO implementation and have started to use it as the basis for JDO experimentation. One of my biggest concerns is how it handles datasets in the tens of millions. I'll be creating test data to see in the coming weeks and posting the results.