Archives of the TeradataForum

Message Posted: Tue, 18 May 2010 @ 11:51:47 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Questions (Modes, SQL Assistant, Indexes)

From:		Dieter Noeth

Dmitriy Boyko wrote:

1. What is the difference between ANSI SQL and Teradata SQL? I mean does anyone use ANSI Sql and in what cases?

The main differences:

ANSI

- default CASESPECIFIC chars/literals

- default MULTISET tables

- COMMIT

Teradata

- default NOT CASESPECIFIC chars/strings

- default SET tables

- BT/ET or implicit transactions

Most customers run their system in Teradata mode, but some switch to ANSI for load scripts in BTEQ:

ANSI throws more errors, whereas Teradata tries to avoid them, e.g. insert 'abcde' into a char(4) column, ANSI fails, Teradata succeeds, but truncates the string.

You'll find all the details in the manuals:

SQL Request and Transaction Processing
Chapter 9: Locking and Transaction Processing ANSI Session Mode

2. How SQL assistant estimates the total number of rows query returns before loading them to client (By default it asks the question if the total number exceeds 20K)?

It doesn't estimate :-) It's the exact number, Teradata creates the full answer set, returns that count and then starts sending row.

3. Does anyone use indexes on huge fact tables (different from primary)? What is the size of such indexes (table size (row count)/ index size). What literature about "right" index building can you suggest?

If fact tables are mainly accessed through dimension columns you're trying to get "LT/ST" or "star"-joins:

SQL Request and Transaction Processing
Chapter 4: Join Optimizations

NUPI sizes are a bit hard to pre-calculate, two columns with the same datatype might have totally different sizes depending on rows per val. For column with a large number of rows per val the size will approach 8 bytes per row (10 byte for PPI).

You'll find all the sizing details in

Database Design
Chapter 15: Database-Level Capacity Planning Considerations

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference