Normalization and Bulk Collect

Normalization :

1NF	Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2NF	Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF	Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF	Boyce-Codd Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF	Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF	Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.

4NF Isolate Independent Multiple Relationships

Initial business request

Incorrect solution

Correct 4th normal form

---

5NF : Isolate Semantically Related Multiple Relationships

Initial business request

Incorrect solution

Correct 5th normal form

Bulk Bind:

  Improves the performance of oracle while manipulating data in Loops in Pl/sql.

  It can be implemented with For all Construct in Pl/SQL.

Example :

The time taken to insert, update then delete 10,000 rows using regular FOR..LOOP statements is approximately 34 seconds on my test server:

CREATE TABLE test1(  id    NUMBER(10) primary key,  description  VARCHAR2(50));

The time taken to insert, update then delete 10,000 rows using regular FOR..LOOP statements is approximately 34 seconds on my test server:

DECLARE

  TYPE id_type          IS TABLE OF test1.id%TYPE;

  TYPE description_type IS TABLE OF test1.description%TYPE;

  t_id           id_type     :=  id_type();

  t_description  description_type := description_type();

BEGIN

  FOR i IN 1 .. 10000 LOOP

    t_id.extend;

    t_description.extend;

    t_id(t_id.last)   :=  i;

    t_description(t_description.last) := 'Description: ' || To_Char(i);

  END LOOP;

    FOR i IN t_id.first .. t_id.last LOOP

    INSERT INTO test1 (id, description)

    VALUES (t_id(i), t_description(i));

  END LOOP;

  FOR i IN t_id.first .. t_id.last LOOP

    UPDATE test1   SET    description = t_description(i)

    WHERE  id = t_id(i);

  END LOOP;

  FOR i IN t_id.first .. t_id.last LOOP

    DELETE test1 WHERE  id = t_id(i);

  END LOOP;

Commit;         

End;

Using the FORALL construct to bulk bind the inserts this time is reduced to 18 seconds:

DECLARE

  TYPE id_type  IS TABLE OF test1.id%TYPE;

  TYPE description_type IS TABLE OF test1.description%TYPE;

  t_id           id_type          :=  id_type();

  t_description  description_type := description_type();

BEGIN

  FOR i IN 1 .. 10000 LOOP

    t_id.extend;

    t_description.extend;

    t_id(t_id.last) := i;

    t_description(t_description.last) := 'Description: ' || To_Char(i);

  END LOOP;

    FORALL i IN t_id.first .. t_id.last

    INSERT INTO test1 (id, description)

    VALUES (t_id(i), t_description(i));

    FORALL i IN t_id.first .. t_id.last

    UPDATE test1  SET    description = t_description(i)

    WHERE  id = t_id(i);

   FORALL i IN t_id.first .. t_id.last

    DELETE test1 WHERE  id = t_id(i);

    COMMIT;

END;

/

Using Bulk Collect :

Populating two collections with 10,000 rows using a FOR..LOOP takes

approximately 1.02 seconds:

DECLARE

  TYPE id_type          IS TABLE OF test1.id%TYPE;

  TYPE description_type IS TABLE OF test1.description%TYPE;

    t_id           id_type          := id_type();

  t_description  description_type := description_type();

    CURSOR c_data is SELECT * FROM   test1;

BEGIN

  FOR cur_rec IN c_data LOOP

    t_id.extend;

    t_description.extend;

    t_id(t_id.last)   :=  cur_rec.id;

    t_description(t_description.last) := cur_rec.description;

  END LOOP;

END;

Using the BULK COLLECT INTO construct reduces this time to approximately

0.01 seconds:

DECLARE

  TYPE id_type          IS TABLE OF test1.id%TYPE;

  TYPE description_type IS TABLE OF test1.description%TYPE;

  t_id           id_type;

  t_description  description_type;

BEGIN

  SELECT id, description

  BULK COLLECT INTO t_id, t_description FROM test1;

END;

/