Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Row sizes 3

Several years ago I published a couple of examples of scripts that I’d been using since Oracle 6 (with minor changes over time) to analyse the content of tables in some detail. The first was a breakdown of the lengths of the rows in the table, the second was a map showing the distribution of nulls in the rows. There used to be a third component of the analysis which produced a report of the number of non-null entries for each column in the table,  but by the time I published the first two parts there didn’t seem to be much point in publishing the third since you could get a sufficiently accurate picture by querying view user_tab_columns after gathering stats:


break on num_rows

select
        tab.num_rows, col.column_name , col.num_nulls
from 
        user_tables             tab
join
        user_tab_columns        col
on
        col.table_name = tab.table_name
where
        tab.table_name = 'T1' 
order by 
        col.num_nulls desc, col.column_id
/


  NUM_ROWS COLUMN_NAME           NUM_NULLS
---------- -------------------- ----------
     56777 EDITION_NAME              56777
           CREATED_APPID             56777
           CREATED_VSNID             56777
           MODIFIED_APPID            56777
           MODIFIED_VSNID            56777
           SUBOBJECT_NAME            56570
           DATA_OBJECT_ID            55353
           DEFAULT_COLLATION         51058
           EDITIONABLE               40216
           OWNER                         0
           OBJECT_NAME                   0
           OBJECT_ID                     0
           OBJECT_TYPE                   0
           CREATED                       0
           LAST_DDL_TIME                 0
           TIMESTAMP                     0
           STATUS                        0
           TEMPORARY                     0
           GENERATED                     0
           SECONDARY                     0
           NAMESPACE                     0
           SHARING                       0
           ORACLE_MAINTAINED             0
           APPLICATION                   0
           DUPLICATED                    0
           SHARDED                       0


In this example t1 is a copy of view all_objects and I’ve listed the columns in order of most frequently null with the table’s num_rows reported at the top for comparison purposes. If you were going to do something that made it a good idea to rebuild the table you could use this report as a guide to re-arranging the column order to position the most frequently null columns towards the end of the row (saving the “trailing nulls take no space” length bytes).

This approach depends on the stats being up to date and accurate, of course, so if you didn’t have them, and didn’t want to collect them, another strategy would be to run a query like the following:


select
        to_char(count(OWNER),'999,999,999,999,999') OWNER,
        to_char(count(OBJECT_NAME),'999,999,999,999,999') OBJECT_NAME,
        to_char(count(SUBOBJECT_NAME),'999,999,999,999,999') SUBOBJECT_NAME,
        to_char(count(OBJECT_ID),'999,999,999,999,999') OBJECT_ID,
        to_char(count(DATA_OBJECT_ID),'999,999,999,999,999') DATA_OBJECT_ID,
        to_char(count(OBJECT_TYPE),'999,999,999,999,999') OBJECT_TYPE,
        to_char(count(CREATED),'999,999,999,999,999') CREATED,
        to_char(count(LAST_DDL_TIME),'999,999,999,999,999') LAST_DDL_TIME,
        to_char(count(TIMESTAMP),'999,999,999,999,999') TIMESTAMP,
        to_char(count(STATUS),'999,999,999,999,999') STATUS,
        to_char(count(TEMPORARY),'999,999,999,999,999') TEMPORARY,
        to_char(count(GENERATED),'999,999,999,999,999') GENERATED,
        to_char(count(SECONDARY),'999,999,999,999,999') SECONDARY,
        to_char(count(NAMESPACE),'999,999,999,999,999') NAMESPACE,
        to_char(count(EDITION_NAME),'999,999,999,999,999') EDITION_NAME,
        to_char(count(SHARING),'999,999,999,999,999') SHARING,
        to_char(count(EDITIONABLE),'999,999,999,999,999') EDITIONABLE,
        to_char(count(ORACLE_MAINTAINED),'999,999,999,999,999') ORACLE_MAINTAINED,
        to_char(count(APPLICATION),'999,999,999,999,999') APPLICATION,
        to_char(count(DEFAULT_COLLATION),'999,999,999,999,999') DEFAULT_COLLATION,
        to_char(count(DUPLICATED),'999,999,999,999,999') DUPLICATED,
        to_char(count(SHARDED),'999,999,999,999,999') SHARDED,
        to_char(count(CREATED_APPID),'999,999,999,999,999') CREATED_APPID,
        to_char(count(CREATED_VSNID),'999,999,999,999,999') CREATED_VSNID,
        to_char(count(MODIFIED_APPID),'999,999,999,999,999') MODIFIED_APPID,
        to_char(count(MODIFIED_VSNID),'999,999,999,999,999') MODIFIED_VSNID,
        to_char(count(*),'999,999,999,999,999') row_count 
fromi
         t1
;

You don’t really need the to_char() function, but it’s a conveience for what I’m going to do with the SQL. Obviouslty it would be a bit tedious to create this statement by hand so, as I did in the previous “rowsize” notes, I’ve written some code to generate it for me:


rem
rem     row_size_2b.sql
rem     
rem     Generate SQL to report counts of 
rem     non-null columns in a table.
rem

set linesize 32000
set feedback off
define m_table = '&1'

declare
        m_string        varchar2(32000) := 'select ';
        m_cursor        sys_refcursor;

begin
        for r in (
                select 
                        column_name, data_type
                from    user_tab_columns
                where   table_name = upper('&m_table')
        ) loop
                m_string := m_string ||  
                                ' to_char(count(' || trim(r.column_name) || '),''999,999,999,999,999'') ' ||
                                trim(r.column_name) || ',' || chr(10) ;
        end loop;

        m_string := m_string || ' to_char(count(*),''999,999,999,999,999'') row_count from &m_table';

--      dbms_output.put_line(m_string);
        print_table(m_string);

end;
/

set linesize 156

The script accepts a table name from the user’s schema (you could edit this to query dba_tab_columns), constructs a string (as shown above – though I’ve done a little cosmetic work on it), and then passes the resulting string to a verion of Tom Kyte’s print_table() routine which produces the following output:


OWNER                         :               56,777
OBJECT_NAME                   :               56,777
SUBOBJECT_NAME                :                  207
OBJECT_ID                     :               56,777
DATA_OBJECT_ID                :                1,424
OBJECT_TYPE                   :               56,777
CREATED                       :               56,777
LAST_DDL_TIME                 :               56,777
TIMESTAMP                     :               56,777
STATUS                        :               56,777
TEMPORARY                     :               56,777
GENERATED                     :               56,777
SECONDARY                     :               56,777
NAMESPACE                     :               56,777
EDITION_NAME                  :                    0
SHARING                       :               56,777
EDITIONABLE                   :               16,561
ORACLE_MAINTAINED             :               56,777
APPLICATION                   :               56,777
DEFAULT_COLLATION             :                5,719
DUPLICATED                    :               56,777
SHARDED                       :               56,777
CREATED_APPID                 :                    0
CREATED_VSNID                 :                    0
MODIFIED_APPID                :                    0
MODIFIED_VSNID                :                    0
ROW_COUNT                     :               56,777

-----------------

1 rows selected

As with many of the scripts I’ve pubished, the task is one I rarely do, so I haven’t bothered to add in all the extra little refinements that might make it foolproof and totally self-contained. In this case, for example I’d have to go through a final couple of manual steps to edit the output by deleteing the last few lines then (since I use vi) issuing “!%!sort -k3 -n -r” which would sort the results in reverse order, numerically, according to the third field.

 

print_table()

Many years ago Tom Kyte published a small PL/SQL procedure to output each row in a table as a list of (column_name , value). I often use the original version, even though Tom refined it several times over the years. From time to time the links in my blog that I’ve set up to point to Tom’s code have simply stopped working so I’ve decided to publish a reference copy on my blog in case newer readers need to find a copy and the current link above stops  working.

rem
rem     Script:         print_table.sql
rem     Dated:          Nov 2001
rem     Author:         Tom Kyte - from Expert One on One Oracle.
rem
rem     Purpose:
rem     Prints the results of a query one column per line.
rem
rem     Created as a 'authid current_user' so that it can
rem     access any table that the caller is allowed to access
rem     from the SQL*Plus prompt.
rem
rem     A suitably privilefed user could also make this publicly
rem     available through the public synonym and grant at the end
rem     of the script
rem
rem     Minor modifications by Jonathan Lewis to separate rows
rem     in the result set, and report the number of rows returned
rem

create or replace procedure print_table( p_query in varchar2 )
authid current_user
is
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(4000);
        l_status        integer;
        l_descTbl       dbms_sql.desc_tab;
        l_colCnt        number;
        l_rowcnt        number := 0;
begin
        execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

        dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
        dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

        for i in 1 .. l_colCnt loop
                dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000 );
        end loop;

        l_status := dbms_sql.execute(l_theCursor);

        while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
                for i in 1 .. l_colCnt loop
                        dbms_sql.column_value( l_theCursor, i, l_columnValue );
                        dbms_output.put_line(
                                rpad( l_descTbl(i).col_name, 30 )
                                || ' : ' || l_columnValue
                        );
                end loop;
                dbms_output.new_line;
                dbms_output.put_line( '-----------------' );
                dbms_output.new_line;
                l_rowcnt := l_rowcnt + 1;
        end loop;

        dbms_output.put_line(l_rowcnt || ' rows selected');

        execute immediate 'alter session set nls_date_format=''dd-mon-rr'' ';

exception
        when others then
                execute immediate 'alter session set nls_date_format=''dd-mon-rr'' ';
        raise;
end;
/

-- create public synonym print_table for print_table;
-- grant execute on print_table to public;

The reason I’m publishing this now is that I’m about to publish the third variant of a piece of code I wrote many years ago, and first blogged about in 2012.

DynamoDB PartiQL – part II: SELECT

By Franck Pachot

.
In the previous post I insertd a few rows in a Demo table using the SQL-like new API on DynamoDB. I checked my items with a SELECT but was limited in the ORDER BY clause. Here is the most important to understand: there are no additional data processing engine here. PartiQL (pronounce it like ‘particle’ and it helps to avoid any kind of dyslexia) parses a statement with INSERT/UPDATE/DELETE/SELECT and calls the NoSQL API you already know (Put/Get/Update/Delete Item, Query and Scan). It looks like SQL but SQL is a language that declares operations on a set of rows, like relational tables or views, which are a logical layer above the physical model. In RDBMS, you build your SQL queries according to your business needs, not the physical layout. Of course, the physical layout (like indexing, partitioning) is also optimized for this access, but this is done independently. With PartiQL on DynamoDB you must know which operation will happen when you write your statement. Because all the simplicity and scalability of DynamoDB resides on the bounded API that matches the physical layout:

  • GetItem does partition pruning + unique hash/index access to retrieve one item
  • Query does partition pruning + index access to retrieve a sorted range of items
  • Scan reads all partitions, possibly in parallel, to retrieve all items

Of course, the cost is different. With the DynamoDB API you know which one you are doing because you call a different operation. With PartiQL you should know what you are doing but you execute the same statement (SELECT) and the data access will depend on the columns referenced in WHERE clause. Basically, if you don’t have an equality predicate on the partition (HASH) key, you have to read all partitions (Scan). If you have an equality predicate on the partition (HASH) key and inequality on the sort (RANGE) key you benefit from partition pruning (Query). This is obvious when you know what is a hash function, but error-prone if you don’t know the data model. The DynamoDB API helps you to prevent that because your fingers should hurt when typing “scan” for a large table.

Scan

So, if what you want is actually get all items, because you need all of them, or maybe to filter out a small part of them only, you want a scan. Yes, it reads everything, but it is the most efficient access to read a large portion of your table. Because with one RCU you can get many items. Doing the same (getting all items) with GetItem would cost one RCU per item (I suppose strong consistency and small items here). To put it basically, for OLTP workload (many users reading few items) you avoid scans on large tables. DynamoDB is a key-value store: the goal is to access by the key. And for some reporting or to export data, you may scan, which is expensive (in time and RCU) but not done frequently.

As seen in the previous post, scanning the whole table to get all items with all attributes is a simple SELECT * FROM:


[opc@a aws]$ aws dynamodb execute-statement --statement "select * from Demo"

{"Items":[
{"MyKeyPart":{"N":"2"},"MyUnstructuredData":{"S":"a"},"MyKeySort":{"N":"1"}},
{"MyKeyPart":{"N":"2"},"MyUnstructuredData":{"S":"use parameters when embedding SQL in programs"},"MyKeySort":{"N":"2"}},
{"MyKeyPart":{"N":"2"},"MyUnstructuredData":{"S":"c"},"MyKeySort":{"N":"3"}},
{"MyKeyPart":{"N":"2"},"MyUnstructuredData":{"S":"d"},"MyKeySort":{"N":"4"}},
{"MyKeyPart":{"N":"2"},"MyUnstructuredData":{"S":"e"},"MyKeySort":{"N":"5"}},
{"MyKeyPart":{"N":"1"},"MyUnstructuredData":{"S":"here is my first insert :)"},"MyKeySort":{"N":"1"}}
]}

As long as there’s no equality predicate on the primary key (or the hash part of it in case of composite hash/sort key) the SELECT will do a scan. I mentioned “equality”, we will see later when there are many equality predicates or a list of values to be equal to. We will see later, probably in a further post, what happens with secondary indexes. Anyway, this is not a RDBMS. When you query the table, there’s no query planer to optimize the access to read from an index. If you want to access by a secondary index, the index name must be mentioned in the FROM clause.

Another thing that we have seen in the previous post is that, as it is a scan, you cannot have the partition key in the ORDER BY because DynamoDB does not sort the rows when retrieved from multiple partitions, and PartiQL do not do further data processing on the result. So, basically, there’s no possible ORDER BY when not having a WHERE clause on the partition key:



[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo order by MyKeyPart"

An error occurred (ValidationException) when calling the ExecuteStatement operation: Must have WHERE clause in the statement when using ORDER BY clause.

[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo order by MyKeySort"

An error occurred (ValidationException) when calling the ExecuteStatement operation: Must have WHERE clause in the statement when using ORDER BY clause.

Query

Then, if we query for one partition only, this is a Query rather than a Scan. Here is an example where I select only the items where MyKeyPart = 2 which, with the HASH function, maps to only one partition:


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo where MyKeyPart = 2"

{"Items":[{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"5"}}]}

The items are ordered by MyKeySort even in the absence of ORDER BY because this is how it is stored and retreived physically within each partition. But, as SQL is a declarative language, I prefer not to rely on the order without ORDER BY clause.

Here is the correct way to do it, with no additional cost:


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo where MyKeyPart = 2 order by MyKeySort"
{"Items":[{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"5"}}]}


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo where MyKeyPart = 2 order by MyKeySort desc"
{"Items":[{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"5"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"1"}}]}

Here because there is only one value for MyKeyPart I didn’t need to put MyKeyPart in the ORDER BY, but with multiple values you need to:


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort \
from Demo where MyKeyPart = 1  or MyKeyPart = 2 order by MyKeySort"

An error occurred (ValidationException) when calling the ExecuteStatement operation: Must have hash key in ORDER BY clause when more than one hash key condition specified in WHERE clause.

[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort \
from Demo where MyKeyPart = 1  or MyKeyPart = 2 order by MyKeyPart,MyKeyPart desc"

{"Items":[{"MyKeyPart":{"N":"1"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"5"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"1"}}]}

[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort \
from Demo where MyKeyPart = 1  or MyKeyPart = 2 order by MyKeyPart desc,MyKeyPart"

{"Items":[{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"5"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"1"},"MyKeySort":{"N":"1"}}]}

You might be surprised to see this query with multiple values run as a Query rather than a Scan. What if they come from multiple partitions?
This is possible when the number of values is well known in advance (“1” and “2” here) and then this can be sorted first, and a Query run for each of them. Of course, this will multiply the cost of it. For example, because I know that I inserted values 1 to 5, I can get all my items with:


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort \
from Demo where MyKeyPart in [1,2,3,4,5] order by MyKeyPart,MyKeySort"

{"Items":[{"MyKeyPart":{"N":"1"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"5"}}]}

So I’m able to get all items sorted now? Yes, but for a higher cost than a scan because it will query them one by one. I would be cheaper to Scan here but there is no optimizer to estimate the cost of both operations and choose the cheaper. But at least, the cost is predictable as it is proportional to the number of key values in the list.

I cannot use inequalities, or BETWEEN, because they work on a range and this Query access can be done only on known values.


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo where MyKeyPart between 2 and 2 order by MyKeyPart,MyKeySort"

An error occurred (ValidationException) when calling the ExecuteStatement operation: Must have at least one non-optional hash key condition in WHERE clause when using ORDER BY clause.

Here, even if maths tells me that it is equivalent to equality (“MyKeyPart between 2 and 2” is the same as “MyKeyPart = 2”) we have no optimizer there to do those transformations. The rule is basic: a set of value can be sorted and queried individually but anything else is considered as a range of value that cannot be accessed with a hash function.

How can I be sure about this behaviour? I have a small table where the response time difference is not significant. Be best proof is to see what happens when full table scan is impossible. There’s an IAM policy to deny scans:

I have created a user with deny on “dynamodb:PartiQLSelect” action on condition “dynamodb:FullTableScan”=”True”
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/11/Screens... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/11/Screens... 1024w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/11/Screens... 768w" sizes="(max-width: 1162px) 100vw, 1162px" />
With this user profile I execute the following:


[opc@a aws]$ aws --profile noscan dynamodb execute-statement --statement "select MyKeyPart,MyKeySort \
from Demo"

An error occurred (AccessDeniedException) when calling the ExecuteStatement operation: User: arn:aws:iam::802756008554:user/ddb-noscan is not authorized to perform: dynamodb:PartiQLSelect on resource: arn:aws:dynamodb:eu-west-1:802756008554:table/Demo with an explicit deny

[opc@a aws]$ aws --profile noscan dynamodb execute-statement --statement "select MyKeyPart,MyKeySort \
from Demo where MyKeyPart in [1,2,3,4,5]"                

{"Items":[{"MyKeyPart":{"N":"1"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"5"}}]}

It is clear that when Full Table Scan is denied the WHERE on a list of 5 values is still possible. Because it 5 query calls instead of a scan.

Pagination

I have additionally inserted many rows with MyKeyPart=10 and large size attributes, and query them:


$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo where MyKeyPart=10"

{"Items":[{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"1"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"2"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"3"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"4"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"5"}}],"NextToken":"CS4sUIPi4Efg7eSg4sGJZHJ09C/m8JWMwLXB+DF5n54EIBl6yuPZNAHfoRUFg7qgGg872qXswoXSZEI/XAIfvUPNisWSYGrPiquxLFakMecd6aF/ggaexxpKlhPS+ridkOXu8HoWIuWgSXFRBa32QmIXITRhrSMwuT1Q54+6Li6emcxvtpJfmxvxWf/yQkece5nqQIwH/EC3vAr1SZ4Pd537qexKejVHJ+2QrXALwG283UR/obWc53A2HTQ+G3cNeL4xOvVwp9gsOhlKxhsRrS+GqHRF0IHlGrpsdc0LkbMS1hISuagp/KZ0dqP/v7ejB6HsEHhFYZeKYZBoysTYTzhpB02NF3F4MSKp8QF4nO4vcq4="}

I get a few items and a “Next Token” that is quite large.

I can query the next pages with the –next-token option:


$ ws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo where MyKeyPart=10 \
--next-token CS4sUIPi4Efg7eSg4sGJZHJ09C/m8JWMwLXB+DF5n54EIBl6yuPZNAHfoRUFg7qgGg872qXswoXSZEI/XAIfvUPNisWSYGrPiquxLFakMecd6aF/ggaexxpKlhPS+ridkOXu8HoWIuWgSXFRBa32QmIXITRhrSMwuT1Q54+6Li6emcxvtpJfmxvxWf/yQkece5nqQIwH/EC3vAr1SZ4Pd537qexKejVHJ+2QrXALwG283UR/obWc53A2HTQ+G3cNeL4xOvVwp9gsOhlKxhsRrS+GqHRF0IHlGrpsdc0LkbMS1hISuagp/KZ0dqP/v7ejB6HsEHhFYZeKYZBoysTYTzhpB02NF3F4MSKp8QF4nO4vcq4="

{"Items":[{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"6"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"7"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"8"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"9"}},{"MyKeyPart":{"N":"10"},"MyKeySort":{"N":"10"}}],"NextToken":"FjHEA2wnIK74SlGaS6TiPSv2fEwfiZhJNHyxvJ+qG750oeKlqSNyx9IDdCUD+m2rSpodPIFJhYYQHXBM9sJed3k6qaA/aUk4s4DUlPvZHl7WAJ4rTY0AmNDUYBPqWyCV8FliSsGPtFTfj1A9T4zD1TU6uuvNIORY/zKHtsAjWzT4Jsg5y32MFcVOmOsDBhyWsQotFqxy1ErMGhJy3cQnEvy1P1KpQak6sflzp3sWLWzUgOXQB/xF1PXRtT8w/E1lPk26LnA/L2bA91nucuohN63hP3MVojPH0GkPCjZsx08wJTn4MEpqDArEREWO2XCkL/GI7vTtYw6GXRenKZoatSG55yKCVDkFRuw7cbK749mEIb6r6Xs="}

Again, this is completely different from SQL databases where you have cursors, but this is adapted to DynamoDB query that reads ranges of items with small chunks.

Projection

I used SELECT with * to get the whole item key and attributes (like ALL_ATTRIBUTES), and with a list of attributes to do a query projection (like SPECIFIC_ATTRIBUTES). There’s no aggregation and I don’t think we can do the equivalent of COUNT. Here is how I would do it if it were possible:


[opc@a aws]$ aws dynamodb execute-statement --statement "select count(*) from Demo"

An error occurred (ValidationException) when calling the ExecuteStatement operation: Unexpected path component at 1:8

This clearly not supported (yet).

According to the documentation expressions should be allowed, like this:


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort,size(MyUnstructuredData) from Demo where MyKeyPart=2 and size(MyUnstructuredData)>10"

An error occurred (ValidationException) when calling the ExecuteStatement operation: Unexpected path component at 1:28

Apparently, this size() function is allowed only on the WHERE clause:


[opc@a aws]$ aws dynamodb execute-statement --statement "select MyKeyPart,MyKeySort from Demo where MyKeyPart=2 and size(MyUnstructuredData)>10"

{"Items":[{"MyKeyPart":{"N":"2"},"MyKeySort":{"N":"2"}}]}

In summary, be careful. You must know on which attributes you filter in the WHERE clause. Equality on partition key allows single hash partition access. Without it, it is a scan that can take time, and a lot of RCU (but this stays under limit thanks to 1MB the pagination – see the next post about it).

Cet article DynamoDB PartiQL – part II: SELECT est apparu en premier sur Blog dbi services.

A layered approach to product design

Some time ago I wrote this post.

The Problem With Oracle : If a developer/user can’t do it, it doesn’t exist.

As a result of that post I was invited to speak to a bunch of product managers at Oracle. In that session I spoke about the contents of that post and also about my thoughts regarding a layered approach to product design. I thought I would briefly outline the latter here.

I used to be an Oracle specialist, back in the days when it was possible to have a normal job as an Oracle specialist. Nowadays I find myself doing a really wide range of things. That inevitably means I am Googling my way to solutions a lot of the time. If I had stayed completely in the Oracle world I think my attitude to technology would be very different. There’s something about going outside your comfort zone that helps you gain perspective. In my previous post I said,

“I’m just a generalist that uses a whole bunch of products from a whole bunch of companies, and most of them are too damn hard to use, even with the assistance of Uncle Google.”

In my head, the solution to this issue is for companies to take a layered approach to every aspect of their products. They should be asking themselves.

  1. Who is the audience for this product today?
  2. Who do we expect the audience for this product to be in the future?
  3. Does the product we’ve created meet the needs of these audiences?

Let’s use the Oracle database as an example of this. We could use many other database and non-database products.

  1. There are aspects of the Oracle database that are focused on the developer audience, but there are also aspects of the database that are focused heavily on the administrator audience. So the audience for this product is split.
  2. Over time I expect the DBA role to completely disappear. We have already seen the beginning of this with cloud-based databases. I expect this trend to continue. As a result, I would suggest the future audience of the Oracle database will be solely developers.
  3. Since the product currently has a split audience, and we expect one of those roles to go, I don’t think the database is targeting the correct audience. Anything that is currently a DBA task needs to be automated out of existence. Anything where the DBA is a gatekeeper needs some redesign.

So how do we achieve this? My suggestion would be to take a layered approach. I’ve discussed a similar idea of a layered approach to documentation.

  • Layer 1: Simple how-to.
  • Layer 2: Give me some details, but don’t make my head hurt.
  • Layer 3: I’ve taken some paracetamol. Do your worst!

This time I’m thinking about how the product itself works.

  • Layer 1: The product works out of the box. A developer with no experience of administering this engine can start doing something useful with it immediately. There are no commonly used features that are out of their realm of control. A company or person may choose to stay within this layer for the lifetime of a project and see no ill effects.
  • Layer 2: If the company have some skills in the product, or a specific aspect of the product, they may choose to dip down into this layer. Here they can perform some actions that are not directly covered in layer 1, but this doesn’t mean they lose all the benefits of layer 1. They are not switching off all automations just because they want to deviate from one bit of default functionality.
  • Layer 3: The company have some people with God-mode skills and want to do almost everything by hand. They want to take full control of the system.

The important point is, people want work in the layer(s) they are comfortable in, and still do an effective job. This makes the product accessible to everyone, but doesn’t discriminate against those that want to no-life the product, if they can see a benefit in doing so.

I know there will be objections to this approach, and believe me I can make counter-arguments to it myself, but I don’t see a way forward without taking this sort of approach. I’ll go back to a quote by Jacob Duval where he was comparing MySQL and PostgreSQL.

simply put: mysql has a superior developer experience. postgres has a superior DBA experience. DBA is not really a job anymore, so I pick the developer experience every time.

Jacob Duval

The developer experience has to be the #1 focus from now on!

I’m not underestimating the impact of that statement. It is a massive job for a huge and mature product such as the Oracle database, but not doing so is a death sentence IMHO.

I know some people will see this as a cloud sales pitch, but actually it’s not. I think the on-prem products need to live up to these ideals too. Why? Because I see the future as multi-cloud. If Oracle focus entirely on their cloud offerings, people who decide not to pick Oracle Cloud will be left with a sub-par experience when running Oracle products on other clouds. The result of that is they will pick non-Oracle solutions. I don’t think this is a road Oracle should go down.

Cheers

Tim…

PS. I’ve kept this post purposely vague, because I think focussing on individual features will make the post huge, and detract from the overall message…

Update. Akiva Lichtner raised an interesting point. I thought I would add it here, in case someone else is using their own interpretation of what I am suggesting.

“You can see from Tesla’s speed of change that vertical integration which is the opposite of a layered approach is a superior approach. Also reminds me of Bryan Cantrill’s old DTrace talk where he says that the layers conspire to make the whole system impossible to troubleshoot”

Akiva Lichtner

My response.

“It depends what your interpretation of the layers are. Sounds like you are interpreting it as something different to me. The Tesla interface has layers. It can self-drive, or you can drive for yourself. Just like what I’m talking about…”

Me

I’m not suggested we should just keep stacking layer-upon-layer on the existing products. My focus is very much on the potential for a layered audience. As I mentioned above, a Tesla can use autopilot or be driven by a human. Same car. Two modes of operation. We could consider them two audiences.

The post A layered approach to product design first appeared on The ORACLE-BASE Blog.


A layered approach to product design was first posted on November 25, 2020 at 9:18 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Reasons why SELECT * is bad for SQL performance

Here’s a list of reasons why SELECT * is bad for SQL performance, assuming that your application doesn’t actually need all the columns. When I write production code, I explicitly specify the columns of interest in the select-list (projection), not only for performance reasons, but also for application reliability reasons. For example, will your application’s data processing code suddenly break when a new column has been added or the column order has changed in a table?

Reasons why SELECT * is bad for SQL performance

Here’s a list of reasons why SELECT * is bad for SQL performance, assuming that your application doesn’t actually need all the columns. When I write production code, I explicitly specify the columns of interest in the select-list (projection), not only for performance reasons, but also for application reliability reasons. For example, will your application’s data processing code suddenly break when a new column has been added or the column order has changed in a table?

PeopleSoft Financials Ledger Partitioning Recommendations

I have written previously about partitioning the PS_LEDGER table in Financials for the benefit of General Ledger (GL) reporting.  I have always recommended top-level range partitioning on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD with separate partitions for each accounting period.  It also leaves sub-partitioning available to use another column, usually LEDGER.

However, recent research into partition change tracking (PCT) and materialized views has made me question that advice.  The decision is not as clear cut.

Summary Ledgers or Materialized Views

You can create summary ledger tables in PeopleSoft Financials that are incrementally maintained by batch processes, and then nVision reports can reference the summary ledgers instead.  If the summary ledgers are not up to date, then the reports will produce out of date results.

Similarly, materialized views can be used in an Oracle database to create pre-generated reporting tables.  An application can reference a materialized view directly, or the database can dynamically rewrite submitted SQL queries to use the materialized view if they are sufficiently similar to the query that created the materialized view and if the optimizer judges that it is cheaper to query the materialized view.  By default, the database will check that the materialized view is up to date, that is no change has been made to the underlying tables since the last refresh commenced, before it can rewrite the SQL query.  So the query will always return the same data, but if the materialized view is out of date you don't get the performance improvement.

You can optionally choose to configure the database to write SQL queries to use stale materialized views by setting QUERY_REWRITE_INTEGRITY=stale_tolerated at either database or session-level.  

Materialized views can be created for the nVision queries that you wish to optimise, and no further code change is required because the database will rewrite the SQL.  You can see a typical example of this in my blog about PCT.

Partition Change Tracking is a mechanism the Oracle database uses to 'track freshness to a finer grain than the entire materialized view'.  It can identify which partitions and subpartitions are fresh and can be used for query rewrite, and to refresh just the partitions that are stale or that contain stale sub-partitions.  

Alternatives for Partitioning PS_LEDGER

If you wish to create materialized views on the main ledger table, and rely upon query rewrite, and keep the materialized views up to date with respect to the ledger table, and only use them when they are up to date, then you probably want PCT to help with both rewrite and refresh.

1. Multi-column composite partitioning 

I usually like to range partition PS_LEDGER on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to produce a separate range partition for each accounting period.   Partition pruning works very effectively with the SQL generated by nVision.  It also allows sub-partitioning on another column, usually LEDGER.  This is desirable when a system has multiple actuals ledgers, and especially since 9.2 where the budget data is also stored in PS_LEDGER rather than PS_LEDGER_BUDG.  

However, amongst the documented restrictions of PCT is that partitioned tables must use either range, or list partitioning, or composite partitioning with range or list as the top-level partitioning strategy.  Also, the top-level partition key must consist of only a single column (see Database Data Warehousing Guide -> Advanced Materialized Views -> About Partition Change Tracking)

If I want to use query rewrite to materialized views for queries on LEDGER table then I have a few choices.

  • If I stick with multi-column range partitioning, then I cannot use PCT.  I must either keep the materialized views fresh, or the queries remain on the ledger table.  Any update to any partition in the ledger table will render the entire materialized view stale and prevent query rewrite.  Many customers run a suite of nVision reportbooks overnight.  I could set QUERY_REWRITE_INTEGRITY=stale_tolerated at session-level for the report books processes using a trigger on the process scheduler request table (PSPRCSRQST) - see Setting Oracle Session Parameters for Specific Process Scheduler Processes.  In this case, I would have to take responsibility for refreshing the materialized views prior to running, say, a suite of report processes.  This is effectively the same situation as using summary ledgers, but without code change to the reports.
    • I have created materialized views on summary ledger tables in order to provide compressed copies of the summary ledger.  Again, in this case, the materialized views had to be refreshed after the summary ledger maintenance process.
  • Or, I have to produce a simpler partitioning strategy for the ledger table that is still compatible with PCT.

2. Composite Range-Range Partitioning on FISCAL_YEAR and ACCOUNTING_PERIOD

I could composite partition both the LEDGER table and the materialized views by FISCAL_YEAR and ACCOUNTING_PERIOD, but then I cannot further subpartition by other columns.  This would degrade queries on smaller ledgers that could not be rewritten to dedicated materialized views.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
/

The materialized view will be similarly composite partitioned.  Note that I have created the materialized view for specific fiscal years and for a specific ledger.  I would create materialized views for each combination of ledger and each distinct set of analysis columns that are regularly reported upon.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">CREATE 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
/

Note that I have equality criteria on LEDGER and CURRENCY_CD in the materialized view, but I have not included those columns in the select clause, and so they are not in the view.  Oracle can still rewrite queries to use this materialized view and that specify the same criteria on PS_LEDGER

PCT will determine whether any unpruned partitions or subpartitions are stale and if so prevent query rewrite.  It is documented behaviour that the materialized view refresh will truncate and rebuild the whole top-level partitions, in this case each fiscal year, where the partition or any constituent sub-partitions are stale.  So even if just one subpartition, for one accounting period is stale, the whole fiscal year is refreshed.

3. Composite Range-Range Partitioning on ACCOUNTING_PERIOD and FISCAL_YEAR

I investigated making ACCOUNTING_PERIOD the top-level partitioning key, and sub-partitioning on FISCAL_YEAR.  

  • Partitioning pruning works correctly, so the query performance will be unaffected, 
  • 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, this is the case.  I usually see systems that contain 3 to 6 fiscal years of data.

However, it has a number of problems.

  • 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.
  • It is not possible to interval range sub-partition an object, 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 14 range partitions.

On balance, I don't think I would choose to implement this.

Conclusion

There is no single clear recommendation for partitioning and PCT.  It will depend on the circumstances.

  • If I don't need to introduce materialized views on PS_LEDGER then I would usually stick with the multi-column composite partitioning with the top-level range partition on FISCAL_YEAR and ACCOUNTING_PERIOD.
    • PeopleSoft provides summary ledgers to improve the performance of the ledger queries and compressed materialized views can be built on these.
  • If you only have a single actuals ledger then composite range-range partition on FISCAL_YEAR and ACCOUNTING_PERIOD is attractive.  
    • I do not recommend interval partitioning on FISCAL_YEAR because this affects the scope of the refresh process.  It processes a second top-level range partition.
  • If the budget data is stored in the PS_LEDGER table rather than LEDGER_BUDG then consider building separate materialized views for each value of LEDGER.  
    • If you can manage to build materialized views to support rewrite of most the queries on the smaller ledgers, then the lack of sub-partitioning by LEDGER is not going to be a problem unless the query doesn't get rewritten because the materialized views are stale.  Keeping the materialized views up to date is going to be a bigger challenge.

The power of SQL macros

Here is another example of what I’m sure will just become a plethora of such examples from the community on how the flexibility of SQL macros can solve problems that would normally take a lot of code in the form of DBMS_SQL and/or object types and/or pipelined functions and/or …well, you get the idea Smile

A few days back someone asked me if any of our tools had the facility where the output from a SQL query could be represented as a HTML table in the most simplest HTML possible so that it can be cut-pasted into a HTML editor. Recent versions of SQL*Plus comes pretty close to achieving this with the “set markup html” option – here’s an example of what it will output:

image

But not everyone uses SQL Plus, and you don’t have an enormous amount of control over the HTML generated because its aimed at producing a report style output.

Which got me thinking: “Wouldn’t it be nice to take an query and produce the most basic HTML table output as a utility?”

Inspired by Andrej Pashchenko‘s blog post on using SQL macros to generated CSV output, here’s a similar routine to generate a HTML table for any passed query.

Apologies for posting the code as an image but if you’ve ever tried to post snippets that contain a truck load of HTML tags, then you’ll understand Smile. But I’ll also post this on my github repo for you to grab, and of course you’re free to edit it (in particular for date formats etc) to suit whatever need you have.

image

 

Thus you simply construct any input you like using a WITH clause, and out will pop a nice clean HTML table.

image

Yet another motivation to learn more about SQL macros.

Partition Change Tracking During Materialized View Refresh and Query Rewrite

This article discusses the interplay of Partitioning, Partition Change Tracking and Query Rewrite in relation to Materialized Views.

Contents

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.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
@popledger

I will also create the tree selector tables used as dimension tables in the nVision General Ledger Reports

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">REM 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');

And then I will populate and collect statistics on the ledger with randomised, but skewed, data to simulate 

  • 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. 

Some typical indexes will be built on the ledger table. 

The tree selector tables will be populated with data corresponding to the ledger data:
  • 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. 

Statistics preferences will be defined so that statistics will be collected at all table, partition and subpartition levels on all these tables. There will only be histograms on a few low cardinality columns.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
/

Per complete fiscal year, there are 1,000,000 actuals rows and 100,000 budget rows

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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 2938408

There are about 77K rows per accounting period with just 1000 rows in periods 998 (adjustments), 999 (carry forward)

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">LEDGER     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

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
@@mvcap

The materialized views are populated on creation, but I will explicitly collect statistics on them.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">SELECT 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');

Although I can do a full refresh of the MV, I cannot do a PCT refresh.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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 1

I can use EXPLAIN_MVIEW to check the status of the MV

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">REM 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;

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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 key

At the moment, the materialized views are up to date.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">SELECT 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
/

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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 |
--------------------------------------------------------------------------------------------------------------------------

Now I will add more random data for the financial year 2020, accounting period 7. So there have been changes to just one partition.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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;
/

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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 0

I no longer get Query Rewrite for either fiscal year.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">SELECT 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.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL

) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR)
(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) PCTFREE 10 NOCOMPRESS
,PARTITION ledger_2021 VALUES LESS THAN (2022) PCTFREE 10 NOCOMPRESS)
ENABLE ROW MOVEMENT
NOPARALLEL NOLOGGING
/
@treeselectors
@popledger

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS NOPARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, ledger, 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, ledger, account, chartfield1, fiscal_year, accounting_period
/
@mvpop

I can see the MV has partitions for 2019 and 2020 populated, and they contain fewer rows than the original.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">                                          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 ENABLED BASIC
2 LEDGER_2020 ENABLED BASIC
1456077 4864 299.4

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

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan hash value: 1780139226
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 822 | 76446 | 4883 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 822 | 76446 | 4883 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 822 | 76446 | 4882 (1)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 228 | 4332 | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 3601 | 260K| 4880 (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| | 39970 | 1756K| 4877 (1)| 00:00:01 | 1 | 1 |
|* 10 | TABLE ACCESS FULL | PS_LEDGER | 39970 | 1756K| 4877 (1)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------

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"=2018 AND
"A"."ACCOUNTING_PERIOD">=1 AND "A"."CURRENCY_CD"='GBP')

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan 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 | 2 | 2 |
|* 10 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020 | 52909 | 1705K| 668 (2)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------------------------------

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"=2020 AND
"MV_LEDGER_2020"."ACCOUNTING_PERIOD">=1)

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">@pop2020m7.sql

The materialised view status and staleness on USER_MVIEWS changes to NEEDS_COMPILE when the insert into PS_LEDGER is committed. 

  • 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.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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/2020

The query on 2019 still rewrites because the 2019 partition is fresh

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan 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
)

But we no longer get rewrite on the 2020 partition because it is stale. The query stays on PS_LEDGER.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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')

So now I have to refresh the view. I am going to use 

  • 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 am also going to trace the refresh process so I can see what actually happened. I will give the trace file an identifying suffix to make it easier to find. I can query the trace file name from v$diag_info

I need to collect statistics myself, or they won't be updated.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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

I can see the total number of rows in MV_LEDGER_2020 has gone up from 1455085 to 1528980, reflecting the rows I inserted.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">                                          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

I am just going to pick out the statements from the trace that alter the materialized view. I can see the LEDGER_2020 partition was truncated and then the data for the stale ledger partition is reinserted in direct path mode, so it will have been compressed. Statistics confirm this as I can calculate that the number of rows per block is still around 300.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">
/* 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"

I can use EXPLAIN_MVIEW to check the status of MV_LEDGER_2020. PCT is enabled for refresh and rewrite.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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_LEDGER
I 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. 

However, I have to regenerate the materialized view for the whole fiscal year, when I have only changed one accounting period. Could I organise it to refresh just a single accounting period?

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.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
@popledger

I will similarly create a single materialized view with interval partitioning per fiscal year and populate it for 2019 onwards.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">CREATE 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

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">@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/2020

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE PARTITION SYS_P987

/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE PARTITION SYS_P986

/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ FIRST WHEN ( ( ( ( "P0" >= 2020 ) ) ) AND ( ( ( "P0" < 2021 )
) ) ) THEN INTO "SCOTT"."MV_LEDGER_2020" PARTITION (SYS_P986)("BUSINESS_UNIT", "ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR",
"ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") WHEN ( ( ( ( "P0" >= 2021 ) ) ) AND ( ( ( "P0" < 2022 ) ) ) ) THEN INTO
"SCOTT"."MV_LEDGER_2020" PARTITION (SYS_P987)("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">=2019 AND "PS_LEDGER"."LEDGER"='ACTUALS' AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND ( ( ( ( ( (
"PS_LEDGER"."FISCAL_YEAR" >= 2020 ) ) ) AND ( ( ( "PS_LEDGER"."FISCAL_YEAR" < 2022 ) ) ) ) ) ) GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"

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). 

Interval 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.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
@popledger

I will similarly partition the materialized view

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">CREATE 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

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">@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 STALE

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan 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)

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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)

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">
/* 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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
@popledger

This time I will create one materialized view with two range partitions for two fiscal years

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">CREATE 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

After inserting and committing data for fiscal year 2020, period 7 USER_MVIEW_DETAIL_SUBPARTITION correctly identified the one stale sub-partition, and USER_MVIEW_DETAIL_PARTITION reports that one range subpartition is stale.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">@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 STALE

Query rewrite continues to work on the fresh partitions.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan 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)

PCT correctly identifies stale partition in this query on period 7 only and prevents query rewrite.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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)

The query rewrite is prevented if a stale partition is not pruned. It is all or nothing. The query is not expanded and then rewritten to use materialised view for periods 1 to 6 and then the underlying table for period 7.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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')

Again, the materialized view refresh process truncates and repopulates the whole partition not the sub-partition. So we are still processing a whole fiscal year.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">
/* 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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
@popledger

I will create two materialized views, one for 2019 and one for 2020. I will only range partition the MV on accounting period because each contains only a single fiscal year.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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

USER_MVIEW_DETAIL_RELATIONS reports that PCT does apply to these materialized views. USER_MVIEW_DETAIL_SUBPARTITION correctly identified the one stale sub-partition into which new data was added is stale, but in both materialised views, even though we can see it is not needed by MV_LEDGER_2019.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">@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 STALE

Query on 2019 continues to be rewritten to use MV_LEDGER_2019 even though the MV needs compilation.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan 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)

Queries on periods 1-6 in 2020 also get rewritten

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan hash value: 3016493666
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12328 | 927K| | 653 (2)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 12328 | 927K| 1080K| 653 (2)| 00:00:01 | | |
|* 2 | HASH JOIN | | 12328 | 927K| | 429 (2)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | PS_PSTREESELECT10 | 238 | 4522 | | 2 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 51748 | 2931K| | 427 (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 | | 496K| 13M| | 423 (2)| 00:00:01 | 2 | 7 |
|* 10 | MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020 | 496K| 13M| | 423 (2)| 00:00:01 | 2 | 7 |
------------------------------------------------------------------------------------------------------------------------------

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)

Quite correctly, the query on 2020 period 7 is not rewritten because the underlying partition is stale.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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)

Both MVs are compressed after the initial creation. Note the sizes of the partitions for fiscal year 2020; about 256 blocks, and 284 rows per block

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">                                          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

Let's look at the trace of the refresh processes. Both materialised views were marked as NEEDS_COMPILE, so both were refreshed. However, the trace shows that the refresh has changed from truncate to delete and the insert is not done in direct path mode. The refresh of MV_LEDGER_2019 didn't actually change any data because both refreshes tried to process 2020 because a 2020 subpartition had been changed. No data was deleted, and none was inserted.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">
/* MV_REFRESH (DEL) */ DELETE FROM "SCOTT"."MV_LEDGER_2019" WHERE ( ( ( (2020 <= "FISCAL_YEAR" AND "FISCAL_YEAR" < 2021) )) )

/* MV_REFRESH (INS) */ INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2019" ("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" , 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 ( ( ( (2020 <= "PS_LEDGER"."FISCAL_YEAR" 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"



/* MV_REFRESH (DEL) */ DELETE FROM "SCOTT"."MV_LEDGER_2020" WHERE ( ( ( (2020 <= "FISCAL_YEAR" AND "FISCAL_YEAR" < 2021) )) )

/* MV_REFRESH (INS) */ INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_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" , "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 ( ( ( (2020 <= "PS_LEDGER"."FISCAL_YEAR" 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"

However, the 2020 materialized view has gone from 256 blocks per period to 384 blocks, and from 285 to 189 rows per block because the data is no longer compressed because it was not inserted in direct path mode, although there was still a commit between the delete and insert statements.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">                                          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.4

MV_CAPABILITIES reports PCT is available, and it is. It correctly identified stale partitions that prevent rewrite.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">MVNAME                         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_LEDGER

Mismatching partitioning caused non-atomic refresh to go back to atomic mode and so the data was no longer compressed.  

 

Demonstration 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.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
/

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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
@popledger
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">                                          Sub-                                             Rows
Part Part per
TABLE_NAME Pos PARTITION_NAME Pos SUBPARTITION_NAME NUM_ROWS BLOCKS Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ------------
PS_LEDGER 1 AP_BF 261458 5147 50.8 NONE
1 1 AP_BF_LEDGER_2018 84565 1372 61.6 ENABLED BASIC
1 2 AP_BF_LEDGER_2019 84519 1371 61.6 ENABLED BASIC
1 3 AP_BF_LEDGER_2020 84673 2193 38.6 DISABLED
1 4 AP_BF_LEDGER_2021 7701 211 36.5 DISABLED
2 AP_01 261108 5174 50.5 NONE
2 1 AP_01_LEDGER_2018 84268 1368 61.6 ENABLED BASIC
2 2 AP_01_LEDGER_2019 84233 1366 61.7 ENABLED BASIC
2 3 AP_01_LEDGER_2020 84831 2224 38.1 DISABLED
2 4 AP_01_LEDGER_2021 7776 216 36.0 DISABLED
3 AP_02 261174 5172 50.5 NONE
3 1 AP_02_LEDGER_2018 84372 1369 61.6 ENABLED BASIC
3 2 AP_02_LEDGER_2019 84444 1370 61.6 ENABLED BASIC
3 3 AP_02_LEDGER_2020 84596 2218 38.1 DISABLED
3 4 AP_02_LEDGER_2021 7762 215 36.1 DISABLED
4 AP_03 259982 5149 50.5 NONE
4 1 AP_03_LEDGER_2018 84105 1364 61.7 ENABLED BASIC
4 2 AP_03_LEDGER_2019 83820 1360 61.6 ENABLED BASIC
4 3 AP_03_LEDGER_2020 84284 2210 38.1 DISABLED
4 4 AP_03_LEDGER_2021 7773 215 36.2 DISABLED
5 AP_04 261376 5177 50.5 NONE
5 1 AP_04_LEDGER_2018 84378 1369 61.6 ENABLED BASIC
5 2 AP_04_LEDGER_2019 84649 1374 61.6 ENABLED BASIC
5 3 AP_04_LEDGER_2020 84652 2220 38.1 DISABLED
5 4 AP_04_LEDGER_2021 7697 214 36.0 DISABLED
6 AP_05 261772 5180 50.5 NONE
6 1 AP_05_LEDGER_2018 84984 1378 61.7 ENABLED BASIC
6 2 AP_05_LEDGER_2019 84656 1374 61.6 ENABLED BASIC
6 3 AP_05_LEDGER_2020 84507 2216 38.1 DISABLED
6 4 AP_05_LEDGER_2021 7625 212 36.0 DISABLED
7 AP_06 260581 5165 50.5 NONE
7 1 AP_06_LEDGER_2018 83994 1363 61.6 ENABLED BASIC
7 2 AP_06_LEDGER_2019 84150 1366 61.6 ENABLED BASIC
7 3 AP_06_LEDGER_2020 84729 2222 38.1 DISABLED
7 4 AP_06_LEDGER_2021 7708 214 36.0 DISABLED
8 AP_07 184118 3163 58.2 NONE
8 1 AP_07_LEDGER_2018 84863 1377 61.6 ENABLED BASIC
8 2 AP_07_LEDGER_2019 84155 1366 61.6 ENABLED BASIC
8 3 AP_07_LEDGER_2020 7587 211 36.0 DISABLED
8 4 AP_07_LEDGER_2021 7513 209 35.9 DISABLED
9 AP_08 184619 3173 58.2 NONE
9 1 AP_08_LEDGER_2018 84547 1372 61.6 ENABLED BASIC
9 2 AP_08_LEDGER_2019 84775 1376 61.6 ENABLED BASIC
9 3 AP_08_LEDGER_2020 7662 213 36.0 DISABLED
9 4 AP_08_LEDGER_2021 7635 212 36.0 DISABLED
10 AP_09 184375 3168 58.2 NONE
10 1 AP_09_LEDGER_2018 84407 1370 61.6 ENABLED BASIC
10 2 AP_09_LEDGER_2019 84645 1373 61.6 ENABLED BASIC
10 3 AP_09_LEDGER_2020 7570 210 36.0 DISABLED
10 4 AP_09_LEDGER_2021 7753 215 36.1 DISABLED
11 AP_10 184327 3166 58.2 NONE
11 1 AP_10_LEDGER_2018 84300 1368 61.6 ENABLED BASIC
11 2 AP_10_LEDGER_2019 84738 1374 61.7 ENABLED BASIC
11 3 AP_10_LEDGER_2020 7656 212 36.1 DISABLED
11 4 AP_10_LEDGER_2021 7633 212 36.0 DISABLED
12 AP_11 184489 3167 58.3 NONE
12 1 AP_11_LEDGER_2018 84406 1369 61.7 ENABLED BASIC
12 2 AP_11_LEDGER_2019 84861 1376 61.7 ENABLED BASIC
12 3 AP_11_LEDGER_2020 7700 213 36.2 DISABLED
12 4 AP_11_LEDGER_2021 7522 209 36.0 DISABLED
13 AP_12 184244 3168 58.2 NONE
13 1 AP_12_LEDGER_2018 84611 1373 61.6 ENABLED BASIC
13 2 AP_12_LEDGER_2019 84155 1365 61.7 ENABLED BASIC
13 3 AP_12_LEDGER_2020 7776 216 36.0 DISABLED
13 4 AP_12_LEDGER_2021 7702 214 36.0 DISABLED
14 AP_CF 4800 154 31.2 NONE
14 1 AP_CF_LEDGER_2018 2200 53 41.5 ENABLED BASIC
14 2 AP_CF_LEDGER_2019 2200 53 41.5 ENABLED BASIC
14 3 AP_CF_LEDGER_2020 200 24 8.3 DISABLED
14 4 AP_CF_LEDGER_2021 200 24 8.3 DISABLED
2938423 55323 53.1

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan 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')
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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

USER_MVIEW_DETAIL_SUBPARTITION correctly identified the one stale sub-partition, but USER_MVIEW_DETAIL_PARTITION reports that one range partition is stale

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">@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 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

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Plan 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)

Fiscal year 2020 period 7 doesn't rewrite, because the subpartition is stale.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">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)

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.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">
/* 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. 

On balance, I don't think I would choose to implement this.

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.

Video : Adaptive Cursor Sharing

In today’s video we’ll discuss the Adaptive Cursor Sharing feature, introduced in Oracle 11g Release 1.

This video is based on the following article.

Here are some other things you might want to check out.

The star of todays video is Franck Pachot, who is clearly looking for an exit. </p />
</p></div>

    	  	<div class=