# 创建范围分区表
CREATE TABLE BIG_TABLE (
ID BIGINT NOT NULL,
DATA_DATE DATE NOT NULL,
DATA_VALUE DECIMAL(20, 6),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_BIG_TABLE PRIMARY KEY (ID, DATA_DATE)
) PARTITION BY RANGE (DATA_DATE)
(
PARTITION P202601 STARTING ‘2026-01-01’ ENDING ‘2026-01-31’,
PARTITION P202602 STARTING ‘2026-02-01’ ENDING ‘2026-02-28’,
PARTITION P202603 STARTING ‘2026-03-01’ ENDING ‘2026-03-31’,
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’
);

# 分区裁剪查询
SELECT * FROM BIG_TABLE
WHERE DATA_DATE BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;

# 查看分区信息
SELECT
TABNAME,
DATAPARTITIONNAME,
LOWVALUE,
HIGHVALUE
FROM SYSCAT.DATAPARTITIONS
WHERE TABNAME = ‘BIG_TABLE’;

# 添加新分区
ALTER TABLE BIG_TABLE
ADD PARTITION P202605 STARTING ‘2026-05-01’ ENDING ‘2026-05-31’;

# 分离旧分区
ALTER TABLE BIG_TABLE
DETACH PARTITION P202601 INTO TABLE BIG_TABLE_OLD;

# 删除旧分区数据
DROP TABLE BIG_TABLE_OLD;

# 创建MDC表
CREATE TABLE MDC_SALES (
SALE_ID BIGINT NOT NULL,
SALE_DATE DATE NOT NULL,
REGION VARCHAR(50) NOT NULL,
PRODUCT_ID VARCHAR(50) NOT NULL,
QUANTITY INTEGER,
AMOUNT DECIMAL(18, 2),
CONSTRAINT PK_MDC_SALES PRIMARY KEY (SALE_ID)
) ORGANIZE BY (
SALE_DATE,
REGION,
PRODUCT_ID
);

# MDC查询
SELECT * FROM MDC_SALES
WHERE SALE_DATE = ‘2026-04-08’
AND REGION = ‘NORTH’;

# 查看MDC块信息
SELECT
TABNAME,
BLOCK_SIZE,
BLOCK_SIZE_UNITS
FROM SYSCAT.TABLES
WHERE TABNAME = ‘MDC_SALES’;

# 创建复合索引
CREATE INDEX IDX_BIG_TABLE_DATE_VALUE ON BIG_TABLE(DATA_DATE, DATA_VALUE);

# 创建包含列索引
CREATE INDEX IDX_BIG_TABLE_COVER ON BIG_TABLE(DATA_DATE)
INCLUDE (DATA_VALUE, CREATE_TIME);

# 创建分区索引
CREATE INDEX IDX_BIG_TABLE_PART ON BIG_TABLE(DATA_DATE, ID)
PARTITIONED;

# 查看索引使用情况
SELECT
SUBSTR(INDNAME, 1, 30) AS INDNAME,
TABNAME,
COLNAMES,
NUMRIDS,
NLEAF,
NLEVELS,
CLUSTERRATIO
FROM SYSCAT.INDEXES
WHERE TABNAME = ‘BIG_TABLE’;

# 查看索引统计信息
SELECT
INDNAME,
TABNAME,
CARD,
FULLKEYCARD,
CLUSTERFACTOR
FROM SYSCAT.INDEXES
WHERE TABNAME = ‘BIG_TABLE’;

# 使用索引提示
SELECT * FROM BIG_TABLE
WHERE DATA_DATE = ‘2026-04-08’
OPTIMIZE FOR 100 ROWS;

# 使用FETCH FIRST限制结果集
SELECT * FROM BIG_TABLE
WHERE DATA_DATE = ‘2026-04-08’
FETCH FIRST 100 ROWS ONLY;

# 避免全表扫描
# 确保WHERE条件使用索引列

# 使用索引列排序
SELECT * FROM BIG_TABLE
WHERE DATA_DATE = ‘2026-04-08’
ORDER BY DATA_VALUE;

# 使用索引列分组
SELECT DATA_DATE, COUNT(*)
FROM BIG_TABLE
WHERE DATA_DATE >= ‘2026-04-01’
GROUP BY DATA_DATE;

# 启用并行查询
UPDATE DATABASE CONFIGURATION USING DFT_DEGREE 4;

# 设置并行度
SET CURRENT DEGREE = 4;

# 查询时指定并行度
SELECT * FROM BIG_TABLE
WHERE DATA_DATE >= ‘2026-04-01’
DEGREE 4;

# 查看并行执行计划
EXPLAIN PLAN FOR
SELECT * FROM BIG_TABLE
WHERE DATA_DATE >= ‘2026-04-01’;

SELECT * FROM EXPLAIN_OPERATOR
WHERE OPERATOR_TYPE = ‘BTQ’;

# 配置并行参数
UPDATE DATABASE CONFIGURATION USING INTRA_PARALLEL YES;
UPDATE DATABASE CONFIGURATION USING MAX_QUERYDEGREE 8;

# 监控并行查询
SELECT
AGENT_ID,
DEGREE_OF_PARALLELISM,
TOTAL_CPU_TIME
FROM SYSIBMADM.APPLICATIONS
WHERE DEGREE_OF_PARALLELISM > 1;

# 使用MQT优化
CREATE TABLE MQT_BIG_TABLE_SUMMARY AS (
SELECT
DATA_DATE,
COUNT(*) AS RECORD_COUNT,
SUM(DATA_VALUE) AS TOTAL_VALUE,
AVG(DATA_VALUE) AS AVG_VALUE
FROM BIG_TABLE
GROUP BY DATA_DATE
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE MQT_BIG_TABLE_SUMMARY;

# 使用CTE优化
WITH DAILY_SUMMARY AS (
SELECT
DATA_DATE,
COUNT(*) AS RECORD_COUNT,
SUM(DATA_VALUE) AS TOTAL_VALUE
FROM BIG_TABLE
WHERE DATA_DATE >= ‘2026-04-01’
GROUP BY DATA_DATE
)
SELECT * FROM DAILY_SUMMARY
WHERE RECORD_COUNT > 1000;

# 使用临时表优化
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_DATA AS (
SELECT * FROM BIG_TABLE
WHERE DATA_DATE = ‘2026-04-08’
) DEFINITION ONLY
ON COMMIT PRESERVE ROWS
NOT LOGGED;

INSERT INTO SESSION.TEMP_DATA
SELECT * FROM BIG_TABLE
WHERE DATA_DATE = ‘2026-04-08’;

SELECT * FROM SESSION.TEMP_DATA;