Some tips on Basics!

Different types of Tables in Teradata:

Permenant Tables:
1. SET table - Strictly NO duplicate values [By-Default]
2. MULTISET table - Allows Duplicate Values.

Temporary Tables:
1. Global Temporary Table - supports [Compression in table],
when session logged out, Table persists and its record gets
vanished.
2. Volatile Table - When session logged out, TABLE and its
records are not available further.

Derived table: stores the derived result from a subquery.

It uses only spool space, so no table definition, permission needed.

simple query

select * from (select max(sal) from emp) DT (Max_sal)

answer:
derived table(DT) has Max salary and result would be

Max_sal- here subquery result is maintained in derived table
-------
25000

3. derived table auto drops after the query ends

Basics of Teradata Database

What exactly is a Teradata Database?

• Teradata Database is a RDMS (Relational Database Management System) which helps in driving the company’s Data warehouse.

• It provides foundation for the company to grow, compete, evolve business by getting answers to a new generation of questions (typically “what if” queries)

• It supports business growth from gigabytes to whooping hundreds of Terabytes

• Teradata Database is an Open system and compliant with major support from ANSI standards

• It works on UNIX MP-RAS, WINDOWS and LINUX operating systems, connects with other client through Network Attached channel (TCP/IP) and Channel connection for Mainframe.

Why Teradata Database?

•  Ability to keep up with rapid changes and ever growing demand from business (through Scalabilty)

•  It supports and maintains

     o Enterprise Data warehousing

     o Active Data warehousing

     o CRM

     o DataMarts

What makes Teradata Database unique?

1. Single Data store

* Doesn’t replicate data for different requests from varied clients

* Concurrent approach, avoids data replication for each requests

2. Scalability

*  Provides Linear Scalability, slope of 1.

*  If you add a Node (memory block), increases performance without delayed throughput

* Supports terabytes of data. (10power12)

3. Unconditional Parallelism

* Multiple processors work together to accomplish a task

4. Parallel aware Optimizer

* Aware of all components present in the system

* It determines the Least Expensive path (time-wise)

5. Ability to model the business

* Provides Single View of Business

* Focus is on the whole-Business Model and on individual model

-- We will focus on classical architecture of TeradataDB in next post.