I have recently started working on teradata. I am stuck at a point. I want to move trillions of rows from one table to other. what will be the best way to do this? creating the structure and inserting the data or something like select * into as in sql which is minimally logged.
Can someone please suggest.
Really trillions? There are not many customers with that large amount of data in a single table.
The fastest way will utilize a FastPath Insert/Select:
Target table is empty and has exactly the same definition as the source table.
If the DDLs are different, but the PI is the same, you should prefer a Merge over InsSel (it avoids spooling).
If PIs are different you will need a spool with the size of the target table.
Thanks... yes this is what my seniors are saying here... the size can go upto it.......
so i should get the DLL of the soure table and create a new table and then use insert select....
Thanks a lot
We have a mirror database concept where data from t1 table is copied into t2 & t3 tables.
All t1, t2 & t3 tables have same DDL definitionand everytime it is a complete delete and re-load into t2 or t3 ( based on view swap)
one of the Main source tables (t1) have around 93 milllion records.
Problem is, this re-load takes around 6 minutes to complete.
Is there any way we can improve the copy time ?
we are following this same approach for 350 tables, which are deleted and re-loaded in sequence. It takes close to 30 minutes to perform mirror copy.
Is there any better approach for this?
For huge volume, generally you want the target table to be empty and same structure as source, but with no SIs / JIs.
Drop SIs, Delete, Insert/Select, re-Create SIs.
The Delete should be done in Teradata mode with no BT/ET, or else the Delete and subsequent ET / Commit should be issued together as a multi-statement request.
You may also want to copy statistics values from the original table rather than re-collecting.
The best way to decrease runtime would be removing some NUSIs.
Why do you have so many NUSIs, this did you check if they're actually used?
Yeah. Most of them being used in different reports.
Some are multicolumn NUSI, and most are single column NUSI.
Our purpose is to do mirror copy from OLTP to MART.
Instead of loading entire records, I'm thinking to load only the changed records to MART to reduce copying time.
But even MINUS is of no use, as it takes much time and spool.
Will temporal be of any use ?
Help me with a query to purge data from the below table. This table has 670,235,825,680 rows. I'm trying to delete around 164,154,526,390 rows using cal_dt column.
del from pr_us_inv.str_inv_dly where cal_dt < '2010-01-01';
del from pr_us_inv.str_inv_dly where cal_dt between '2012-01-01' and '2013-12-31';
Both the above queries are consuming much time due to huge table size (33 Tb).