get past 6 month numbers from a calendar table in TeraData.

SELECT
year_of_calendar,
month_of_year
FROM
sys_calendar.calendar
WHERE
calendar_date<=current_date AND EXTRACT(month FROM calendar_date)<> EXTRACT(MONTH FROM current_date) AND
--because you want last 6 months and not current month
calendar_date>current_date-INTERVAL '6' MONTH
GROUP BY 1,2

ORDER BY 1 DESC, 2 DESC;

Results set  look like this:

Yr Mo
2010 03
2010 02
2010 01
2009 12
2009 11
2009 10

Product Join and Merge Join and Hash Join

A product join, every row of the left table to every row of the right table. Typically it is chosen as the join method, when one of the tables is small. Since it basically needs to read every row of the left table once, and every row of the right table n times, where n is the number of rows in the left table, it can be expensive to execute, if the number of rows in the left table is large.

The typical alternative is merge join. The extra step, however, that is needed to run a merge join is to sort the two source tables. If the tables are already in sorted order this step is not necessary. A merge join executes very fast, since it only reads both tables once. In addition, it is possible, depending on the join selectivities, that not all the datablocks of both tables need to be read.

The hash join does not require that both tables are sorted. The smaller table/spool is "hashed" into memory (sometimes using multiple hash partitions). Then, the larger table is scanned and for each row, it looks up the row from the smaller table in the hashed table that was created in memory. If the smaller table must be broken into partitions to fit into memory, the larger table must also be broken into the same partitions prior to the join.

So, the main advantage of the hash join is that the big table does not have to be sorted and the small table can be much larger than for a product join.

NOPI feature in Teradata 13.0

The purpose of teradata 13.0 feature that allows us to create table with no primary index is to improve performance of fastload. because there is no primary index for the rows of a NOPI table the rows are not hashed to an AMP based on their primary index.Instead , Teradata Database either hashes on the Query ID for a row, or it uses a different algorithm to assign the row to its home AMP once it reaches the AMP onto which it has been FastLoaded. The Teradata Database then generates a RowID for each row in a NoPI table by randomly selecting an arbitrary hash bucket that an AMP owns and using it to generate a RowID. Using a NoPI table as a staging table for such applications avoids the row redistribution and sorting required for primary-indexed staging tables. Another advantage of NoPI tables is that you can quickly load data into them and be finished with the acquisition phase of the utility operation, which frees client resources for other applications. LIMITATIONS : You cannot create a NoPI queue table. • You cannot create a NoPI error table. • You cannot create a NoPI table as a SET table. The default table type for NoPI tables in both Teradata and ANSI session modes is always MULTISET. • If neither PRIMARY INDEX (column_list) nor NO PRIMARY INDEX is specified explicitly in a CREATE TABLE request, then whether the table is created with or without a primary index generally depends on the setting of the DBS Control flag DisableNoPI • NoPI tables cannot specify a permanent journal. • NoPI tables cannot specify an identity column. • Hash indexes cannot be defined on NoPI tables because they inherit the primary index of their underlying base table, and NoPI tables have no primary index. • SQL UPDATE triggers cannot update a NoPI table. • SQL UPDATE and UPDATE (Upsert Form) requests cannot update a NoPI target table. SQL UPDATE and UPDATE (Upsert Form) requests can update a primary-indexed target table from a NoPI source table. • SQL MERGE requests cannot update or insert into a NoPI target table. SQL MERGE requests can update or insert into a primary-indexed target table from a NoPI source table. • You cannot load rows into a NoPI table using the MultiLoad utility.

difference between TRUNCATE and DELETE

TRUNCATE :
ddl command.
truncate being a ddl is auto commit.
we can only truncate the whole table(cant use where clause)
once table is truncated we cant rollback the changes.
when a table is truncated the memory occupied is released.

DELETE :
delete is a dml command and can be rolled back.
is slower than truncate as it is dml has to go through rollback segments etc.
we can use where clause with delete.
when a table is deleted memory occupied is not released

difference between OLAP and aggregate functions.

OLAP functions are similar to aggregate functions in that they:
Operate on groups of rows (like the GROUP BY clause)
Can filter groups using QUALIFY (like the HAVING clause)

OLAP functions are unlike aggregate functions because they:
Return a data value for each qualifying row - not group.
May not be performed within subqueries.

FastExport sessions and Bteq Export

advantages of FastExport over Bteq Export :
FastExport Has
Full Restart Capability
uses multiple sessions
uses Block I/O.


By default FastExport uses one sessions for each AMP.
Maximum specification must be greater than zero and no more than
the total number of AMPs on the system.

All AMP Operations vs Full Table Scan

All AMP Operation (NUSI) :
1. The SQL is submitted, specifying a NUSI.The hashing algorithm calculates a row hash value for
the NUSI .
2. All AMPs are activated to find the hash value of the NUSI in their index subtables. The AMPs
whose subtables contain that value become the participating AMPs in this request The other
AMPs discard the message.
3. Each participating AMP locates the row IDs (row hash value plus uniqueness value) of the base
rows corresponding to the hash value.
4. The participating AMPs access the base table rows, which are located on the same AMP as the
NUSI subtable.
5. The qualifying rows are sent over the BYNET to the PE, and the PE sends the answer set on to
the client application .


Full-Table Scan : (Accessing data with out indexes) :
If the request does not use a defined index, the Teradata Database does a full-table scan. A full-table scan is another way to access data without using Primary or Secondary Indexes. In evaluating an SQL request, the Optimizer examines all possible access methods and chooses the one it believes to be the most efficient.

A request could turn into a full-table scan when:
The SQL request searches on a NUSI column with many duplicates.
An SQL request uses a non-equality WHERE clause on an index column.
An SQL request uses a range WHERE clause on an index column.

For all requests, you must specify a value for each column in the index