Difference Between NORMAL Delete and M LOAD DELETE in TeraData

Difference Between NORMAL Delete and M LOAD DELETE

M LOAD Delete Runs Faster than Normal Delete Because of Below Reason:

• No ROLLBACK so No TRANSIENT JOURNAL Overhead
• Fully Automatic Restorable
• RUNS in phases
• Delete Based on UPI Not permitted

MLOAD PROCESSING And PHASES

It processes the data Block by Block

Preliminary phase[BASK SETUP]

Target Tablesà A, B, C

3 ETS

3 UVS

3 WTS

LOG TABLE

“INITIAL LEVEL LOCKS”

(ACQUISIONLOCKS/APPLICATION LOCKS)  

DML TRANSACTION PHASE[TO SENDING DML STATEMENTS TO RDBMS]

MLOAD Syntaxes

DML Syntaxes

DML Syntaxes MOVE TO WT TASK  

ACQUISITION PTIASE[SENDING INPUT DATA TO AMPS]

Input dataà” WT” Access work on Target

Note: No AQuision phase for delete task “ET” Tabke  

APPLICATION PHASE[SEND AMPS DATA TO TABLES]

WT Target tasks

“Write lock”

“UV TABLES”  

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

TASK CLEAN UP PHASE[CLEARING PROCESS]

DROP ETS

UVS

WTS

LOGTABLES

RELEASES” LOCKS”  

Note: Refer to the material for detail description of phases  

MULTI LOAD LOCKS

A)ACCESS LOCK

Applied in the acquisition phase  

b)Write Lock

Applied in the application phase  

C)AQuision Lock

Applied in the initial phase available till AQuision phase During this locks any DML operation including drip can be performed on the target table  

D)APPLICATION LOCK

Applied in the initial phase available until the application phase during these locks, select operation including DR can be performed.  

E)EXCLUSIVE LACK

This Lock is useful to release the locks on the target table by using the release Mload command  

CHECKPOINT

Syntax - Checkpoint  time/Records

<60 times based

>60 Records based  

Ex- checkpoint 30;

For every 30 minutes it takes a checkpoint 50000;

For every 50000 Records, it takes a checkpoint  

SESSIONS

Syntax: Sessions max-min Ex: Sessions           4       2

It takes max 4, min2 sessions  

Errlimit

1. Without percentage:

Ex -  errlimit10; for 10errors script fails

1. with percentage

Ex: errlimit 1000 1;

For every 1000 Records, it checks for 1% fo errors, if it crosses script fails  

AMPCHECK ALL/NONE/APPLY

APPLY -  till the application phase continuous after that it stops the process

NONE - it continuous of execution with Down AMP If fallback amp are available

All - it cannot continue its execution with down amp

EX - DELETING THE data by using delete TASK  

For an in-depth knowledge, click on below

• Teradata Interview Questions
• Multi load tasks in Teradata