USEGEAR

公告

pg連接外部數(shù)據(jù)庫的TDS_FDW 之后

pg通過tds_fdw實現(xiàn)外部表后，一般就可以是同本地表進行操作。

CREATE FOREIGN TABLE "test"."Remote_IncomeStatement" (
  "VAJ47" timestamp(6),
  "BCK01B" int4,
  "BBY01" int4,
  "VAJ25" numeric(18,4),
  "VAJ36" money
)
SERVER "HIS_SQLServer"
OPTIONS ("query" 'select  VAJ47,BCK01B  ,BBY01,VAJ25,VAJ36 from vaj1"')
;

ALTER FOREIGN TABLE "test"."Remote_IncomeStatement" 
  OWNER TO "postgres";

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."VAJ47" IS 'RIS_Date';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."BCK01B" IS 'RIS_DeptCode';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."BBY01" IS 'RIS_ItemCode';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."VAJ25" IS 'RIS_SL';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."VAJ36" IS 'RIS_JE';

注意點：不要使用外庫的視圖作為外部表。因為這個速度相當慢。

如果對外部表進行相關連接查詢，建議通過物化視圖來實現(xiàn)。

CREATE MATERIALIZED VIEW "test"."MV_IncomeStatement"
AS
SELECT "Remote_IncomeStatement"."VAJ47",
    "Remote_IncomeStatement"."BCK01B",
    "Remote_IncomeStatement"."BBY01",
    "Remote_IncomeStatement"."VAJ25",
    "Remote_IncomeStatement"."VAJ36"
   FROM test."Remote_IncomeStatement";

ALTER MATERIALIZED VIEW "test"."MV_IncomeStatement" OWNER TO "postgres";

為什么？還是因為速度

直接使用外部表實現(xiàn)	物化視圖實現(xiàn)
SELECT a.*,b."PID_ServiceName" FROM "test"."Remote_IncomeStatement" a LEFT JOIN "public"."PayItems_Dict" b ON a."BCK01B"::TEXT=b."PID_PayCode" where to_char(a."VAJ47", 'yyyy-mm') ='2021-06'	SELECT a.*,b."PID_ServiceName" FROM "test"."MV_IncomeStatement" a LEFT JOIN "public"."PayItems_Dict" b ON a."BCK01B"::TEXT=b."PID_PayCode" where to_char(a."VAJ47", 'yyyy-mm') ='2021-06'
共300條記錄運行時間 0.440s	共300條記錄運行時間 0.005s
	運行時間：0.35s　　REFRESH MATERIALIZED VIEW "test"."MV_IncomeStatement"

注意物化視圖數(shù)據(jù)不是最新的。需要最新數(shù)據(jù)要執(zhí)行：REFRESH MATERIALIZED VIEW "test"."MV_IncomeStatement"; 運行時間：0.35s。

效率還是非常客觀的。

posted on 2022-10-14 16:19 USEGEAR 閱讀(253) 評論(0) 收藏舉報

刷新頁面返回頂部

USEGEAR

導航

公告

pg連接外部數(shù)據(jù)庫的TDS_FDW 之后