Performance considerations during taking back up--

Database
Enthusiast

Performance considerations during taking back up--

Hi Experts,

Need Your suggestions among the 2 possible scenerio through which we can take the backupof a  Huge table at production.

Scenerio 1-

Create table a_bkp as A with Data and Stats;  

Scenerio 2-

First Create empty table as create table A_bkp as A with no data .

second Use the merge statement to copy the data from main table to _bkp table

Merge statement in place of normal INsert  select ...

like -

Merge into _bkp

Using A

I had a discussion with one of my peer and he suggested to go with scernio 2 not with scenerio  1 as it scenerio 1 will run into spool issues at the production as table is really huge and MERGE dont take any Spool to process the record how ever Insert Select does.

As i am bit unsure ,Just wanted  to check this up Experts..

Thanks in advance.

Nishant 

9 REPLIES
Senior Apprentice

Re: Performance considerations during taking back up--

Hi Nishant,

if i had to rank the different scenarios it would be:

#1: first scenario

#2: create table as existing_table with no data plus ins/sel

#3: second scenario

- "create table as existing_table" is simply copying datablocks without any spool

- INS/SEL is also not using any spool if source and target have exactly the sams DDL

- MERGE might be more efficient than INS/SEL, but definitely not in this case

Dieter

Enthusiast

Re: Performance considerations during taking back up--

One thing I'd like to mention about scenario # 1 ( create table as existing table with data) that if your existing table has few PIs, SIs, partitions then teradata would not create the same in the new table (it will give the defaul PI to the new table) and that may cause the delay in inserting the records, especially when you have lot of records to insert. So just make sure of that.

Senior Apprentice

Re: Performance considerations during taking back up--

"CREATE TABLE AS existing_table" creates an *exact* copy of the existing_table including PI/SI/Partitioning, etc.

There are only two things which are not copied: Foreign Keys and Triggers.

But of course you're correct if it's "CREATE TABLE AS (SELECT...)" 

Dieter

Enthusiast

Re: Performance considerations during taking back up--

You are right, I meant, CREATE TABLE AS (SELECT..) only.

I never tried "CREATE TABLE AS existing_table" but it's good to know that it copies all the indexes, will use that from next time.

Enthusiast

Re: Performance considerations during taking back up--

Thanks Dieter for the Response..

Actaully the thing is ,we have PPI defined on A so when we do the  INSERT SELECT on the empty A_bkp table then in this case  optimizer is using the spool space and hence taking lot of time and leading to spool space issues.

I think INSERT SELECT on a NONPPI table doesnt use any spool.

Hence we thought of doing it with MERGE statement which is not throwing any ERROR.. i think  it doesnt uses any spool and does BLOCK by BLOCK ..thats why.. pls confirm..

Just  wanted to know out of curiousity  the possible other scenerios in which  MERGE  STATEMENT is Preferred.?

Thanks !

Regards,

Nishant

Senior Apprentice

Re: Performance considerations during taking back up--

Hi Nishant,

if target is an exact copy of source then there will be no spool involved, regardless of PPI or not, simply look at explain.

You should check if A and A_bkp DDL is different.

Dieter

Enthusiast

Re: Performance considerations during taking back up--

Hi Dieter ,

Both are having same Structures but they are created at different databases, i mean, one at LIVE and other at BACKUP Db.

does different DB's making the usage of spool in INSERT Select?

Also can you please  suggest the scenrios which MERGE gives the OPTIMAL performance?

THanks!

Nishant

Senior Apprentice

Re: Performance considerations during taking back up--

Hi Nishant,

same database or not doesn't matter. If there's spool usage there must be a difference in DDL.

MERGE might use no spool when both source and target have the same (P)PI.

Dieter

Enthusiast

Re: Performance considerations during taking back up--

thanks Dieter!