SriniTera

Usage-1 It returns a rows randomly from Teradata database. Syntax for SAMPLE function: SAMPLE [WITH REPLACEMENT]    [RANDOMIZED ALLOCATION]                  [WHEN <condition>  THEN]                  {<number-of-rows> | <percentage>}    […,<number-of-rows> | <percentage>]                  [ELSE {<number-of-rows> |    <percentage }   END] Usage 2 It allows the user to get absolute number of rows or percentage of rows. Usage 3 It also allows the rows to get from multiple samples How to get random rows from a table: SELECT * from sample_table SAMPLE 5; How to get certain percentage of rows: SELECT * from sample_table SAMPLE .30; How to get multiple SAMPLE data from a single tables with out duplicates: SELECT * from sample_...

Indexing is one of the most important features of the Teradata RDBMS. In the Teradata RDBMS, an index is used to define row uniqueness and retrieve data rows, it also can be used to enforce the primary key and unique constraints for a table. The Teradata RDBMS support five types of indexes Unique Primary Index (UPI)  Unique Secondary Index (USI)  Non-Unique Primary Index (NUPI)  Non-Unique Secondary Index (NUPI)  Join Index  The typical index contains two fields: a value and a pointer to instances of that value in a data table. Because the Teradata RDBMS uses hashing to distribute rows across the AMPs, the value is condensed into an entity called a row hash, which is used as the pointer. The row hash is not the value, but a mathematically transformed address. The Teradata RDBMS uses this transformed address as a retrieval index. The following rules apply to the indexes used in the Teradata Relation database: An index is a scheme used to...

Secondary Indexes provide an alternate path to the data, and should be used on queries that run thousands of times. Teradata Secondary Index Teradata runs extremely well without secondary indexes, but since secondary indexes use up space and overhead, they should only be used on "KNOWN QUERIES" or queries that are run over and over again. Once you know the data warehouse, environment you can create secondary indexes to enhance its performance. Whenever a secondary index is created, Teradata creates a secondary index subtable on each AMP. All secondary index subtables contain: Secondary Index Value  Secondary Index Row ID  Primary Index Row ID Secondary indexes are two types A UNIQUE Secondary Index (USI) will improve data retrieval and can also be used to enforce uniqueness on a primary key. Typically, only two AMPs are used on a Unique Secondary Index (USI) access. A Non-Unique Secondary Index (NUSI) is AMP local and is an All AMP operation, but not a full tab...

#Primary Index in Teradata Primary index is created while creating Table. We no need to worry about definition. If we forget to mention Primary index, Teradata will create it automatically. Create table samples.orders (Order_no INT, Amount DEC (5,2), Total INTEGER, Item_name VARCHAR(10) Unique Primary Index(Order_No); How Teradata selects UPI? It selects first column in the Table as UPI (Unique Primary Index). By default Teradata create UPI. NUPI - Is also called non-unique primary index. During the table definition we can give NUPI. Especially we are loading data into Staging-tables from mainframe or other server. The disadvantage of NUPI is all the duplicate rows, grouped together in same AMP. It causes for skewing. But this kind of skewing is acceptable. Where we need to give PI in SELECT statement. For efficient performance, we need to give PI in where clause. SELECT * from Samples.order where Order_No = 10005; Keep reading for more on Teradata BI.

CREATE UNIQUE INDEX ( department )   ON tbl_employee ; If a Teradata SQL request uses secondary index values in a WHERE constraint, the optimizer may use the rowID in a secondary index subtable to access the qualifying rows in the data table. If a secondary index is used only periodically by certain applications and is not routinely used by most applications, disk space can be saved by creating the index when it is needed and dropping it immediately after use. A unique secondary index is very efficient, it typically allows access of only two AMPs, requires no spool file, and has one row per value, therefore, when a unique secondary index is used to access a row, two AMPs are involved. Unique secondary indexes can thus improve performance by avoiding the overhead of scanning all AMPs. Relates Posts All about Secondary Indexes in Teradata