select * from dbc.PartitioningConstraintsV where databasename='tutorial_db' and tablename='employee';
Rows are sents to their appropriate AMPs based on row hash. By default rows are ordered within AMP by row Id. For example,Primary index for the table employee is emp_no.
LOCK ROW FOR ACCESS SELECT * FROM tutorial_db.employee where hire_date between '1981-02-01' AND '1981-02-28';
Partitioned Primary Index : Now Partitioned Primary Index is added on the hiredate while the primary index is same as before i.e emp_no. Rows will go to exactly same AMP as before
Syntax/Example :
CREATE MULTISET TABLE tutorial_db.employee
(
emp_no INTEGER,
emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
manager_id INTEGER,
hire_date DATE FORMAT 'yyyy-mm-dd',
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER)
PRIMARY INDEX ( emp_no )
PARTITION BY range_n(
hire_date BETWEEN date '1981-01-01' and '1988-12-31' Each interval '1' Month,
NO range OR UNKNOWN );
LOCK ROW FOR ACCESS SELECT * FROM tutorial_db.employee where hire_date between '1981-02-01' AND '1981-02-28';
Question: How many partition on each AMP will need to be read by above query?
Answer: 1
Teradata also support Case(CASE_N) & Range(RANGE_N) as a partitioning option.
CREATE MULTISET TABLE tutorial_db.employee(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER
)
Primary Index(emp_no)
PARTITION BY CASE_N
(dept_no = 100
,dept_no = 200
,dept_no = 300, NO CASE, UNKNOWN
);
CREATE MULTISET TABLE tutorial_db.employee(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER
)
Primary Index(emp_no)
PARTITION BY Range_N(hire_date BETWEEN '1981-06-01' AND '1982-06-01' EACH INTERVAL '1' DAY,
NO RANGE OR UNKNOWN );
LOCK ROW FOR ACCESS SELECT PARTITION,hire_date FROM tutorial_db.employee;
SELECT RANGE_N(hire_date BETWEEN date '1981-01-01' and '1988-12-31' EACH INTERVAL '1' MONTH,
NO RANGE OR UNKNOWN ) AS Partition#,hire_date FROM tutorial_db.employee;
Note: For non-PPI tables,above query will return only probable partition numbers where data may be stored if that particular PPI is added on the table.
SELECT * FROM tutorial_db.employee WHERE PARTITION=2;
Handling Unknown values: Different conditions and combinations to handle unknown values and store them into separate or one partitions. Number of partition will depend on how conditions are specified while defining PPI, if 'OR' is used between clauses then only 1 partition will be created for unknown & NULL values and if comma(,) is used then 2 partitions will be created for unknown & NULL values.
*** Failure 5728 Partitioning violation for table tutorial_db.employee.
CREATE MULTISET TABLE tutorial_db.pradeep_employee_test
(
emp_no INTEGER,
emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
manager_id INTEGER,
hire_date DATE FORMAT 'yyyy-mm-dd',
hire_year INTEGER,
hire_month INTEGER,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER)
PRIMARY INDEX ( emp_no )
PARTITION BY (
RANGE_N(hire_year BETWEEN 1980 AND 1988 EACH 1),
RANGE_N(hire_month BETWEEN 1 AND 12 Each 1,
NO RANGE OR UNKNOWN ));