partitioned_table_view_Test_Data_and_Examples.sql.txt

-- Create the main table (partitioned table)

CREATE TABLE invoices (
    invoice_id VARCHAR(20) PRIMARY KEY,
    document_date DATE NOT NULL,
    material_code VARCHAR(20),
    quantity DECIMAL(10,2),
    amount DECIMAL(12,2),
    approval_status VARCHAR(10),
    data_control_scope VARCHAR(10),
    sales_org VARCHAR(10),
    valuation_order_flag VARCHAR(1),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (invoice_id);

CREATE TABLE invoices_2020 PARTITION OF invoices FOR VALUES FROM ('INV-2020-00000') TO ('INV-2020-99999');
CREATE TABLE invoices_2021 PARTITION OF invoices FOR VALUES FROM ('INV-2021-00000') TO ('INV-2021-99999');
CREATE TABLE invoices_2022 PARTITION OF invoices FOR VALUES FROM ('INV-2022-00000') TO ('INV-2022-99999');
CREATE TABLE invoices_2023 PARTITION OF invoices FOR VALUES FROM ('INV-2023-00000') TO ('INV-2023-99999');
CREATE TABLE invoices_2024 PARTITION OF invoices FOR VALUES FROM ('INV-2024-00000') TO ('INV-2024-99999');
CREATE TABLE invoices_2025 PARTITION OF invoices FOR VALUES FROM ('INV-2025-00000') TO ('INV-2025-99999');

CREATE INDEX idx_invoices_2020_date ON invoices_2020(document_date);
CREATE INDEX idx_invoices_2021_date ON invoices_2021(document_date);
CREATE INDEX idx_invoices_2022_date ON invoices_2022(document_date);
CREATE INDEX idx_invoices_2023_date ON invoices_2023(document_date);
CREATE INDEX idx_invoices_2024_date ON invoices_2024(document_date);
CREATE INDEX idx_invoices_2025_date ON invoices_2025(document_date);

CREATE INDEX idx_invoices_valuation ON invoices(valuation_order_flag);
CREATE INDEX idx_invoices_control_scope ON invoices(data_control_scope);
CREATE INDEX idx_invoices_sales_org ON invoices(sales_org);
CREATE INDEX idx_invoices_approval_status ON invoices(approval_status);


CREATE OR REPLACE VIEW v_invoices AS
SELECT 
    invoice_id,
    document_date,
    material_code,
    quantity,
    amount,
    approval_status,
    data_control_scope,
    sales_org,
    valuation_order_flag,
    created_at
FROM invoices;


-- Create a new function to generate test data.
CREATE OR REPLACE FUNCTION generate_test_data()
RETURNS void AS $$
DECLARE
    year_start INT := 2020;
    year_end INT := 2025;
    current_year INT;
    i INT;
    invoice_prefix VARCHAR(10);
    sample_count INT := 1000;
    batch_size INT := 100;
BEGIN
    TRUNCATE invoices CASCADE;
    
    FOR current_year IN year_start..year_end LOOP
        RAISE NOTICE 'Generating data for year %', current_year;
        
        invoice_prefix := 'INV-' || current_year || '-';
        
        FOR i IN 1..sample_count LOOP
            INSERT INTO invoices (
                invoice_id,
                document_date,
                material_code,
                quantity,
                amount,
                approval_status,
                data_control_scope,
                sales_org,
                valuation_order_flag
            ) VALUES (
                invoice_prefix || LPAD(i::TEXT, 5, '0'),
                MAKE_DATE(current_year, 
                         FLOOR(RANDOM() * 12)::INT + 1,
                         FLOOR(RANDOM() * 28)::INT + 1),
                'MAT-' || LPAD(FLOOR(RANDOM() * 1000)::TEXT, 4, '0'),
                ROUND((RANDOM() * 1000)::NUMERIC, 2),
                ROUND((RANDOM() * 10000)::NUMERIC, 2),
                CASE WHEN RANDOM() > 0.3 THEN 'n' ELSE 'y' END,
                CASE WHEN RANDOM() > 0.2 THEN 'DD_OG' ELSE 'OTHER' END,
                CASE 
                    WHEN RANDOM() > 0.7 THEN 'DF01'
                    WHEN RANDOM() > 0.5 THEN 'DE01'
                    WHEN RANDOM() > 0.3 THEN 'DG01'
                    ELSE 'OTHER'
                END,
                CASE WHEN RANDOM() > 0.4 THEN '1' ELSE '0' END
            );
        END LOOP;
        
        RAISE NOTICE 'Year %: % records inserted', current_year, sample_count;
    END LOOP;
    
    RAISE NOTICE 'Data generation completed. Total records: %', 
        (year_end - year_start + 1) * sample_count;
END;
$$ LANGUAGE plpgsql;

-- Execute data generation.
SELECT generate_test_data();




-- Option 1: Does not use a view and uses GROUP BY ROLLUP.
EXPLAIN ANALYZE
SELECT
    COALESCE(invoices.document_date::TEXT, 'Total') AS "document_date",
    COALESCE(SUM(invoices.amount), 0) AS "amount"
FROM invoices
WHERE
    (invoices.document_date BETWEEN '2024-06-01' AND '2024-12-31' 
     OR invoices.document_date BETWEEN '2025-01-01' AND '2025-12-09')
    AND invoices.data_control_scope = 'DD_OG'
    AND invoices.valuation_order_flag = '1'
    AND invoices.approval_status IN ('y')
    AND invoices.sales_org IN ('DF01', 'DG01')
GROUP BY ROLLUP(invoices.document_date);

-- Option 2: Uses a view and uses GROUP BY ROLLUP.
EXPLAIN ANALYZE
SELECT
    COALESCE(v_invoices.document_date::TEXT, 'Total') AS "document_date",
    COALESCE(SUM(v_invoices.amount), 0) AS "amount"
FROM v_invoices
WHERE
    (v_invoices.document_date BETWEEN '2024-06-01' AND '2024-12-31' 
     OR v_invoices.document_date BETWEEN '2025-01-01' AND '2025-12-09')
    AND v_invoices.data_control_scope = 'DD_OG'
    AND v_invoices.valuation_order_flag = '1'
    AND v_invoices.approval_status IN ('y')
    AND v_invoices.sales_org IN ('DF01', 'DG01')
GROUP BY ROLLUP (v_invoices.document_date);


-- Option 3: Uses a view but does not use GROUP BY ROLLUP.
EXPLAIN ANALYZE
SELECT
    COALESCE(v_invoices.document_date::TEXT, 'Total') AS "document_date",
    COALESCE(SUM(v_invoices.amount), 0) AS "amount"
FROM v_invoices
WHERE
    (v_invoices.document_date BETWEEN '2024-06-01' AND '2024-12-31' 
     OR v_invoices.document_date BETWEEN '2025-01-01' AND '2025-12-09')
    AND v_invoices.data_control_scope = 'DD_OG'
    AND v_invoices.valuation_order_flag = '1'
    AND v_invoices.approval_status IN ('y')
    AND v_invoices.sales_org IN ('DF01', 'DG01')
GROUP BY (v_invoices.document_date);