TERADATA JOIN STRATEGIES, JOIN & HASH INDEXES AND THEIR EXAMPLES

 

JOIN STRATEGIES,MERGE & PRODUCT JOIN SCREEN SHOTS(FIRST THREE)

 CONFIDENCE LEVELS ON SINGLE OR MULTIPLE TABLES

JOIN INDEX AND HASH INDEX

INDICES OBSERVATION IN A PARTICULAR DATABASE AND TABLE
-----------------------------------------------------------
SEL * FROM DBC.INDICES WHERE DATABASENAME='VINAYAKA' AND TABLENAME='DEPT'
BEFORE JOIN INDEXES
---------------------
EXPLAIN SEL EID,ENAME,DEPTID FROM EMP WHERE DEPTID=20--ALL AMP OPERATION
EXPLAIN
SELECT E.EID,E.ENAME,E.DEPTID FROM EMP E
INNER JOIN DEPT D
ON E.DEPTID=D.DEPTID --ROW DISTRIBUTION,SORTING  AND MERGE JOIN STRATEGY
AFTER JOIN INDEXES
==================
DROP JOIN INDEX  IDX;
SYN:
CREATE JOIN INDEX <INDEXNAME> AS <SEL QUERY> PRIMARY INDEX(COLS)
EX:
CREATE JOIN INDEX IDX  AS SEL EID,ENAME,DEPTID FROM EMP PRIMARY INDEX(DEPTID);
SHOW JOIN INDEX IDX;
NOTE: IF REQUIRED COLLECT STATS ON THE TABLES
   --COLLECT STATS ON EMP INDEX(EID)
   --COLLECT STATS ON DEPT INDEX(DEPTID)
EXPLAIN
SELECT E.EID,E.ENAME,E.DEPTID FROM EMP E
INNER JOIN DEPT D
ON E.DEPTID=D.DEPTID --NO ROW DISTRIBUTION,MERGE JOIN
EXPLAIN SEL EID,ENAME,DEPTID FROM EMP WHERE DEPTID=20 --SINGLE AMP OPERATION