
//K (primary key) and U (unique constraint)

CREATE TABLE TEST_PKPI (
    PI_COL1 INTEGER NOT NULL, 
    PK_COL2 INTEGER NOT NULL, 
    PU_COL3 INTEGER NOT NULL,
    PU_COL4 INTEGER NOT NULL,
    PU_COL5 INTEGER NOT NULL,
    PRIMARY KEY (PK_COL2)
    ) UNIQUE PRIMARY INDEX _ads_index_01 (PI_COL1, PU_COL5)
GO
ALTER TABLE TEST_PKPI ADD CONSTRAINT _ADS_UKEY_IVAN UNIQUE (PU_COL3)
GO
CREATE INDEX TEST_PKPI_IDX_01("PU_COL5") ON "TEST_PKPI"
GO
CREATE INDEX TEST_PKPI_IDX_02("PU_COL3", "PU_COL4", "PU_COL5") ON "TEST_PKPI"
GO

//partition primary Index(Q).
CREATE SET TABLE Orders (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE FORMAT 'YYYY-MM-DD', 
   OrderTotal INTEGER 
) 
PRIMARY INDEX(OrderNo) 
PARTITION BY RANGE_N  (
   OrderDate BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY
)

GO
//Nonpartitioned Primary table:P 
CREATE SET TABLE tablePI ,
	NO FALLBACK,
	LOG,
	DEFAULT MERGEBLOCKRATIO,
	CHECKSUM = DEFAULT ( 
	Trx_id	INTEGER,
	Trx_dt	DATE 
	) PRIMARY INDEX _ADS_INDEX_1 (Trx_id)
GO

//Join index(J)

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30) , 
   LastName VARCHAR(30) , 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );
Go

CREATE JOIN INDEX Employee_JI 
AS 
SELECT EmployeeNo,FirstName,LastName, 
DOB,JoinedDate,DepartmentNo 
FROM Employee 
PRIMARY INDEX(FirstName)
GO


