ORACLE SQL

Table of Contents Types Boolean Vendor specific SQL hints concatenate rows Limit fetches ROWNUM ROW_NUMBER INSERT DELETE UPDATE MERGE Deal with errors COMMIT NOLOGGING

Types

Boolean

Use a char(1) inforced with a check | More on this

Vendor specific SQL

hints

Handful:

• FIRST_ROWS
• MATERIALIZE
• MATERIALIZE INDEX

concatenate rows

SELECT 
   <HEADER>,
   LISTAGG(<DETAIL>, ',') WITHIN GROUP (ORDER BY <DETAIL>)
FROM 
   <TABLE>
GROUP BY 
   <HEADER>;

Limit fetches

ROWNUM

Use in conjunction with EXISTS (ROWNUM=1) in a subquery

ROW_NUMBER

INSERT

APPEND hint make INSERT faster, because it doesn't look for free space. Beware of constraints !

INSERT /*+ APPEND */ 
INTO table1
SELECT (..)

You can achieve a similar result in a working table with

DROP TABLE table1
CREATE TABLE table1 AS SELECT (..)

DELETE

To delete related rows (works using other JOIN types, eg. LEFT OUTER)

SQL> DELETE FROM (SELECT grp.*
  2                  FROM grp
  3                  INNER JOIN my_data ON grp.id1 = my_data.id1
  4                                   AND grp.id2 = my_data.id2
  5                                   AND grp.id3 = my_data.id3
  6                                   AND grp.id4 = my_data.id4);

If table has no primary key or join conditions, then you'll get the following error message:

SQL Error: ORA-01752: cannot delete from view without exactly one key-preserved table
01752. 00000 -  "cannot delete from view without exactly one key-preserved table"
*Cause:    The deleted table had
           - no key-preserved tables,
           - more than one key-preserved table, or
           - the key-preserved table was an unmerged view.
*Action:   Redefine the view or delete it from the underlying base table

Use the following syntax based on ROWID

delete from grp where rowid in
    (
    select
         grp.rowid
    from
         grp INNER JOIN  my_data ON
            grp.id1 = my_data.id1
        and grp.id2 = my_data.id2
        and grp.id3 = my_data.id3
        and grp.id4 = my_data.id4
    )

UPDATE

MERGE

MERGE = UPSERT = UPDATE OR INSERT

• FASTER - more than an hundred times than using CURSOR
• SLOWER than INSERT, if used alone, without UPDATE

Note that a high ratio of rows affected can be processed faster with intermediate table using DDL (CREATE TABLE AS SELECT).

Usage

MERGE INTO TARGET t
  USING SOURCE s
    ON (s.id = t.id)
  WHEN MATCHED THEN
    UPDATE SET t.status = s.status
  WHEN NOT MATCHED THEN
    INSERT (id, status)
    VALUES (s.id, s.status);

Can use conditional in WHEN

MERGE INTO TARGET t
  USING SOURCE s
    ON (s.id = t.id)
  WHEN MATCHED THEN
    UPDATE SET t.status = s.status
    WHERE t.status <> 'CANCEL'

Using queries as source

MERGE INTO TARGET t
  USING (
       SELECT * FROM source WHERE status = 'DONE'
       UNION ALL  
       SELECT * FROM source WHERE status <> 'CANCEL' AND cancel_date < SYSDATE
    ) s
    ON (s.id = t.id )
  WHEN MATCHED THEN
    UPDATE SET t.status = s.status

Beware to join on several columns, including one NULLABLE that contains NULL !
The WHEN MATCHED clause will never match on NULL, so the row containing NULL will always be inserted.
Result is a duplicate on columns in ON clause.
Original post

Beware not to generate doubloons in source table, as they will be inserted "as-is" in the target.
In short, the WHEN NOT MATCHED clause is evaluated for all rows before any INSERT takes places.

Deal with errors

Several ways:

• Log exception in cursor (SAVE EXCEPTION clause +sql%bulk_exception pseudo-array)
• Log record in table (LOG ERRORS INTO)

COMMIT

Too much COMMIT = More I/O = Slower Too few COMMIT = ORA-1555 SNAPSHOT TOO OLD (or other ROLLBACK segment issues) COMMIT rate

NOLOGGING

NOLOGGING disable writing in the redo log.
It speeds up performance but disable the option of using ROLLBACK after an INSERT.
Eligible statements are:

• INSERT USING APPEND (DIRECT LOAD)
• CREATE TABLE...AS SELECT
• ALTER TABLE...MOVE PARTITION
• ALTER TABLE...SPLIT PARTITION
• ALTER INDEX...SPLIT PARTITION
• ALTER INDEX...REBUILD
• ALTER INDEX...REBUILD PARTITION
• CREATE INDEX
• SQL*LOADER

Usage with INSERT.

ALTER TABLE <TABLE> NOLOGGING;
INSERT /*+ append */ INTO <TABLE> (..);