The Pstart=KEY or Pstop=KEY indicate that the exact partition cannot be determined at compile time, but will most likely be found during run time.
Some earlier step in the plan is producing one or more values for the partition key, so that pruning can take place.
Example:
I have a composite partitioned table, with a locally partitioned index:
create table published_documents(
UNIQUE_ID VARCHAR2(160 BYTE) NOT NULL,
REGYEAR NUMBER(18),
DOCUMENT_TYPE VARCHAR2(100 CHAR),
DOCUMENT_NAME VARCHAR2(1000 CHAR),
TOPIC VARCHAR2(30 CHAR),
VALID CHAR(1 BYTE),
VERSION NUMBER(18),
DATA_XML CLOB,
FORMAT VARCHAR2(1000 CHAR),
PERIOD VARCHAR2(1000 CHAR)
)
PARTITION BY LIST (DOCUMENT_TYPE)
SUBPARTITION BY LIST (PERIOD)
...
);
create index pub_docs_idx1 on published_documents
(regyear, document_type, period)
local;
Send the following query to the database:
select document_type, count(*)
from myuser.published_documents
partition(LEGAL)
group by document_type;
The output is as expected:
| DOKUMENTTYPE |
COUNT(*) |
| Affidavit |
7845 |
| Amending Agreement |
29909 |
| Contract |
6647 |
And result in the following execution plan:
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 128 | 195M (1)| 02:07:06 | | |
| 1 | PARTITION LIST SINGLE| | 4 | 128 | 195M (1)| 02:07:06 | KEY | KEY |
| 2 | HASH GROUP BY | | 4 | 128 | 195M (1)| 02:07:06 | | |
| 3 | PARTITION LIST ALL | | 2198M| 65G| 195M (1)| 02:07:03| 1 | 114 |
| 4 | TABLE ACCESS FULL | PUBLISHED_DOCUMENTS | 2198M| 65G| 195M (1)| 02:07:03| KEY | KEY |
-------------------------------------------------------------------------------------------------------------
When we specifiy a named partition, we can see how the optimzer is limiting its search
only to the partition mentioned in the predicate, but it does not yet know how many subpartitions to scan. Since there is no mention of a date range to match the PERIOD column in the predicate, all 114 subpartitions must be scanned.
Note that the text "TABLE ACCESS FULL" in step 4 can be somewhat confusing: we are
only talking about a full table access of the partition called "LEGAL", not the the entire table.
In my experience, specifying the partition name directly is rather unusual, and mostely done by DBAs.
Let's try it with a predicate that is more likely to be sent to the oracle server by a user or a batch program:
select dokumenttype, period, count(*)
from myuser.published_documents
where periode = '2018-01'
group by dokumenttype, period;
The output is as expected:
| DOKUMENTTYPE |
PERIODE |
COUNT(*) |
| Affidavit |
2018-01 |
7845 |
| Amending Agreement |
2018-01 |
29909 |
| Contract |
2018-01 |
6647 |
| Payroll |
2018-01 |
7824 |
| HA_related |
2018-01 |
36608 |
| Banking |
2018-01 |
14167 |
| IT |
2018-01 |
4094 |
The rows in the output above belongs to many different partitions, but they are all from the period 2018-01.
The explain plan for this query would be:
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 1950 | 6589K (1)| 00:04:18 | | |
| 1 | PARTITION LIST ALL | | 50 | 1950 | 6589K (1)| 00:04:18 | 1 | 11 |
| 2 | HASH GROUP BY | | 50 | 1950 | 6589K (1)| 00:04:18 | | |
| 3 | PARTITION LIST SINGLE| | 8122K| 302M| 6589K (1)| 00:04:18 | | |
|* 4 | INDEX SKIP SCAN | PUB_DOCS_IDX1 | 8122K| 302M| 6589K (1)| 00:04:18 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PERIOD"='2018-01')
filter("PERIOD"='2018-01')
Here, too, we see that the optimizer
first selects all 11 partitions, but then use the partitioned index PUB_DOCS_IDX1 to find the rows that would match the string '2018-01'.
The optimizer does not yet know how many index subpartitions to scan; this will be determined during run-time.
Thanks to
Jim Brull
Justin Cave
