primary indexes sec indexes

7/28/2019 Primary Indexes Sec Indexes

1/7

PRIMARY INDEX

The physical mechanism used to assign a row to an AMPA table must have a Primary Index The Primary Index cannot be changed

UPI If the index choice of column(s) is unique, we call this aUPI(Unique Primary Index).

A UPI choice will result in even distribution of the rows ofthe table across all AMPs.

UPIs guarantee even data distribution and eliminate duplicate row checking.

NUPI If the index choice of column(s) isnt unique, we call this aNUPI(Non-Unique Primary Index).

A NUPI choice will result in even distribution of the rows ofthe table proportional to the degree of uniqueness of theindex.

Why would you choose an Index that is different from the Primary Key? Join performance Known access paths


2/7

Choosing Primary index

A Primary Index is defined at table creation.

UPICREATE TABLE sample_1

(col_a INT,col_b INT

,col_c INT)UNIQUE PRIMARY INDEX (col_b);

NUPICREATE TABLE sample_2

(col_x INT,col_y INT

,col_z INT)PRIMARY INDEX (col_x);

Note: Changing the Primary Index requires dropping and recreating thetable.


3/7

Secondary Indexes

Four basic ways to access a table:

Primary index access (one-AMP access) Secondary index access (two-or all-AMP access) Full-Table Scan (all-AMP access) Partitioned primary index access (all-AMP access)

A secondary index is an alternate path to the rows of a table.A table can have from 0 to 32 secondary indexes.Secondary indexes: Do not affect table distribution. Avoids full table scan.

Internally it creates sub tables.Add overhead, both in terms of disk space and maintenance. May be added or dropped dynamically as needed.Are chosen to improve table performance.


4/7

Choosing a Secondary Index

At table creation (CREATE TABLE) Following table creation (CREATE INDEX)

USI If the choice of column(s) is unique, it is called a USI (unique secondary index). Accessing a row via a USI typically requires 2 AMPs.

NUSI If the choice of column(s) is non-unique, it is called a NUSI (non-unique secondary

index).Accessing a row via a NUSI requires all AMPs.

USICREATE UNIQUE INDEX(employee-number) onEmployee

NUSICREATE INDEX (last-name) on employee

CREATE INDEX (last-name, first-name) on employee Secondary indexes cause an internal subtable to be built. Dropping the index causes the subtable to be deleted.


5/7

Partitioned Primary Index

It is used to speed up the range Queries. Case_N & Range_N are the arguments used in SQL to create PPI. Partition is local to each AMP. Partitioned primary indexes, introduced in Teradata Database V2R5

ALTER TABLE SalesTable

Modify PRIMARY INDEX (product_code)

DROP RANGE BETWEENDATE 2003-10-01 AND DATE

2003-10-31

ADD RANGE BETWEEN

DATE 2005-11-01 AND DATE

2005-11-30

WITH DELETE;

CREATE TABLE PPI_SalesTable (

product_code CHAR(8), sales_date DATE,

agent_id CHAR(8), quantity_sold INTEGER,

other_columns CHAR(50))

PRIMARY INDEX (product_code)

PARTITION BY RANGE_N

( sales_date BETWEEN

DATE 2003-10-01 AND DATE

2005-10-31 EACH INTERVAL

1 MONTH);

CREATE TABLE SalesTable (

product_code CHAR(8), sales_date DATE,

agent_id CHAR(8),quantity_sold INTEGER, other_columns CHAR(50))

PRIMARY INDEX (product_code);


6/7

EXPORTING DATA BY USING TSQLASSISTANT

1.Go to File.

2.Click on Export Results.Select Query (select * from table name)

And Highlight the Query and press F5 for Execution.It asks path to store or send Data.

Press ok.

Disable Export Results after Execution.


7/7

IMPORTING DATA IN TSQLASSISTANT

1.Go to File.

2.Click on Import Results.

Select Query

i.e. ins into tablename(?,?,?)

And Highlight the Query and press F5 for Execution.

It asks path to select the source.

Press ok.

Disable import Results after Execution.

primary indexes sec indexes

Documents