Difference Between NORMAL Delete and M LOAD DELETE in TeraData

Ratings:
(4)
Views: 914
Banner-Img
Share this blog:

 

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

  MLoad Processing  

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 2Target 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

EXDELETING THE data by using delete TASK  

For an in-depth knowledge, click on below

You liked the article?

Like: 0

Vote for difficulty

Current difficulty (Avg): Medium

EasyMediumHardDifficultExpert
IMPROVE ARTICLEReport Issue

About Author

Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.