with_select

本文除了会讲with select，还会讲with delelte等其他，与with有关的语句。

使用

with查询

貌似速度还慢了一点，不是说是临时表，放到内存里面的吗？貌似没有什么变化，查看执行计划的时候，使用的是hash连接。

DROP TABLE IF EXISTS marked_ship_unqi2;
CREATE TABLE marked_ship_unqi2 WITH ( 
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB 
) AS 
WITH get_ship_ids AS (
    SELECT
        ship_id
    FROM
        test_scan_qian
) 
SELECT
    ship_id,
    string_agg (ship_type, '|') AS ship_type
FROM
    (
        SELECT
            ship_id,
            ship_type
        FROM
            marked_ship
        WHERE
            ship_id IN (SELECT * FROM get_ship_ids)
        GROUP BY
            ship_id,
            ship_type
    ) AS f
GROUP BY
    ship_id
DISTRIBUTED BY (ship_id);

两次结果

SELECT 11897739
Time: 71646.131 ms


SELECT 11897739
Time: 60675.651 ms

普通的查询

DROP TABLE IF EXISTS marked_ship_unqi2;
CREATE TABLE marked_ship_unqi2 WITH ( 
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB 
) AS SELECT
    ship_id,
    string_agg (ship_type, '|') AS ship_type
FROM
    (
        SELECT
            ship_id,
            ship_type
        FROM
            marked_ship
        WHERE
            ship_id IN (
                SELECT
                    ship_id
                FROM
                    test_scan_qian
            )
        GROUP BY
            ship_id,
            ship_type
    ) AS f
GROUP BY
    ship_id
DISTRIBUTED BY (ship_id);

SELECT 11897739
Time: 61671.782 ms

迁移数据

将删除数据保存到临时表，然后再从临时表内，将数据插入到新表中。

WITH tmpdel AS
(DELETE FROM emp_order_insurance WHERE empid =1002 RETURNING *) INSERT INTO emp_order_insurance_his
SELECT* FROM tmpdel;