求助：如何创建无数据的test物化视图？现有创建脚本耗时过长

Hey there! Let's break down what you need to do step by step, plus some actionable tips to speed up that initial 2-hour data load for your 20M rows. I'll cover common databases (PostgreSQL/Oracle) since syntax varies a bit.

First, we'll make the物化视图 without loading any data upfront—this skips the long initial build you're currently dealing with.

For PostgreSQL: #

Use the WITH NO DATA clause to define the view structure only:

CREATE MATERIALIZED VIEW test
AS
-- Replace this with your actual source query (the one you used in your original CREATE statement)
SELECT id, column1, column2, ... FROM your_source_table
WITH NO DATA;

This creates the view schema but leaves it empty, so it runs in seconds.

For Oracle: #

Use BUILD DEFERRED to delay data loading:

CREATE MATERIALIZED VIEW test
BUILD DEFERRED
AS
SELECT id, column1, column2, ... FROM your_source_table;

Now that the empty view exists, we'll populate it with your 20M rows. This is where we can optimize speed too.

PostgreSQL: #

-- Full refresh (since it's empty, this loads all data)
REFRESH MATERIALIZED VIEW test;

-- If you plan to do concurrent refreshes later (without locking the view), add a unique index first:
CREATE UNIQUE INDEX idx_test_id ON test(id);
-- Then future refreshes can use:
-- REFRESH MATERIALIZED VIEW CONCURRENTLY test;

Oracle: #

-- Full initial refresh
REFRESH MATERIALIZED VIEW test;

-- Or use the more flexible DBMS_MVIEW package:
EXEC DBMS_MVIEW.REFRESH('TEST', 'C'); -- 'C' = complete refresh

Set up a cron job or database scheduler to refresh the view every night.

PostgreSQL (using pg_cron extension): #

First install the extension if you haven't:

CREATE EXTENSION pg_cron;

Then schedule a nightly refresh (e.g., 2 AM):

-- Cron syntax: minute hour day month weekday
SELECT cron.schedule('refresh-test-mv', '0 2 * * *', 'REFRESH MATERIALIZED VIEW test;');

Use REFRESH MATERIALIZED VIEW CONCURRENTLY test; instead if you need to avoid locking the view during refresh.

Oracle (using DBMS_SCHEDULER): #

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'REFRESH_TEST_MV',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''TEST'', ''C''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0;', -- Runs at 2 AM daily
    enabled         => TRUE,
    comments        => 'Nightly refresh for materialized view test'
  );
END;
/

2 hours for 20M rows is slower than it needs to be. Try these optimizations:

• Drop indexes before loading: Indexes slow down bulk inserts. Delete any non-essential indexes on the物化视图 first, then rebuild them after loading.
  Example for PostgreSQL:

  DROP INDEX IF EXISTS idx_test_column1;
  REFRESH MATERIALIZED VIEW test;
  CREATE INDEX idx_test_column1 ON test(column1);
  

• Tweak database memory settings: Allocate more memory to reduce disk I/O. For PostgreSQL, increase work_mem; for Oracle, adjust PGA_AGGREGATE_TARGET.
• Parallel execution: Enable parallel processing for the refresh. In Oracle, add PARALLEL 4 to your CREATE statement (adjust the number based on your CPU cores). In PostgreSQL, increase max_parallel_workers_per_gather.
• Optimize the source query: Run your base SELECT query alone—if it's slow, add indexes to the source table or simplify filters/joins before using it in the物化 view.

内容的提问来源于stack exchange，提问作者teepu