By
default Teradata database treats first column as Primary Index
What is a B-TEQ in Teradata?.
It
is client s/w that resides on network or channel-attached host.
After
starting BTEQ,you can log on to Teradata using a TDPid (Teradata Director
Program id) with your user id and password. The TDPid identifies the instance of TD you
are going to access.
Use of BTEQ (Basic Teradata
Query program) is to submit SQL queries to Teradata Database. It works like a
interface b/w query and TD.
What
is Tera Data Explain Command ?.
The
Teradata EXPLAIN command can be added to the front of the SQL statement and
while execution it gives a detailed analysis for particular statement.
The
EXPLAIN command can be used to estimate what are the indexes to be used,how
much time a query will take and how many rows a query will be return.
This command can be used as a useful debug tool to resolve problems with long processing
times.
1) COMPRESS:
COMPRESS
is not a complex data reducing algorithm. It does not reduce repeating
characters (or bits), or character (or bit) patterns within rows, columns,
blocks or cylinders.
COMPRESS
practically eliminates the data storage of nulls (or 256 constant values) for
fixed-length, non-primary index columns.
2)
Rules for compression:
· Column must be fixed-length, 255 characters
or less, and not part of the primary index.
. The following types of data can be
compressed:Nulls, Zeros, Blanks, Any numeric data type,DATE (expressed as
COMPRESS (DATE 'yyyy-mm-dd')),
CHARACTER(up to 255 characters).
· The default setting is no compression.
The
syntax for the compression attribute is as follows:
CREATE SET TABLE tbl1,
( col1 CHAR(1) COMPRESS,
/* Compresses nulls */
col2 CHAR(1)
COMPRESS 'product' ) /*
Compresses nulls plus the
value 'product' */
PRIMARY INDEX ...;
The
compress column values are case specific. The value "PRODUCT" does
not get compressed, if "product" is the specified compression value.
The below are the advantages of data compression in Teradata.
· Compression results in shorter rows which
are generally more performant.
· Column compression provides the following
capacity and performance benefits:
Reduced capacity
Reduced I/O traffic
Moderate CPU savings
· The I/O savings correlates to the
percentage of data compressed out of a row.
· Table backups use the compressed format, so
backups will also be faster.
Disadvantage is It does not
compress varchar columns.
Multiload is a Teradata utility that will read a
unix file and update (insert, delete, update) a populated target table.
Multiload is faster
than Bteq for updating a populated table. Bteq updates 1 row at a time, where
Multiload updates blocks of rows at a time.
When Multiload is
compared to the Fastload/delete/insert method, then Multiload is faster for
volumes above 10,000 records. For volumes less than 10,000 records, the
difference is seconds, and is negligible. Multiload is faster whether the
target table has a unique primary index, or a non-unique primary index.
The Fastload
Teradata Fastload
is a utility which loads records into an empty table using blocks of records
and multiple amps.
Fastload is composed of 2 phases, the first phase reads the unix records, and
writes them to TOS buffers on the amps. The second phase reads the TOS buffers
and writes them to a TOS database table.
Fastload/delete/insert
method of updating populated target tables consists of 3 steps:
1) Fastload into a temporary table
2) Bteq delete matching rows from the target
table
3) Bteq insert into the target table.
Multiload
skips the temporary table and directly updates the target table from the unix
file.
When loading into
an empty target table, both Multiload and Fastload are about the same speed.
Multiload’s
speed is not affected by the number of rows already in the target table. The
speed is affected by the number of update records, and can be affected by the
number of error records written to the error journals.
The
following are Teradata Database Versions
.
1.
Teradata v2r5
2.
Teradata v2r6
3.
Teradata 12
4.
Teradata 13
5.
Teradata 13.10
6.
Teradata 14
No comments:
Post a Comment