Oracle Analytic functions

Here's an excellent article that I always refer to when I am in doubt on using Oracle analytic functions...

http://www.orafaq.com/node/55

Merge statement

Merge statement is available in Oracle since version 9i. You can use this to perform INSERT and UPDATE all in one statement.

However note that running MERGE statement in parallel can cause error situations, this happens mostly due to the fact that each parallel process tries to insert a row that doesn't exist causing primary key violations.

Here's a simple example:

Merge into d
Using (select col1, col2, col3 from src_table) s
On (d.col1 = s.col1)
When matched then
Update set d.col2 = s.col2,
d.col3 = s.col3
When not matched then
Insert (d.col2, d.col3) values (s.col2, s.col3);

Finding duplicate rows in Oracle

Adding these to my utility sqls.

SELECT col1, col2, COUNT(*)
FROM table1
GROUP BY col1, col2
HAVING COUNT(*) > 1

SELECT *
FROM table1 a
WHERE ROWID !=
(SELECT MAX(ROWID) FROM table1 b
WHERE a.col1 = b.col1
AND a.col2 = b.col2)

Note: col1 and col2 are assumed to be the unique columns that identify a row.