Wednesday 24 December 2014

Direct Loader

TWO TYPES OF LOADER
=======================
1. DIRECT LOADER :- DIRECT LOADER WORK ON HIGH WATER MARK & FIRST IS RECORD INSERT ON TEMORARY SEGMENT ,
   AFTER THE COMMIT EXECUTE THEN APPLY THE PARAMANENT

2. SQL*LOADER:- SQL*LOADERIS THE EXTERNAL TABLE ENTRY & INSERT THE RECORD ON TABLE THROUGH EXTERNAL TABLE.

=========================================
DIRECT LOAD:-
SERIAL DIRECT LOAD (HIGH WATER MARK )
=========================================

##THIS QUERY BY DEFAULT LOGGING MODE ,THIS OPERATION GENERATE REDO ENTRIES FOR RECOVERY PURPOSE

INSERT /*+APPEND+*/ INTO ABC
LOGGING
SELECT * FROM EMP WHERE DEPTNO=20


##WHEN THIS QUERY "NOLOGGING" MODE ,THIS OPERATION NOT GENERATED REDO ENTRIES FOR RECOVERY PURPOSE

INSERT /*+APPEND+*/ INTO ABC
NOLOGGING
SELECT * FROM EMP WHERE DEPTNO=20


EXAMPLE:-
===========
SQL> SELECT * FROM EMP;
SQL> CREATE TABLE ABC AS SELECT * FROM EMP;
SQL> SET LINE 320
SQL> SPOOL C:\TEMP\ABC.TXT;
SQL >SELECT * FROM ABC WHERE DEPTNO=20;
SQL> SPOOL OFF;
SQL. DELETE FROM ABC WHERE DEPTNO=20;

SQL> INSERT /*+APPEND+*/ INTO ABC SELECT * FROM EMP WHERE DEPTNO=20

NOTE:- SEE THE DEPTNO 20 RECORDS ARE INSERT END OF THE ROW FOR ALL RECODS IT'S HIGH WATER MARK i.e. CONTIGOUES ENTRY OR BUNCH OF RECODRS ENTRY (SERIAL CONTIGIOUS EXTENT ENTRY)

FOR EG:- YOU WILL INSERT NORMAL ROW & THIS QUERY INSERT ROW SEE THE DIFFERENCE



=============================================
DIRECT LOAD:-
PARALLEL DIRECT LOAD (HIGH WATER MARK )
=============================================

## PARALLEL "DML" CODE PERFORM FOR THE SESSION ONLY

SQL> ALTER SESSION ENABLE PARALLEL DML;


## PRALLEL INSERT RECORD IN THE TABLE, THE "8" IS DENOTED THE PROCESS "8" RECORD i.e. (8 ENTRIES INSERTED INTO THE TABLE PARALLELY AT A TIME )

INSERT /*+ PARALLEL(ABC,8) */ INTO ABC SELECT * FROM EMP WHERE DEPTNO=20;




SQL> DROP TABLE ABC;
SQL> SELECT * FROM EMP;
SQL> CREATE TABLE ABC AS SELECT * FROM EMP;
SQL >SELECT * FROM ABC WHERE DEPTNO=20;
SQL> DELETE FROM ABC WHERE DEPTNO=20;
SQL> COMMIT;
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> INSERT /*+PARALLEL(ABC,8)*/ INTO ABC SELECT * FROM EMP WHERE DEPTNO=20;

NOTE:- SEE THE DEPTNO 20 RECORDS ARE INSERT END OF THE ROW FOR ALL RECODS IT'S HIGH WATER MARK i.e. CONTIGOUES ENTRY & PARALLELY

## PARALLEL DIRECT LOAD INSERT INTO A NONPARTIONED TABLE
DIRECT LOADER WORK ON HIGH WATER MARK & FIRST IS RECORD INSERT ON NEW TEMORARY SEGMENT, AFTER THE COMMIT EXECUTE THEN APPLY THE ARAMANENT

## PARALLEL DIRECT LOAD INSERT INTO A PARTIONED TABLE
PARALLEL EXECUTION SERVER IS ASSIGNED ONE OR MORE PARTITIONS,WITH NO MORE THAN ONE PROCESS WORKING PER PARTITION ,WITH NO MORE THANONE PROCESS WORKING PER PARTION.

THE PARALLEL EXECUTION SERVER INSERTS DATA BEYOND THE CURRENT "HWM" OF THE PARTION SEGMENTS ASSIGNED TO IT.

WHEN A STATEMENT EXECTES,THE "HWM" OF EACH PARTION SEGMENT IS UPDATED BY THE PARALLEL EXECUTION CO-ORDINATOR TO THE NEW VALUE,MAKING THE DATA VISIBLE TO OTHERS.




No comments: