This article discusses the interplay of Partitioning, Partition Change Tracking and Query Rewrite in relation to Materialized Views.
Contents
- Introduction
- Demonstrations
- 1: Multi-column composite partitioning
- 2: Simple 1-Dimensional Range Partitioning
- 3: Interval Partitioning
- 4: Composite (Range-List) Partitioning
- 5: Composite (Range-Range) Partitioning
- 6: Mismatching Partitioning
- 7: Partition on Accounting Period, Subpartition on Fiscal Year
- Conclusion
Introduction
In the Oracle database, Materialized Views can be used to create pre-generated reporting tables. A view of the data based on a SQL query is materialized into a table. That query may restrict the rows and columns and may aggregate the data. An application can reference the materialized view directly, or the Oracle database can 'rewrite' SQL queries on the original tables that are similar to query in a materialized view to use that materialized view instead.
By default, QUERY_REWRITE_INTEGRITY is enforced, which means Query rewrite works only with materialized views that are up to date (i.e. the underlying data hasn't changed since the materialized view was last refreshed). This note deals with that scenario. Optionally, rewrite integrity can be configured to allow rewrite to occur on stale materialized views (this is called 'stale tolerated'). It can be set at system or session-level.
Partition Change Tracking (PCT) is 'a way of tracking the staleness of a materialized view on the partition and subpartition level'. If both the materialized view and at least one underlying table in the view are similarly partitioned, then Oracle can determine the relationship between partitions and subpartitions in the underlying table and those in the materialized view. The database can track not just whether any partition in the underlying tables has been updated since the last refresh of the materialized view, but which ones. During SQL parse, if after partition pruning of the query on the underlying tables, none of the remaining partitions are stale then the query can still be rewritten. Also, it is possible to refresh just the stale partitions in the materialized view, those that correspond to the underlying partitions that have been updated since the last refresh.
Query rewrite is a cost-based SQL transformation. Therefore, it will only occur if the optimizer calculates that the rewritten query has a lower cost. If I refresh the materialized view in non-atomic mode, then the materialized view will be truncated and populated in direct-path mode, thus the data can be compressed (either with basic compression, or Hybrid-Columnar Compression if on an engineered platform) without the need of the Advanced Compression Licence. This will further reduce the size and cost of accessing the materialized view and increase the likelihood of query rewrite.
I have written a series of blogs about retrofitting partitioning into existing applications. One of my examples was based on PeopleSoft General Ledger reporting in which I discussed options for partitioning the ledger such that there is a different partition for each accounting period. Once an accounting period is closed the application generally doesn't usually change it further. It should be possible to create partitioned materialized views on the ledger table to support GL reporting using query rewrite. As the application continues to insert data into the partition for the current accounting period, that partition will quickly become stale and queries on that partition won't be rewritten. However, it is common for customers to run suites of reports overnight, and those could be run after a materialized view refresh and make good use of query rewrite.
However, as I modelled this, I ran into a few problems that reveal some of the behaviour of PCT, query rewrite and materialized view refresh. I have created a number of test scripts that illustrate various scenarios that I will describe below. The full scripts are available on Github.
Documented Preconditions and Limitations
Oracle's documentation sets out a number of preconditions for PCT.
- Partitioned tables must use either range, list or composite partitioning with range or list as the top-level partitioning strategy. - Therefore, hash partitioning is not supported. What about interval partitioning? See demonstration 3.
- The top-level partition key must consist of only a single column. - If, as I proposed, the ledger table is range partitioned on the combination FISCAL_YEAR and ACCOUNTING_PERIOD then PCT will not work (see demonstration 1: Multi-column composite partitioning). So, are other partitioning strategies viable?
- The materialized view must contain either the partition key column or a partition marker or ROWID or join dependent expression of the detail table.
- If you use a GROUP BY clause, the partition key column or the partition marker or ROWID or join dependent expression must be present in the GROUP BY clause.
Note that, while partition change tracking tracks the staleness on a partition and subpartition level (for composite partitioned tables), the level of granularity for PCT refresh is only the top-level partitioning strategy. Consequently, any change to data in one of the subpartitions of a composite partitioned-table will only mark the single impacted subpartition as stale and have the rest of the table available for rewrite, but the PCT refresh will refresh the whole partition that contains the impacted subpartition.
Demonstrations
In each of the following demonstrations, I will create a copy of the PeopleSoft Financials General Ledger table PS_LEDGER, populate it with random data to simulate 2½ years of actuals and 4 years of budget data. The table will be partitioned differently in each demonstration. I will also create one or two materialized views that will also be partitioned. Then I will add data for another accounting period and look at how the materialized view refresh and query rewrite behave when one partition is stale.
The tests have been run on Oracle 19.9. Query rewrite is enabled, and rewrite integrity is enforced.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
Demonstration 1: Multi-column composite partitioning
I will start with my usual composite partitioning of the ledger table on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to permit sub-partitioning on LEDGER.
CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
,ledger VARCHAR2(10) NOT NULL
,account VARCHAR2(10) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR,ACCOUNTING_PERIOD)
SUBPARTITION BY LIST (LEDGER)
SUBPARTITION TEMPLATE
(SUBPARTITION actuals VALUES ('ACTUALS')
,SUBPARTITION budget VALUES ('BUDGET'))
(PARTITION ledger_2018 VALUES LESS THAN (2019,0) PCTFREE 0 COMPRESS
--
,PARTITION ledger_2019_bf VALUES LESS THAN (2019,1) PCTFREE 0 COMPRESS
,PARTITION ledger_2019_01 VALUES LESS THAN (2019,2) PCTFREE 0 COMPRESS
…
,PARTITION ledger_2019_12 VALUES LESS THAN (2019,13) PCTFREE 0 COMPRESS
,PARTITION ledger_2019_cf VALUES LESS THAN (2020,0) PCTFREE 0 COMPRESS
--
,PARTITION ledger_2020_bf VALUES LESS THAN (2020,1)
,PARTITION ledger_2020_01 VALUES LESS THAN (2020,2)
…
,PARTITION ledger_2020_12 VALUES LESS THAN (2020,13)
,PARTITION ledger_2020_cf VALUES LESS THAN (2021,0)
--
,PARTITION ledger_2021_bf VALUES LESS THAN (2021,1)
,PARTITION ledger_2021_01 VALUES LESS THAN (2021,2)
…
,PARTITION ledger_2021_12 VALUES LESS THAN (2021,13)
,PARTITION ledger_2021_cf VALUES LESS THAN (2022,0)
)
ENABLE ROW MOVEMENT
NOPARALLEL NOLOGGING
/
@treeselectors
@popledgerREM treeselectors.sql
CREATE TABLE PSTREESELECT05
(SELECTOR_NUM INTEGER NOT NULL,
TREE_NODE_NUM INTEGER NOT NULL,
RANGE_FROM_05 VARCHAR2(05) NOT NULL,
RANGE_TO_05 VARCHAR2(05) NOT NULL)
PARTITION BY RANGE (SELECTOR_NUM) INTERVAL (1)
(PARTITION pstreeselector VALUES LESS THAN (2))
NOPARALLEL NOLOGGING;
CREATE UNIQUE INDEX PS_PSTREESELECT05 ON PSTREESELECT05 (SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_05);
CREATE TABLE PSTREESELECT10
(SELECTOR_NUM INTEGER NOT NULL,
TREE_NODE_NUM INTEGER NOT NULL,
RANGE_FROM_10 VARCHAR2(10) NOT NULL,
RANGE_TO_10 VARCHAR2(10) NOT NULL)
PARTITION BY RANGE (SELECTOR_NUM) INTERVAL (1)
(PARTITION pstreeselector VALUES LESS THAN (2))
NOPARALLEL NOLOGGING;
CREATE UNIQUE INDEX PS_PSTREESELECT10 ON PSTREESELECT10 (SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_10);
exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT05','GRANULARITY','ALL');
exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT10','GRANULARITY','ALL');
exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT05','METHOD_OPT'-
,'FOR ALL COLUMNS SIZE 1, FOR COLUMNS SELECTOR_NUM, (SELECTOR_NUM, TREE_NODE_NUM) SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT10','METHOD_OPT'-
,'FOR ALL COLUMNS SIZE 1, FOR COLUMNS SELECTOR_NUM, (SELECTOR_NUM, TREE_NODE_NUM) SIZE 254');- actuals data from fiscal year 2018 to period 6 of 2020
- budget data from fiscal year 2018 to 2021 that is 10% of the size of the actuals data.
The tree selector tables will be populated with data corresponding to the ledger data:
Per complete fiscal year, there are 1,000,000 actuals rows and 100,000 budget rows
There are about 77K rows per accounting period with just 1000 rows in periods 998 (adjustments), 999 (carry forward)
I will create two MVs each containing data for a single fiscal year; one for 2019 and one for 2020
I will only range partition the MV on accounting period. We don't need to partition it on FISCAL_YEAR since it only contains a single year.
The materialized views are populated on creation, but I will explicitly collect statistics on them.
Although I can do a full refresh of the MV, I cannot do a PCT refresh.
I can use EXPLAIN_MVIEW to check the status of the MV
EXPLAIN_MVIEW reports that general query rewrite is available but PCT and PCT query rewrite are not. Per the manual, Oracle simply cannot do a PCT refresh if the table has multi-column partitioning.
At the moment, the materialized views are up to date.
And I get MV rewrite because the MV is up to date. Note that Oracle only probed partitions 2 to 7, so it correctly pruned partitions.
Now I will add more random data for the financial year 2020, accounting period 7. So there have been changes to just one partition.
As soon as I have committed the insert, both the MVs need to be refreshed, even though none of the data queried by MV_LEDGER_2019 was changed.
USER_MVIEW_DETAIL_RELATIONS reports PCT not applicable. No individual partitions are listed as stale.
I no longer get Query Rewrite for either fiscal year.
- the business unit tree will have both business units,
- the account tree will have 25% of the 999 accounts,
- the chartfield tree will have 10% of the 999 chartfields.
REM popledger.sql
set autotrace off echo on pages 99 lines 200 trimspool on
truncate table ps_ledger;
exec dbms_stats.set_table_prefs('SCOTT','PS_LEDGER','METHOD_OPT'-
,'FOR ALL COLUMNS SIZE 1, FOR COLUMNS FISCAL_YEAR, ACCOUNTING_PERIOD, LEDGER, BUSINESS_UNIT SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','PS_LEDGER','GRANULARITY','ALL');
ALTER TABLE PS_LEDGER PARALLEL 8 NOLOGGING;
CREATE /*UNIQUE*/ INDEX ps_ledger ON ps_ledger
(business_unit, ledger, account, deptid
,product, fund_code, class_fld, affiliate
,chartfield2, project_id, book_code, gl_adjust_type
,currency_cd, statistics_code, fiscal_year, accounting_period
) COMPRESS 2 PARALLEL
/
INSERT /*+APPEND PARALLEL ENABLE_PARALLEL_DML NO_GATHER_OPTIMIZER_STATISTICS*//*IGNORE_ROW_ON_DUPKEY_INDEX(PS_LEDGER)*/
INTO ps_ledger
with n as (
SELECT rownum n from dual connect by level <= 1e2
), fy as (
SELECT 2017+rownum fiscal_year FROM dual CONNECT BY level <= 4
), ap as (
SELECT FLOOR(dbms_random.value(0,13)) accounting_period FROM dual connect by level <= 998
UNION ALL SELECT 998 FROM DUAL CONNECT BY LEVEL <= 1
UNION ALL SELECT 999 FROM DUAL CONNECT BY LEVEL <= 1
), l as (
SELECT 'ACTUALS' ledger FROM DUAL CONNECT BY LEVEL <= 10
UNION ALL SELECT 'BUDGET' FROM DUAL
)
select 'BU'||LTRIM(TO_CHAR(CASE WHEN dbms_random.value <= .9 THEN 1 ELSE 2 END,'000')) business_unit
, l.ledger
, 'ACC'||LTRIM(TO_CHAR(999*SQRT(dbms_random.value),'000')) account
, 'ALTACCT'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) altacct
, 'DEPT'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) deptid
, 'OPUNIT'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) operating_unit
, 'P'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) product
, 'FUND'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) fund_code
, 'CLAS'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) class_fld
, 'PROD'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) program_code
, ' ' budget_ref
, 'AF'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) affiliate
, 'AFI'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) affiliate_intra1
, 'AFI'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) affiliate_intra2
, 'CF'||LTRIM(TO_CHAR( 999*SQRT(dbms_random.value),'000')) chartfield1
, 'CF'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) chartfield2
, 'CF'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) chartfield3
, 'PRJ'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) project_id
, 'BK'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) book_code
, 'GL'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) gl_adjust_type
, 'GBP' currency_cd
, ' ' statistics_code
, fy.fiscal_year
, ap.accounting_period
, dbms_random.value(0,1e6) posted_total_amt
, 0 posted_base_amt
, 0 posted_tran_amt
, 'GBP' base_currency
, SYSDATE dttm_stamp_sec
, 0 process_instance
FROM fy,ap, l, n
WHERE l.ledger = 'BUDGET' or (fy.fiscal_year < 2020 or (fy.fiscal_year = 2020 AND ap.accounting_period <= 6))
/
commit;
exec dbms_stats.gather_table_stats('SCOTT','PS_LEDGER');
CREATE INDEX psxledger ON ps_ledger
(ledger, fiscal_year, accounting_period, business_unit, account, chartfield1
) LOCAL COMPRESS 4 PARALLEL
/
CREATE INDEX psyledger ON ps_ledger
(ledger, fiscal_year, business_unit, account, chartfield1, accounting_period
) LOCAL COMPRESS 3 PARALLEL
/
ALTER INDEX ps_ledger NOPARALLEL;
ALTER INDEX psxledger NOPARALLEL;
ALTER INDEX psyledger NOPARALLEL;
TRUNCATE TABLE PSTREESELECT05;
TRUNCATE TABLE PSTREESELECT10;
INSERT INTO PSTREESELECT05
WITH x as (SELECT DISTINCT business_unit FROM ps_ledger)
, y as (SELECT 30982, FLOOR(DBMS_RANDOM.value(1,1e10)) tree_node_num, business_unit FROM x)
select y.*, business_unit FROM y
/
INSERT INTO PSTREESELECT10
WITH x as (SELECT DISTINCT account FROM ps_ledger)
, y as (SELECT 30984, FLOOR(DBMS_RANDOM.value(1,1e10)) tree_node_num, account FROM x)
select y.*, account FROM y
where mod(tree_node_num,100)<25
/
INSERT INTO PSTREESELECT10
WITH x as (SELECT DISTINCT chartfield1 FROM ps_ledger)
, y as (SELECT 30985, FLOOR(DBMS_RANDOM.value(1,1e10)) tree_node_num, chartfield1 FROM x)
select y.*, chartfield1 FROM y
where mod(tree_node_num,100)<10
/LEDGER FISCAL_YEAR COUNT(*) MAX(ACCOUNTING_PERIOD)
---------- ----------- ---------- ----------------------
ACTUALS 2018 1000000 999
2019 1000000 999
2020 538408 6
BUDGET 2018 100000 999
2019 100000 999
2020 100000 999
2021 100000 999
********** ----------
sum 2938408LEDGER FISCAL_YEAR ACCOUNTING_PERIOD COUNT(*)
---------- ----------- ----------------- ----------
…
ACTUALS 2019 0 76841
1 76410
2 76867
3 77088
4 77740
5 77010
6 76650
7 76553
8 76923
9 76586
10 76276
11 76943
12 76113
998 1000
999 1000
********** *********** ----------
sum 1000000
ACTUALS 2020 0 77308
1 76696
2 76944
3 77227
4 76944
5 76524
6 76765
********** *********** ----------
sum 538408
…CREATE MATERIALIZED VIEW mv_ledger_2019
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1)
,PARTITION ap_01 VALUES LESS THAN (2)
,PARTITION ap_02 VALUES LESS THAN (3)
,PARTITION ap_03 VALUES LESS THAN (4)
,PARTITION ap_04 VALUES LESS THAN (5)
,PARTITION ap_05 VALUES LESS THAN (6)
,PARTITION ap_06 VALUES LESS THAN (7)
,PARTITION ap_07 VALUES LESS THAN (8)
,PARTITION ap_08 VALUES LESS THAN (9)
,PARTITION ap_09 VALUES LESS THAN (10)
,PARTITION ap_10 VALUES LESS THAN (11)
,PARTITION ap_11 VALUES LESS THAN (12)
,PARTITION ap_12 VALUES LESS THAN (13)
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2019
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1)
,PARTITION ap_01 VALUES LESS THAN (2)
,PARTITION ap_02 VALUES LESS THAN (3)
,PARTITION ap_03 VALUES LESS THAN (4)
,PARTITION ap_04 VALUES LESS THAN (5)
,PARTITION ap_05 VALUES LESS THAN (6)
,PARTITION ap_06 VALUES LESS THAN (7)
,PARTITION ap_07 VALUES LESS THAN (8)
,PARTITION ap_08 VALUES LESS THAN (9)
,PARTITION ap_09 VALUES LESS THAN (10)
,PARTITION ap_10 VALUES LESS THAN (11)
,PARTITION ap_11 VALUES LESS THAN (12)
,PARTITION ap_12 VALUES LESS THAN (13)
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2020
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop
@@mvpop
@@mvsql
@@pop2020m7
@@mvsql
@@mvtrc
@@mvvol
@@mvsql
@@mvcapSELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
ALTER MATERIALIZED VIEW mv_ledger_2019 NOPARALLEL;
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2019','METHOD_OPT',-
'FOR ALL COLUMNS SIZE 1, FOR COLUMNS FISCAL_YEAR, ACCOUNTING_PERIOD, BUSINESS_UNIT SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2019','GRANULARITY','ALL');
ALTER MATERIALIZED VIEW mv_ledger_2020 NOPARALLEL;
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2020','METHOD_OPT',-
'FOR ALL COLUMNS SIZE 1, FOR COLUMNS FISCAL_YEAR, ACCOUNTING_PERIOD, BUSINESS_UNIT SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2020','GRANULARITY','ALL');
exec dbms_stats.gather_table_stats('SCOTT','MV_LEDGER_2019');
exec dbms_stats.gather_table_stats('SCOTT','MV_LEDGER_2020');BEGIN dbms_mview.refresh(list=>'MV_LEDGER_2020',method=>'P',atomic_refresh=>FALSE); END;
*
ERROR at line 1:
ORA-12047: PCT FAST REFRESH cannot be used for materialized view "SCOTT"."MV_LEDGER_2020"
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1REM mvcap.sql
create table MV_CAPABILITIES_TABLE
(
statement_id varchar(30) ,
mvowner varchar(30) ,
mvname varchar(30) ,
capability_name varchar(30) ,
possible character(1) ,
related_text varchar(2000) ,
related_num number ,
msgno integer ,
msgtxt varchar(2000) ,
seq number
) ;
truncate table MV_CAPABILITIES_TABLE;
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SCOTT.MV_LEDGER_2019');
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SCOTT.MV_LEDGER_2020');
break on mvname skip 1
column rel_text format a20
column msgtxt format a60
SELECT mvname, capability_name, possible, SUBSTR(related_text,1,20) AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
WHERE mvname like 'MV_LEDGER_20%'
ORDER BY mvname, seq;CAPABILITY_NAME P REL_TEXT MSGTXT
------------------------------ - -------------------- ------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N PS_LEDGER PCT not supported with multi-column partition key
REFRESH_FAST_AFTER_INSERT N SCOTT.PS_LEDGER the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N POSTED_TOTAL_AMT SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE N PS_LEDGER PCT not supported with multi-column partition keySELECT L.TREE_NODE_NUM,L2.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A
, PSTREESELECT05 L1
, PSTREESELECT10 L
, PSTREESELECT10 L2
WHERE A.LEDGER='ACTUALS'
AND A.FISCAL_YEAR=2020
AND (A.ACCOUNTING_PERIOD BETWEEN 1 AND 6)
AND L1.SELECTOR_NUM=30982 AND A.BUSINESS_UNIT=L1.RANGE_FROM_05
AND L.SELECTOR_NUM=30985 AND A.CHARTFIELD1=L.RANGE_FROM_10
AND L2.SELECTOR_NUM=30984 AND A.ACCOUNT=L2.RANGE_FROM_10
AND A.CURRENCY_CD='GBP'
GROUP BY L.TREE_NODE_NUM,L2.TREE_NODE_NUM
/Plan hash value: 3290858815
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5573 | 239K| 276 (3)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 5573 | 239K| 276 (3)| 00:00:01 | | |
|* 2 | HASH JOIN | | 5573 | 239K| 275 (3)| 00:00:01 | | |
| 3 | JOIN FILTER CREATE | :BF0000 | 2 | 22 | 1 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 5 | VIEW | VW_GBC_17 | 5573 | 179K| 274 (3)| 00:00:01 | | |
| 6 | HASH GROUP BY | | 5573 | 364K| 274 (3)| 00:00:01 | | |
| 7 | JOIN FILTER USE | :BF0000 | 5573 | 364K| 273 (2)| 00:00:01 | | |
|* 8 | HASH JOIN | | 5573 | 364K| 273 (2)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 239 | 4541 | 2 (0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 23295 | 1091K| 270 (2)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 77 | 1386 | 2 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE ITERATOR | | 301K| 8827K| 267 (2)| 00:00:01 | 2 | 7 |
|* 13 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020 | 301K| 8827K| 267 (2)| 00:00:01 | 2 | 7 |
--------------------------------------------------------------------------------------------------------------------------
REM pop2020m7.sql
insert into ps_ledger
with n as (
SELECT rownum n from dual connect by level <= 1e6/13
)
select 'BU'||LTRIM(TO_CHAR(CASE WHEN dbms_random.value <= .9 THEN 1 ELSE 2 END,'000')) business_unit
, 'ACTUALS' ledger
, 'ACC'||LTRIM(TO_CHAR(999*SQRT(dbms_random.value),'000')) account
, 'ALTACCT'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) altacct
, 'DEPT'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) deptid
, 'OPUNIT'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) operating_unit
, 'P'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) product
, 'FUND'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) fund_code
, 'CLAS'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) class_fld
, 'PROD'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) program_code
, ' ' budget_ref
, 'AF'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) affiliate
, 'AFI'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) affiliate_intra1
, 'AFI'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) affiliate_intra2
, 'CF'||LTRIM(TO_CHAR( 999*SQRT(dbms_random.value),'000')) chartfield1
, 'CF'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) chartfield2
, 'CF'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) chartfield3
, 'PRJ'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) project_id
, 'BK'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) book_code
, 'GL'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) gl_adjust_type
, 'GBP' currency_cd
, ' ' statistics_code
, 2020 fiscal_year
, 7 accounting_period
, dbms_random.value(0,1e6) posted_total_amt
, 0 posted_base_amt
, 0 posted_tran_amt
, 'GBP' base_currency
, SYSDATE dttm_stamp_sec
, 0 process_instance
FROM n
/
set lines 200 pages 999 autotrace off
commit;
column owner format a10
column table_name format a15
column mview_name format a15
column detailobj_owner format a10 heading 'Detailobj|Owner'
column detailobj_name format a15
column detailobj_alias format a20
column detail_partition_name format a20
column detail_subpartition_name format a20
column parent_table_partition format a20
select * from user_mview_detail_relations;
select * from user_mview_detail_partition;
select * from user_mview_detail_subpartition where freshness != 'FRESH';
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
/MVIEW_NAME STALENESS LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2019 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
MV_LEDGER_2020 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAILOBJ DETAILOBJ_ALIAS D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT MV_LEDGER_2019 SCOTT PS_LEDGER TABLE PS_LEDGER N 86 0
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER TABLE PS_LEDGER N 86 0SELECT L.TREE_NODE_NUM,L2.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A
, PSTREESELECT05 L1
, PSTREESELECT10 L
, PSTREESELECT10 L2
WHERE A.LEDGER='ACTUALS'
AND A.FISCAL_YEAR=2019
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 6
AND L1.SELECTOR_NUM=30982 AND A.BUSINESS_UNIT=L1.RANGE_FROM_05
AND L.SELECTOR_NUM=30985 AND A.CHARTFIELD1=L.RANGE_FROM_10
AND L2.SELECTOR_NUM=30984 AND A.ACCOUNT=L2.RANGE_FROM_10
AND A.CURRENCY_CD='GBP'
GROUP BY L.TREE_NODE_NUM,L2.TREE_NODE_NUM
/
Plan hash value: 346876754
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 492 | 45756 | 2036 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 492 | 45756 | 2036 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 492 | 45756 | 2035 (1)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 239 | 4541 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 2055 | 148K| 2033 (1)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 154 | 4466 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 77 | 1386 | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 77 | 1386 | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE ITERATOR| | 26686 | 1172K| 2030 (1)| 00:00:01 | 3 | 8 |
| 10 | PARTITION LIST SINGLE | | 26686 | 1172K| 2030 (1)| 00:00:01 | 1 | 1 |
|* 11 | TABLE ACCESS FULL | PS_LEDGER | 26686 | 1172K| 2030 (1)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------Without PCT, I cannot do a partial refresh of a partitioned materialized view, and will not get query rewrite if just a single partition in the underlying table has changed, whether I need it for this query or not.
So is there a different partitioning strategy that will permit PCT to work effectively?
Demonstration 2: Simple 1-Dimensional Range Partitioning
Let's start with a simple range partitioned example; one partition per fiscal year.
Now I am going to build a materialized view to summarise the ledger data by BUSINESS_UNIT, ACCOUNT and CHARTFIELD1, and of course by FISCAL_YEAR and ACCOUNTING_PERIOD.
I can see the MV has partitions for 2019 and 2020 populated, and they contain fewer rows than the original.
When I query 2018 ledger data, for which there is no materialized view, the execution plan shows that Oracle full scanned only the first partition of the PS_LEDGER table that contains the 2018 data. It eliminated the other partitions.
When I query the 2020 data, Oracle has rewritten the query to use the second partition of the materialised view. Again it only queried a single partition.
Now I am going to simulate running financial processing for period 7 in fiscal year 2020, by inserting data into PS_LEDGER for that period.
The materialised view status and staleness on USER_MVIEWS changes to NEEDS_COMPILE when the insert into PS_LEDGER is committed.
@pop2020m7.sql- USER_MVIEW_DETAIL_RELATIONS shows that 1 tracked partition is stale but three are still fresh.
- USER_MVIEW_DETAIL_PARTITION shows the tracking status of each source partition. We can see that the LEDGER_2020 partition on PS_LEDGER is stale but the others are still fresh.
22:00:01 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
MVIEW_NAME STALENESS LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
22:00:01 SQL> select * from user_mview_detail_relations;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAILOBJ DETAILOBJ_ALIAS D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER TABLE PS_LEDGER Y 3 1
22:00:01 SQL> select * from user_mview_detail_partition;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAIL_PARTITION_NAM DETAIL_PARTITION_POSITION FRESHNE LAST_REFRESH_TIME
---------- --------------- ---------- --------------- -------------------- ------------------------- ------- -------------------
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2018 1 FRESH 21:59:41 15/11/2020
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2019 2 FRESH 21:59:41 15/11/2020
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2020 3 STALE 21:59:41 15/11/2020
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2021 4 FRESH 21:59:41 15/11/2020Plan hash value: 4006930814
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1088 | 88128 | 674 (2)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1088 | 88128 | 674 (2)| 00:00:01 | | |
|* 2 | HASH JOIN | | 1088 | 88128 | 673 (2)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 228 | 4332 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 4767 | 288K| 671 (2)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 180 | 5220 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 90 | 1620 | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 90 | 1620 | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SINGLE | | 52909 | 1705K| 668 (2)| 00:00:01 | 1 | 1 |
|* 10 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020 | 52909 | 1705K| 668 (2)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
"MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
10 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6 AND "MV_LEDGER_2020"."FISCAL_YEAR"=2019 AND
"MV_LEDGER_2020"."ACCOUNTING_PERIOD">=1)Plan hash value: 1780139226
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 477 | 44361 | 4483 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 477 | 44361 | 4483 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 477 | 44361 | 4482 (1)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 228 | 4332 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 2090 | 151K| 4479 (1)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 180 | 5220 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 90 | 1620 | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 90 | 1620 | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SINGLE| | 23179 | 1018K| 4476 (1)| 00:00:01 | 3 | 3 |
|* 10 | TABLE ACCESS FULL | PS_LEDGER | 23179 | 1018K| 4476 (1)| 00:00:01 | 3 | 3 |
---------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
10 - filter("A"."ACCOUNTING_PERIOD"<=6 AND "A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND
"A"."ACCOUNTING_PERIOD">=1 AND "A"."CURRENCY_CD"='GBP')- method P to indicate that it should use PCT,
- atomic refresh is set to false because I want Oracle to truncate the partition and repopulate it in direct path mode so that the data is compressed (because I am not licenced for advanced compression).
I need to collect statistics myself, or they won't be updated.
REM mvtrc.sql
disconnect
connect scott/tiger@oracle_pdb
column name format a20
column value format a70
alter session set tracefile_identifier=PCT;
select * from v$diag_info where name like '%Trace%';
alter session set sql_trace = true;
exec dbms_mview.refresh(list=>'MV_LEDGER_2019',method=>'P',atomic_refresh=>FALSE);
exec dbms_mview.refresh(list=>'MV_LEDGER_2020',method=>'P',atomic_refresh=>FALSE);
alter session set sql_trace = false;
exec dbms_stats.gather_Table_stats(user,'MV_LEDGER_2019');
exec dbms_stats.gather_Table_stats(user,'MV_LEDGER_2020');
v$diag_info indicates the trace file
INST_ID NAME VALUE CON_ID
---------- -------------------- ---------------------------------------------------------------------- ----------
1 Diag Trace /u01/app/oracle/diag/rdbms/oracle/oracle/trace 0
1 Default Trace File /u01/app/oracle/diag/rdbms/oracle/oracle/trace/oracle_ora_7802_PCT.trc 0 Sub- Rows
Part Part per
TABLE_NAME Pos PARTITION_NAME Pos SUBPARTITION_NAME NUM_ROWS BLOCKS Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ------------------------------
MV_LEDGER_2020 1 LEDGER_2019 946825 3173 298.4 ENABLED BASIC
2 LEDGER_2020 582155 1926 302.3 ENABLED BASIC
1528980 5099 299.9
PS_LEDGER 1 LEDGER_2018 1100000 17893 61.5 ENABLED BASIC
2 LEDGER_2019 1100000 17892 61.5 ENABLED BASIC
3 LEDGER_2020 637915 16456 38.8 DISABLED
4 LEDGER_2021 100000 2559 39.1 DISABLED
2937915 54800 53.6…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE PARTITION LEDGER_2020
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( LEDGER_2020 ) ("BUSINESS_UNIT"
,"LEDGER", "ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT /*+ X_DYN_PRUNE */
"PS_LEDGER"."BUSINESS_UNIT", "PS_LEDGER"."LEDGER" , "PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" P0,
"PS_LEDGER"."ACCOUNTING_PERIOD" ,SUM("PS_LEDGER"."POSTED_TOTAL_AMT") FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR">=2019
AND "PS_LEDGER"."LEDGER"='ACTUALS' AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND ( ( ( ( ( ( "PS_LEDGER"."FISCAL_YEAR" >= 2020 ) ) )
AND ( ( ( "PS_LEDGER"."FISCAL_YEAR" < 2021 ) ) ) ) ) ) GROUP BY "PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."LEDGER"
,"PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…CAPABILITY_NAME P REL_TEXT MSGTXT
------------------------------ - -------------------- ------------------------------------------------------------
PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE Y PS_LEDGER
REFRESH_FAST_AFTER_INSERT N SCOTT.PS_LEDGER the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N POSTED_TOTAL_AMT SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT Y
PCT_TABLE_REWRITE Y PS_LEDGERI can see PCT has worked.
- I still get query rewrite for the partitions that are still fresh rather than stale.
- The refresh process refreshes only the stale partitions.
Demonstration 3: Interval Partitioning
This time I am going to use interval partitioning. I have explicitly specified the partitions for previous years because I don't want to allow any free space in the blocks, but the current and future partitions will be created automatically.
I will similarly create a single materialized view with interval partitioning per fiscal year and populate it for 2019 onwards.
I get exactly the same behaviour as the previous demonstration. The only difference is that the new partitions have system generated names, but as before just one of them is identified as stale.
However, when I look in the trace of the refresh, I see that it has truncated and repopulated the partitions for both 2020 and 2021 even though I didn't change any of the data in the 2021 partition, and it is listed as fresh.
In practice, in this particular case, it won't make a huge difference because there is no actuals data in 2021. The partition for 2021 has been created in the data dictionary, but due to deferred segment creation, it has not been physically created because there is no data in it. However, if I had updated data in 2019, then it would have truncated and repopulated two partitions (2019 and 2020).
CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) INTERVAL (1)
(PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS)
ENABLE ROW MOVEMENT NOLOGGING
/
@treeselectors
@popledgerCREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR) INTERVAL (1)
(PARTITION ledger_2019 VALUES LESS THAN (2020)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@@mvpop
@@mvvol
@@mvsql@pop2020m7.sql
23:25:42 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
MVIEW_NAME STALENESS LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
23:25:42 SQL> select * from user_mview_detail_relations;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAILOBJ DETAILOBJ_ALIAS D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER TABLE PS_LEDGER Y 3 1
23:25:42 SQL> select * from user_mview_detail_partition;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAIL_PARTITION_NAM DETAIL_PARTITION_POSITION FRESHNE LAST_REFRESH_TIME
---------- --------------- ---------- --------------- -------------------- ------------------------- ------- -------------------
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2018 1 FRESH 23:25:21 15/11/2020
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2019 2 FRESH 23:25:21 15/11/2020
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER SYS_P981 3 STALE 23:25:21 15/11/2020
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER SYS_P982 4 FRESH 23:25:21 15/11/2020Interval partitioning is a form of range partitioning, so it is expected that PCT still works. However, I have no explanation as to why the partition following the stale partition was also refreshed. This might be a bug.
Demonstration 4: Composite (Range-List) Partitioning
This time I am going to create a composite partitioned table. It will have the same range partitioning on FISCAL_YEAR, but then I will list subpartition it by ACCOUTING_PERIOD with 14 periods per fiscal year. I will use a template so that each partition will have the same subpartitions.
I will similarly partition the materialized view
PCT does work properly. USER_MVIEW_DETAIL_PARTITION reports that one partition is stale
USER_MVIEW_DETAIL_SUBPARTITION correctly identified that it is a stale sub-partition, but as expected, the materialized view refresh truncates the partition not the sub-partition and repopulates it. So we are still processing a whole fiscal year.
If I query periods 1 to 6 in 2020 using a BETWEEN, this is then expanded to two inequalities that I can see in the predicate section. These subpartitions are up to date, and Oracle performs query rewrite.
But if I create period 7 in fiscal year 2020, then that subpartition is stale and Oracle leaves the query against that period as submitted to run against PS_LEDGER.
So PCT also controls query rewrite correctly on list partitioning.
Again, when I look at the trace of the stale partition refresh, the entire 2020 partition was truncated and refreshed in direct-path mode. There is no accounting period criterion on the insert statement.
CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY LIST (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES (0)
,SUBPARTITION ap_01 VALUES (1)
,SUBPARTITION ap_02 VALUES (2)
,SUBPARTITION ap_03 VALUES (3)
,SUBPARTITION ap_04 VALUES (4)
,SUBPARTITION ap_05 VALUES (5)
,SUBPARTITION ap_06 VALUES (6)
,SUBPARTITION ap_07 VALUES (7)
,SUBPARTITION ap_08 VALUES (8)
,SUBPARTITION ap_09 VALUES (9)
,SUBPARTITION ap_10 VALUES (10)
,SUBPARTITION ap_11 VALUES (11)
,SUBPARTITION ap_12 VALUES (12)
,SUBPARTITION ap_cf VALUES (DEFAULT))
(PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021)
,PARTITION ledger_2021 VALUES LESS THAN (2022)
) ENABLE ROW MOVEMENT NOPARALLEL NOLOGGING
/
@treeselectors
@popledgerCREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY LIST (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES (0)
,SUBPARTITION ap_01 VALUES (1)
,SUBPARTITION ap_02 VALUES (2)
,SUBPARTITION ap_03 VALUES (3)
,SUBPARTITION ap_04 VALUES (4)
,SUBPARTITION ap_05 VALUES (5)
,SUBPARTITION ap_06 VALUES (6)
,SUBPARTITION ap_07 VALUES (7)
,SUBPARTITION ap_08 VALUES (8)
,SUBPARTITION ap_09 VALUES (9)
,SUBPARTITION ap_10 VALUES (10)
,SUBPARTITION ap_11 VALUES (11)
,SUBPARTITION ap_12 VALUES (12)
,SUBPARTITION ap_cf VALUES (DEFAULT))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop@pop2020m7.sql
17:40:03 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
MVIEW_NAME STALENESS LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
17:40:03 SQL> select * from user_mview_detail_relations;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAILOBJ DETAILOBJ_ALIAS D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER TABLE PS_LEDGER Y 55 1
17:40:10 SQL> select * from user_mview_detail_partition;
no rows selected
17:40:10 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2020 LEDGER_2020_AP_07 8 STALEPlan hash value: 1400212726
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12260 | 969K| | 664 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 12260 | 969K| 1128K| 664 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 12260 | 969K| | 428 (2)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 270 | 5130 | | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 45363 | 2746K| | 425 (1)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 182 | 5278 | | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 91 | 1638 | | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 91 | 1638 | | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SINGLE | | 497K| 15M| | 421 (1)| 00:00:01 | 2 | 2 |
| 10 | PARTITION LIST ITERATOR | | 497K| 15M| | 421 (1)| 00:00:01 | KEY | KEY |
|* 11 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020 | 497K| 15M| | 421 (1)| 00:00:01 | 15 | 28 |
-------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
"MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
11 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD">=1 AND "MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6 AND
"MV_LEDGER_2020"."FISCAL_YEAR"=2020)Plan hash value: 3964652976
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 7 (15)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 92 | 7 (15)| 00:00:01 | | |
|- * 2 | HASH JOIN | | 1 | 92 | 6 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 92 | 6 (0)| 00:00:01 | | |
|- 4 | STATISTICS COLLECTOR | | | | | | | |
|- * 5 | HASH JOIN | | 1 | 73 | 5 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 73 | 5 (0)| 00:00:01 | | |
|- 7 | STATISTICS COLLECTOR | | | | | | | |
|- * 8 | HASH JOIN | | 1 | 55 | 4 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 | | |
|- 10 | STATISTICS COLLECTOR | | | | | | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | 3 | 3 |
| 12 | PARTITION LIST SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | KEY | KEY |
| * 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER | 1 | 44 | 3 (0)| 00:00:01 | 36 | 36 |
| * 14 | INDEX RANGE SCAN | PSXLEDGER | 1 | | 2 (0)| 00:00:01 | 36 | 36 |
| * 15 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
|- * 16 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
| * 17 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
|- * 18 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
| * 19 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
|- * 20 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
13 - filter("A"."CURRENCY_CD"='GBP')
14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
16 - access("L1"."SELECTOR_NUM"=30982)
17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
18 - access("L"."SELECTOR_NUM"=30985)
19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
20 - access("L2"."SELECTOR_NUM"=30984)…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE PARTITION LEDGER_2020
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( LEDGER_2020 ) ("BUSINESS_UNIT",
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" ,
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" P0, "PS_LEDGER"."ACCOUNTING_PERIOD" ,
SUM("PS_LEDGER"."POSTED_TOTAL_AMT") FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2020 AND "PS_LEDGER"."LEDGER"='ACTUALS'
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND ( ( ( ( ( ( "PS_LEDGER"."FISCAL_YEAR" < 2021 ) ) ) ) ) )GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…Demonstration 5: Composite (Range-Range) Partitioning
I am still composite partitioning the ledger table and materialized view in this test. It will have the same range partitioning on FISCAL_YEAR, but this time I will range subpartition it by ACCOUTING_PERIOD with 14 periods per fiscal year. I will use a template so that each partition will have the same subpartitions.CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1)
,SUBPARTITION ap_01 VALUES LESS THAN (2)
,SUBPARTITION ap_02 VALUES LESS THAN (3)
,SUBPARTITION ap_03 VALUES LESS THAN (4)
,SUBPARTITION ap_04 VALUES LESS THAN (5)
,SUBPARTITION ap_05 VALUES LESS THAN (6)
,SUBPARTITION ap_06 VALUES LESS THAN (7)
,SUBPARTITION ap_07 VALUES LESS THAN (8)
,SUBPARTITION ap_08 VALUES LESS THAN (9)
,SUBPARTITION ap_09 VALUES LESS THAN (10)
,SUBPARTITION ap_10 VALUES LESS THAN (11)
,SUBPARTITION ap_11 VALUES LESS THAN (12)
,SUBPARTITION ap_12 VALUES LESS THAN (13)
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021)
,PARTITION ledger_2021 VALUES LESS THAN (2022)
)
ENABLE ROW MOVEMENT NOLOGGING
/
@treeselectors
@popledgerCREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1)
,SUBPARTITION ap_01 VALUES LESS THAN (2)
,SUBPARTITION ap_02 VALUES LESS THAN (3)
,SUBPARTITION ap_03 VALUES LESS THAN (4)
,SUBPARTITION ap_04 VALUES LESS THAN (5)
,SUBPARTITION ap_05 VALUES LESS THAN (6)
,SUBPARTITION ap_06 VALUES LESS THAN (7)
,SUBPARTITION ap_07 VALUES LESS THAN (8)
,SUBPARTITION ap_08 VALUES LESS THAN (9)
,SUBPARTITION ap_09 VALUES LESS THAN (10)
,SUBPARTITION ap_10 VALUES LESS THAN (11)
,SUBPARTITION ap_11 VALUES LESS THAN (12)
,SUBPARTITION ap_12 VALUES LESS THAN (13)
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop@pop2020m7.sql
19:09:50 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
MVIEW_NAME STALENESS LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
19:09:50 SQL> select * from user_mview_detail_relations;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAILOBJ DETAILOBJ_ALIAS D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER TABLE PS_LEDGER Y 55 1
19:09:56 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2020 LEDGER_2020_AP_07 8 STALEPlan hash value: 589110139
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13427 | 1062K| | 683 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 13427 | 1062K| 1232K| 683 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 13427 | 1062K| | 427 (2)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 257 | 4883 | | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 52141 | 3156K| | 424 (1)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 210 | 6090 | | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 105 | 1890 | | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 105 | 1890 | | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SINGLE | | 496K| 15M| | 420 (1)| 00:00:01 | 2 | 2 |
| 10 | PARTITION RANGE ITERATOR | | 496K| 15M| | 420 (1)| 00:00:01 | 2 | 7 |
|* 11 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020 | 496K| 15M| | 420 (1)| 00:00:01 | 15 | 28 |
-------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
"MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
11 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6 AND "MV_LEDGER_2020"."FISCAL_YEAR"=2020)Plan hash value: 1321682226
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 7 (15)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 92 | 7 (15)| 00:00:01 | | |
|- * 2 | HASH JOIN | | 1 | 92 | 6 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 92 | 6 (0)| 00:00:01 | | |
|- 4 | STATISTICS COLLECTOR | | | | | | | |
|- * 5 | HASH JOIN | | 1 | 73 | 5 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 73 | 5 (0)| 00:00:01 | | |
|- 7 | STATISTICS COLLECTOR | | | | | | | |
|- * 8 | HASH JOIN | | 1 | 55 | 4 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 | | |
|- 10 | STATISTICS COLLECTOR | | | | | | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | 3 | 3 |
| 12 | PARTITION RANGE SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | 8 | 8 |
| * 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER | 1 | 44 | 3 (0)| 00:00:01 | 36 | 36 |
| * 14 | INDEX RANGE SCAN | PSXLEDGER | 1 | | 2 (0)| 00:00:01 | 36 | 36 |
| * 15 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
|- * 16 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
| * 17 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
|- * 18 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
| * 19 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
|- * 20 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
13 - filter("A"."CURRENCY_CD"='GBP')
14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
16 - access("L1"."SELECTOR_NUM"=30982)
17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
18 - access("L"."SELECTOR_NUM"=30985)
19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
20 - access("L2"."SELECTOR_NUM"=30984)Plan hash value: 3827045647
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 561 | 52173 | 3670 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 561 | 52173 | 3670 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 561 | 52173 | 3669 (1)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 227 | 4313 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 2468 | 178K| 3667 (1)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 210 | 6090 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 105 | 1890 | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 105 | 1890 | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SINGLE | | 23486 | 1032K| 3664 (1)| 00:00:01 | 3 | 3 |
| 10 | PARTITION RANGE ITERATOR| | 23486 | 1032K| 3664 (1)| 00:00:01 | 2 | 8 |
|* 11 | TABLE ACCESS FULL | PS_LEDGER | 23486 | 1032K| 3664 (1)| 00:00:01 | 29 | 42 |
------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
11 - filter("A"."ACCOUNTING_PERIOD"<=7 AND "A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND
"A"."CURRENCY_CD"='GBP')…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE PARTITION LEDGER_2020
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( LEDGER_2020 ) ("BUSINESS_UNIT",
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" ,
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" P0, "PS_LEDGER"."ACCOUNTING_PERIOD" ,
SUM("PS_LEDGER"."POSTED_TOTAL_AMT") FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2020 AND "PS_LEDGER"."LEDGER"='ACTUALS'
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND ( ( ( ( ( ( "PS_LEDGER"."FISCAL_YEAR" < 2021 ) ) ) ) ) )GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…Demonstration 6: Mismatching Partitioning
In this example, I am still composite partitioning the ledger table and materialized view. It will have the same range partitioning on FISCAL_YEAR, I still will range subpartition it by ACCOUTING_PERIOD with 14 periods per fiscal year. I will use a template so that each partition will have the same subpartitions.CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1)
,SUBPARTITION ap_01 VALUES LESS THAN (2)
,SUBPARTITION ap_02 VALUES LESS THAN (3)
,SUBPARTITION ap_03 VALUES LESS THAN (4)
,SUBPARTITION ap_04 VALUES LESS THAN (5)
,SUBPARTITION ap_05 VALUES LESS THAN (6)
,SUBPARTITION ap_06 VALUES LESS THAN (7)
,SUBPARTITION ap_07 VALUES LESS THAN (8)
,SUBPARTITION ap_08 VALUES LESS THAN (9)
,SUBPARTITION ap_09 VALUES LESS THAN (10)
,SUBPARTITION ap_10 VALUES LESS THAN (11)
,SUBPARTITION ap_11 VALUES LESS THAN (12)
,SUBPARTITION ap_12 VALUES LESS THAN (13)
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021)
,PARTITION ledger_2021 VALUES LESS THAN (2022)
) ENABLE ROW MOVEMENT NOLOGGING
/
@treeselectors
@popledgerCREATE MATERIALIZED VIEW mv_ledger_2019
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1)
,PARTITION ap_01 VALUES LESS THAN (2)
,PARTITION ap_02 VALUES LESS THAN (3)
,PARTITION ap_03 VALUES LESS THAN (4)
,PARTITION ap_04 VALUES LESS THAN (5)
,PARTITION ap_05 VALUES LESS THAN (6)
,PARTITION ap_06 VALUES LESS THAN (7)
,PARTITION ap_07 VALUES LESS THAN (8)
,PARTITION ap_08 VALUES LESS THAN (9)
,PARTITION ap_09 VALUES LESS THAN (10)
,PARTITION ap_10 VALUES LESS THAN (11)
,PARTITION ap_11 VALUES LESS THAN (12)
,PARTITION ap_12 VALUES LESS THAN (13)
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2019
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1)
,PARTITION ap_01 VALUES LESS THAN (2)
,PARTITION ap_02 VALUES LESS THAN (3)
,PARTITION ap_03 VALUES LESS THAN (4)
,PARTITION ap_04 VALUES LESS THAN (5)
,PARTITION ap_05 VALUES LESS THAN (6)
,PARTITION ap_06 VALUES LESS THAN (7)
,PARTITION ap_07 VALUES LESS THAN (8)
,PARTITION ap_08 VALUES LESS THAN (9)
,PARTITION ap_09 VALUES LESS THAN (10)
,PARTITION ap_10 VALUES LESS THAN (11)
,PARTITION ap_11 VALUES LESS THAN (12)
,PARTITION ap_12 VALUES LESS THAN (13)
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2020
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop@pop2020m7.sql
23:57:09 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
MVIEW_NAME STALENESS LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2019 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
MV_LEDGER_2020 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
Elapsed: 00:00:00.00
23:57:09 SQL> select * from user_mview_detail_relations;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAILOBJ DETAILOBJ_ALIAS D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT MV_LEDGER_2019 SCOTT PS_LEDGER TABLE PS_LEDGER Y 55 1
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER TABLE PS_LEDGER Y 55 1
Elapsed: 00:00:13.46
23:57:23 SQL> select * from user_mview_detail_partition;
no rows selected
Elapsed: 00:00:00.00
23:57:23 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT MV_LEDGER_2019 SCOTT PS_LEDGER LEDGER_2020 LEDGER_2020_AP_07 8 STALE
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER LEDGER_2020 LEDGER_2020_AP_07 8 STALEPlan hash value: 1498194812
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1703 | 128K| 421 (2)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1703 | 128K| 421 (2)| 00:00:01 | | |
|* 2 | HASH JOIN | | 1703 | 128K| 420 (2)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 238 | 4522 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 7156 | 405K| 418 (2)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 208 | 6032 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 104 | 1872 | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 104 | 1872 | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE ITERATOR | | 68804 | 1948K| 415 (2)| 00:00:01 | 2 | 7 |
|* 10 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2019 | 68804 | 1948K| 415 (2)| 00:00:01 | 2 | 7 |
----------------------------------------------------------------------------------------------------------------------
….
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_LEDGER_2019"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("MV_LEDGER_2019"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
"MV_LEDGER_2019"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
10 - filter("MV_LEDGER_2019"."ACCOUNTING_PERIOD"<=6)Plan hash value: 1321682226
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 7 (15)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 92 | 7 (15)| 00:00:01 | | |
|- * 2 | HASH JOIN | | 1 | 92 | 6 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 92 | 6 (0)| 00:00:01 | | |
|- 4 | STATISTICS COLLECTOR | | | | | | | |
|- * 5 | HASH JOIN | | 1 | 73 | 5 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 73 | 5 (0)| 00:00:01 | | |
|- 7 | STATISTICS COLLECTOR | | | | | | | |
|- * 8 | HASH JOIN | | 1 | 55 | 4 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 | | |
|- 10 | STATISTICS COLLECTOR | | | | | | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | 3 | 3 |
| 12 | PARTITION RANGE SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | 8 | 8 |
| * 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER | 1 | 44 | 3 (0)| 00:00:01 | 36 | 36 |
| * 14 | INDEX RANGE SCAN | PSXLEDGER | 1 | | 2 (0)| 00:00:01 | 36 | 36 |
| * 15 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
|- * 16 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
| * 17 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
|- * 18 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
| * 19 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
|- * 20 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
13 - filter("A"."CURRENCY_CD"='GBP')
14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
16 - access("L1"."SELECTOR_NUM"=30982)
17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
18 - access("L"."SELECTOR_NUM"=30985)
19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
20 - access("L2"."SELECTOR_NUM"=30984) Sub- Rows
Part Part per
TABLE_NAME Pos PARTITION_NAME Pos SUBPARTITION_NAME NUM_ROWS BLOCKS Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ----------------
MV_LEDGER_2019 1 AP_BF 72886 252 289.2 ENABLED BASIC
2 AP_01 72925 252 289.4 ENABLED BASIC
3 AP_02 72736 251 289.8 ENABLED BASIC
4 AP_03 72745 251 289.8 ENABLED BASIC
5 AP_04 72649 251 289.4 ENABLED BASIC
6 AP_05 71947 249 288.9 ENABLED BASIC
7 AP_06 72903 252 289.3 ENABLED BASIC
8 AP_07 72510 250 290.0 ENABLED BASIC
9 AP_08 72520 251 288.9 ENABLED BASIC
10 AP_09 72965 252 289.5 ENABLED BASIC
11 AP_10 72209 250 288.8 ENABLED BASIC
12 AP_11 72647 251 289.4 ENABLED BASIC
13 AP_12 73121 253 289.0 ENABLED BASIC
14 AP_CF 1999 25 80.0 ENABLED BASIC
946762 3290 287.8
MV_LEDGER_2020 1 AP_BF 72475 256 283.1 ENABLED BASIC
2 AP_01 72981 256 285.1 ENABLED BASIC
3 AP_02 72726 256 284.1 ENABLED BASIC
4 AP_03 72844 256 284.5 ENABLED BASIC
5 AP_04 72709 256 284.0 ENABLED BASIC
6 AP_05 72535 256 283.3 ENABLED BASIC
7 AP_06 72419 256 282.9 ENABLED BASIC
8 AP_07 0 0 ENABLED BASIC
9 AP_08 0 0 ENABLED BASIC
10 AP_09 0 0 ENABLED BASIC
11 AP_10 0 0 ENABLED BASIC
12 AP_11 0 0 ENABLED BASIC
13 AP_12 0 0 ENABLED BASIC
14 AP_CF 0 0 ENABLED BASIC
508689 1792 283.9 Sub- Rows
Part Part per
TABLE_NAME Pos PARTITION_NAME Pos SUBPARTITION_NAME NUM_ROWS BLOCKS Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ------------------------------
MV_LEDGER_2019 1 AP_BF 72886 252 289.2 ENABLED BASIC
2 AP_01 72925 252 289.4 ENABLED BASIC
3 AP_02 72736 251 289.8 ENABLED BASIC
4 AP_03 72745 251 289.8 ENABLED BASIC
5 AP_04 72649 251 289.4 ENABLED BASIC
6 AP_05 71947 249 288.9 ENABLED BASIC
7 AP_06 72903 252 289.3 ENABLED BASIC
8 AP_07 72510 250 290.0 ENABLED BASIC
9 AP_08 72520 251 288.9 ENABLED BASIC
10 AP_09 72965 252 289.5 ENABLED BASIC
11 AP_10 72209 250 288.8 ENABLED BASIC
12 AP_11 72647 251 289.4 ENABLED BASIC
13 AP_12 73121 253 289.0 ENABLED BASIC
14 AP_CF 1999 25 80.0 ENABLED BASIC
946762 3290 287.8
MV_LEDGER_2020 1 AP_BF 72475 384 188.7 ENABLED BASIC
2 AP_01 72981 384 190.1 ENABLED BASIC
3 AP_02 72726 384 189.4 ENABLED BASIC
4 AP_03 72844 384 189.7 ENABLED BASIC
5 AP_04 72709 384 189.3 ENABLED BASIC
6 AP_05 72535 384 188.9 ENABLED BASIC
7 AP_06 72419 384 188.6 ENABLED BASIC
8 AP_07 72795 1006 72.4 ENABLED BASIC
9 AP_08 0 0 ENABLED BASIC
10 AP_09 0 0 ENABLED BASIC
11 AP_10 0 0 ENABLED BASIC
12 AP_11 0 0 ENABLED BASIC
13 AP_12 0 0 ENABLED BASIC
14 AP_CF 0 0 ENABLED BASIC
581484 3694 157.4MVNAME CAPABILITY_NAME P REL_TEXT MSGTXT
------------------------------ ------------------------------ - -------------------- ------------------------------------------------------------
MV_LEDGER_2019 PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE Y PS_LEDGER
REFRESH_FAST_AFTER_INSERT N SCOTT.PS_LEDGER the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N POSTED_TOTAL_AMT SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT Y
PCT_TABLE_REWRITE Y PS_LEDGER
MV_LEDGER_2020 PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE Y PS_LEDGER
REFRESH_FAST_AFTER_INSERT N SCOTT.PS_LEDGER the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N POSTED_TOTAL_AMT SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT Y
PCT_TABLE_REWRITE Y PS_LEDGERDemonstration 7: Partition on Accounting Period, Subpartition on Fiscal Year!
This final example still composite partitions the ledger table, but now I will swap the partitioning and sub-partitioning. I will range partition on ACCOUNTING PERIOD into 14 partitions per fiscal year and will subpartition on FISCAL_YEAR. The intention is to demonstrate that the partition elimination will still work correctly and that I will only have to refresh a single accounting period.However, you will see that there are some problems, and I can't work around all of them.
I will use a template so that each accounting period partition will have the same fiscal year subpartitions.
I will still only range partition the MV on accounting period. We don't need to partition it on FISCAL_YEAR since it only contains a single year.
I can't specify physical attributes on subpartitions, only partitions. So I have to come along afterwards and alter the sub-partitions. I am going to do that before I populate the data so it is compressed on load rather than load it and rebuild it afterwards.
If I query periods 1-6 in 2018 I get correct partition elimination. Oracle inspects 6 partitions, 1 sub-partition on each. So swapping the composite partitioning types and columns should not affect performance.
USER_MVIEW_DETAIL_SUBPARTITION correctly identified the one stale sub-partition, but USER_MVIEW_DETAIL_PARTITION reports that one range partition is stale
I get query rewrite as you would expect, and as seen in demo 5. Fiscal year 2019, period 7 still rewrites because the partition is not stale
Fiscal year 2020 period 7 doesn't rewrite, because the subpartition is stale.
As we have already seen refresh processes all subpartitions for a partition. Now, not surprisingly, the refresh process truncates the partition for period 7 in both the 2019 and 2020 MVs even though only the 2020 data was affected. So because period 7 was stale in one fiscal year, it processed all fiscal years. We would have had the same problem if I had composite partitioned the materialized view to match table, it would have truncated and reprocessed fiscal yeares for period 7.
CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION TEMPLATE
(SUBPARTITION ledger_2018 VALUES LESS THAN (2019)
,SUBPARTITION ledger_2019 VALUES LESS THAN (2020)
,SUBPARTITION ledger_2020 VALUES LESS THAN (2021)
,SUBPARTITION ledger_2021 VALUES LESS THAN (2022))
(PARTITION ap_bf VALUES LESS THAN (1)
,PARTITION ap_01 VALUES LESS THAN (2)
,PARTITION ap_02 VALUES LESS THAN (3)
,PARTITION ap_03 VALUES LESS THAN (4)
,PARTITION ap_04 VALUES LESS THAN (5)
,PARTITION ap_05 VALUES LESS THAN (6)
,PARTITION ap_06 VALUES LESS THAN (7)
,PARTITION ap_07 VALUES LESS THAN (8)
,PARTITION ap_08 VALUES LESS THAN (9)
,PARTITION ap_09 VALUES LESS THAN (10)
,PARTITION ap_10 VALUES LESS THAN (11)
,PARTITION ap_11 VALUES LESS THAN (12)
,PARTITION ap_12 VALUES LESS THAN (13)
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE))
ENABLE ROW MOVEMENT NOLOGGING
/set serveroutput on
DECLARE
l_sql CLOB;
BEGIN
FOR i IN (
select *
from user_tab_subpartitions
where table_name = 'PS_LEDGER'
and subpartition_name like 'AP%LEDGER%201%'
and (compression = 'DISABLED' OR pct_free>0)
order by table_name, partition_position, subpartition_position
) LOOP
l_sql := 'ALTER TABLE '||i.table_name||' MOVE SUBPARTITION '||i.subpartition_name||' COMPRESS UPDATE INDEXES';
dbms_output.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END;
/
@treeselectors
@popledgerPlan hash value: 2690363151
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 717 | 66681 | 2244 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 717 | 66681 | 2244 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 717 | 66681 | 2243 (1)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 258 | 4902 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 2776 | 200K| 2241 (1)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 208 | 6032 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 104 | 1872 | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 104 | 1872 | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE ITERATOR| | 26693 | 1173K| 2238 (1)| 00:00:01 | 2 | 7 |
| 10 | PARTITION RANGE SINGLE | | 26693 | 1173K| 2238 (1)| 00:00:01 | 1 | 1 |
|* 11 | TABLE ACCESS FULL | PS_LEDGER | 26693 | 1173K| 2238 (1)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
11 - filter("A"."ACCOUNTING_PERIOD"<=6 AND "A"."FISCAL_YEAR"=2018 AND "A"."LEDGER"='ACTUALS' AND
"A"."CURRENCY_CD"='GBP')CREATE MATERIALIZED VIEW mv_ledger_2019
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1)
,PARTITION ap_01 VALUES LESS THAN (2)
,PARTITION ap_02 VALUES LESS THAN (3)
,PARTITION ap_03 VALUES LESS THAN (4)
,PARTITION ap_04 VALUES LESS THAN (5)
,PARTITION ap_05 VALUES LESS THAN (6)
,PARTITION ap_06 VALUES LESS THAN (7)
,PARTITION ap_07 VALUES LESS THAN (8)
,PARTITION ap_08 VALUES LESS THAN (9)
,PARTITION ap_09 VALUES LESS THAN (10)
,PARTITION ap_10 VALUES LESS THAN (11)
,PARTITION ap_11 VALUES LESS THAN (12)
,PARTITION ap_12 VALUES LESS THAN (13)
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2019
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1)
,PARTITION ap_01 VALUES LESS THAN (2)
,PARTITION ap_02 VALUES LESS THAN (3)
,PARTITION ap_03 VALUES LESS THAN (4)
,PARTITION ap_04 VALUES LESS THAN (5)
,PARTITION ap_05 VALUES LESS THAN (6)
,PARTITION ap_06 VALUES LESS THAN (7)
,PARTITION ap_07 VALUES LESS THAN (8)
,PARTITION ap_08 VALUES LESS THAN (9)
,PARTITION ap_09 VALUES LESS THAN (10)
,PARTITION ap_10 VALUES LESS THAN (11)
,PARTITION ap_11 VALUES LESS THAN (12)
,PARTITION ap_12 VALUES LESS THAN (13)
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2020
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop@pop2020m7.sql
MVIEW_NAME STALENESS LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2019 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
MV_LEDGER_2020 NEEDS_COMPILE COMPLETE NEEDS_COMPILE
01:02:53 SQL> select * from user_mview_detail_relations;
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAILOBJ DETAILOBJ_ALIAS D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT MV_LEDGER_2019 SCOTT PS_LEDGER TABLE PS_LEDGER Y 55 1
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER TABLE PS_LEDGER Y 55 1
01:03:06 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';
Detailobj
OWNER MVIEW_NAME Owner DETAILOBJ_NAME DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT MV_LEDGER_2019 SCOTT PS_LEDGER AP_07 AP_07_LEDGER_2020 3 STALE
SCOTT MV_LEDGER_2020 SCOTT PS_LEDGER AP_07 AP_07_LEDGER_2020 3 STALEPlan hash value: 387550712
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1967 | 147K| 76 (3)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1967 | 147K| 76 (3)| 00:00:01 | | |
|* 2 | HASH JOIN | | 1967 | 147K| 75 (2)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 258 | 4902 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 7576 | 429K| 73 (2)| 00:00:01 | | |
| 5 | MERGE JOIN CARTESIAN | | 208 | 6032 | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 2 | 22 | 1 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 104 | 1872 | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 104 | 1872 | 1 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SINGLE | | 72486 | 2052K| 70 (2)| 00:00:01 | 8 | 8 |
|* 10 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2019 | 72486 | 2052K| 70 (2)| 00:00:01 | 8 | 8 |
----------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_LEDGER_2019"."ACCOUNT"="L2"."RANGE_FROM_10")
3 - access("L2"."SELECTOR_NUM"=30984)
4 - access("MV_LEDGER_2019"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
"MV_LEDGER_2019"."CHARTFIELD1"="L"."RANGE_FROM_10")
6 - access("L1"."SELECTOR_NUM"=30982)
8 - access("L"."SELECTOR_NUM"=30985)
10 - filter("MV_LEDGER_2019"."ACCOUNTING_PERIOD"=7)Plan hash value: 1321682226
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 7 (15)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 92 | 7 (15)| 00:00:01 | | |
|- * 2 | HASH JOIN | | 1 | 92 | 6 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 92 | 6 (0)| 00:00:01 | | |
|- 4 | STATISTICS COLLECTOR | | | | | | | |
|- * 5 | HASH JOIN | | 1 | 73 | 5 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 73 | 5 (0)| 00:00:01 | | |
|- 7 | STATISTICS COLLECTOR | | | | | | | |
|- * 8 | HASH JOIN | | 1 | 55 | 4 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 | | |
|- 10 | STATISTICS COLLECTOR | | | | | | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | 8 | 8 |
| 12 | PARTITION RANGE SINGLE | | 1 | 44 | 3 (0)| 00:00:01 | 3 | 3 |
| * 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER | 1 | 44 | 3 (0)| 00:00:01 | 31 | 31 |
| * 14 | INDEX RANGE SCAN | PSXLEDGER | 1 | | 2 (0)| 00:00:01 | 31 | 31 |
| * 15 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
|- * 16 | INDEX RANGE SCAN | PS_PSTREESELECT05 | 1 | 11 | 1 (0)| 00:00:01 | | |
| * 17 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
|- * 18 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 18 | 1 (0)| 00:00:01 | | |
| * 19 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
|- * 20 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 1 | 19 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
13 - filter("A"."CURRENCY_CD"='GBP')
14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
16 - access("L1"."SELECTOR_NUM"=30982)
17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
18 - access("L"."SELECTOR_NUM"=30985)
19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
20 - access("L2"."SELECTOR_NUM"=30984)…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2019" TRUNCATE PARTITION AP_07 UPDATE GLOBAL INDEXES
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2019" PARTITION ( AP_07 ) ("BUSINESS_UNIT",
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" ,
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" , "PS_LEDGER"."ACCOUNTING_PERIOD" P0,
SUM("PS_LEDGER"."POSTED_TOTAL_AMT") FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2019 AND "PS_LEDGER"."LEDGER"='ACTUALS'
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND ( ( ( ( ( ( "PS_LEDGER"."ACCOUNTING_PERIOD" >= 7 ) ) )
AND ( ( ( "PS_LEDGER"."ACCOUNTING_PERIOD" < 8 ) ) ) ) ) )GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE PARTITION AP_07 UPDATE GLOBAL INDEXES
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( AP_07 ) ("BUSINESS_UNIT",
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" ,
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" , "PS_LEDGER"."ACCOUNTING_PERIOD" P0,
SUM("PS_LEDGER"."POSTED_TOTAL_AMT") FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2020 AND "PS_LEDGER"."LEDGER"='ACTUALS'
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND ( ( ( ( ( ( "PS_LEDGER"."ACCOUNTING_PERIOD" >= 7 ) ) )
AND ( ( ( "PS_LEDGER"."ACCOUNTING_PERIOD" < 8 ) ) ) ) ) )GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…Partition pruning still worked correctly after swapping the partitioning and sub-partitioning columns.
It also correctly controlled query rewrite.
However, the PCT refresh processed all years for the single accounting period, rather than all accounting periods for the single year. That is less work if you have fewer fiscal years than accounting periods. Generally, I see systems only contain 3 to 6 fiscal years of data. However, it is also refreshing MVs that didn't need to be refreshed
Swapping the partitioning columns has also made the management of the partitions in the ledger table much more complicated.
- I can't interval sub-partition, so I can't automatically add partitions for future fiscal years on demand. Instead, I am going to have to add new fiscal year subpartitions to each of the 1 4 range partitions.
- I can't specify storage options or compression attributes on sub-partitions in the create table DDL command, so I have to come along afterwards with PL/SQL to alter the sub-partitions.
Conclusion
PCT does track individually stale partitions and subpartitions, but the subsequent refresh is only done by partition. If one subpartition is stale, then all the subpartitions in that partition is refreshed. If you use composite partitioning then you may have to accept reprocessing more data than is absolutely necessary rather than create a partitioning strategy that is less effective.The subpartition key should be subordinate to the partition key. In the ledger example that I have used, I think it is better to partition by fiscal year and subpartition by accounting period (demonstration 5) than vice versa (demonstration 7).
PCT doesn't work when there are multiple partitioning key columns. So you need to find a single partition key column that is used by the application that is sufficiently selective to restrict the number of partitions being refreshed.
The partitioning on the table and the materialized view must be the same type of partitioning and on the same column. Otherwise, while PCT may still work, the refresh process may not be possible to populate the materialized view in direct-path mode, and it may not be possible to maintain compressed materialized views.
There will be a balance to be struck. On the one hand application performance can be improved by partitioning application tables in a way that partition elimination is effective, but that partitioning strategy may not work with PCT. On the other, reporting performance can be improved maintaining fresh pre-aggregated data in materialized views, and PCT can help to keep the materialized fresh with less overhead.
@go-faster.co.uk on BlueSky
Posts
No comments :
Post a Comment