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
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;
No comments:
Post a Comment