Scribd
Upload
9 views4 pages

AMPS Indexes and Data Loading Techniques

The document discusses various Teradata concepts including indexes, utilities like FastLoad and MultiLoad, joining strategies like merge join and hash join, and performance tuning. It also includes examples of SQL queries on sample multiset tables to demonstrate concepts like grouping, aggregation using CASE statements, and finding minimum/maximum timestamps by ID.

Uploaded by

Anonymous r2QPnMZRHF
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
9 views4 pages

AMPS Indexes and Data Loading Techniques

The document discusses various Teradata concepts including indexes, utilities like FastLoad and MultiLoad, joining strategies like merge join and hash join, and performance tuning. It also includes examples of SQL queries on sample multiset tables to demonstrate concepts like grouping, aggregation using CASE statements, and finding minimum/maximum timestamps by ID.

Uploaded by

Anonymous r2QPnMZRHF
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

Indexes & utilization of AMPS

1) UPI - 1 AMP
2) NUPI - 1 AMP
3) USI - 2 AMP
4) NUSI - All amps
Columnar Table to google it , new concept

Utilities
1) Fastload
FastLoad utility is used to load data into empty tables. Since it does not use
transient journals, data can be loaded quickly. It doesn't load duplicate rows
even if the target table is a MULTISET table.
2) MultiLoad

Joining strategies
1) Merge join PI to PI join between the tables but no. of rows differ.
2) Product join One -many kind of operation in the join
3) Nested Join4) Hash Join-

Performance tuning
1) PI to PI join thats merge join will give faster results.

-- Assignement 1--Create multiset table PW_test1


(
COL1 Integer
,COL2 char(1)
,COL3 Integer

)
PRIMARY INDEX (COL1);

COL1 COL2 COL3


1

101

10

102

50

101

10

102

100

101

10

102

30

101

30

102

35

101

10

10

102

15

-- Get the sum in below manner

COL1 Sum(<CASE expression>)


1

101

-10

102

100

--Using CASE

Select COL1
,SUM
(
CASE WHEN COL2 ='-' THEN -COL3
ELSE COL3
END
)
FROM PW_test1
GROUP BY COL1;

-- Using Concatenate

Select COL1, SUM(COL2 || COL3)


From PW_test1
group by COL1;

select Id,SUBID,Start_time,End_time
from PW_datetime
where Start_time in (select Min(start_time) from PW_datetime group by ID)
OR
End_time in (select Max(end_time) from PW_datetime group by ID)
ORDER BY 1,2

1
2
3
4
5
6
7
8
9

ID
101
101
101
102
102
102
103
103
103

SUBID
1
2
4
1
2
4
2
4
5

Start_time
End_time
9/8/2016 11:40:20.1230009/8/2016
9/8/2016 11:40:19.1230009/8/2016
9/8/2016 11:40:19.1231239/8/2016
9/8/2016 11:40:18.1230009/8/2016
9/8/2016 11:40:22.1230009/8/2016
9/8/2016 11:40:19.1231239/8/2016
9/8/2016 11:40:22.1230009/8/2016
9/8/2016 11:40:19.1231239/8/2016
9/8/2016 11:40:40.1230009/8/2016

11:45:19.085000
11:41:19.085000
11:41:25.085000
11:41:18.085000
11:45:12.085000
11:41:25.085000
11:45:12.085000
11:41:25.085000
12:00:40.085123

select id,subid,endt_time Maxtime,start_time


from ds_test2
where endt_time in (select (MAX(CAST(endt_time AS TIMESTAMP(6)))) Maxtime
from ds_test2
GROUP BY id )
OR start_time in (select (MAX(CAST(start_time AS TIMESTAMP(6)))) Maxtime
from ds_test2
GROUP BY id )

Partition primary index : [Link]


Teradata Architecture :
[Link]

You might also like