Need Your suggestions among the 2 possible scenerio through which we can take the backupof a Huge table at production.
Create table a_bkp as A with Data and Stats;
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 ...
Merge into _bkp
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.
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
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.
"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...)"
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.
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.?
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.
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?
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.