jump to navigation

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane) October 9, 2018

Posted by Richard Foote in Global Indexes, Index Internals, Local Indexes, Oracle Indexes, Partitioned Indexes, Partitioning, ROWID.
add a comment

aladdin sane

In Part I of this series, I highlighted how a Non-Partitioned Global Index on a Partitioned Table is able to effectively perform “Partition Pruning” by reading only the associated index entries to access just the table blocks of interest from relevant table partitions when the table partitioned keys are specified in an SQL Predicate.

Understanding how Oracle achieves this is key (pun fully intended) in understanding the associated advantages of Global Indexes.

Back in time before Oracle introduced Partitioning (pre-Oracle 8 days), the 6 byte ROWID was safely made up of the following components:

  • File Number
  • Block Number
  • Row Number

to uniquely determine the location of any given row.

If we look at a partial block dump of a leaf block from the index based on the Non-Partitioned table:

Leaf block dump
===============
header address 1881436260=0x70247064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 29387269=0x1c06a05
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 1d 68 00 18
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 24 c8 00 c1
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 3a 1c 00 96
…

We notice that the ROWID for each index entry is the standard 6 bytes in size.

With the introduction of Oracle 8 and the Partitioning Option, the File Number was no longer unique, with this number of files (approx. 1K) now possible not for the database at large, but for each Tablespace (thus making Oracle able to cater for very large databases with there now being the option for so many more data files in a database).

This means for a Partitioned Table in which each table partition (or sub-partition) could potentially reside in different tablespaces, the associated file number (RELATIVE_FNO) within the ROWID is no longer unique. Therefore, for Global Indexes in which index entries span across all table partitions, the ROWID is extended to include the 4 byte Data Object Id. A specific object can only live in one tablespace and if Oracle knows the tablespace, Oracle can determine which specific file number the ROWID is referencing. So an extended ROWID is consists of:

  • Data Object Id
  • File Number
  • Block Number
  • Row Number

If we look at a partial block dump of a leaf block from the index based on the Partitioned table:

Leaf block dump
===============
header address 1881436260=0x70247064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 399
kdxcofbo 834=0x342
kdxcofeo 1652=0x674
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 29385221=0x1c06205
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8020] flag: ——-, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 01 57 4a 01 c0 5e cf 00 cc
row#1[8004] flag: ——-, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 01 57 4a 01 c0 5f 74 00 e7
row#2[7988] flag: ——-, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 01 57 4b 01 c0 5c 32 00 c9
…

We notice that the ROWID for each index entry is now the extended 10 bytes in size as it includes the Data Object Id.

Storing the Data Object Id as part of the ROWID has various advantages, such as being able to asynchronously maintain index entries following table partition operations such as dropping a table partition (as discussed previously here).

However the key advantage of storing the Data Object Id as part of the ROWID is that this enables Oracle when using Global Indexes to automatically perform “Partition Pruning” (the ability to access only those partitions that can possibly contain data of interest), when the table partition key is specified in an SQL predicate.

When the table partition key is specified in an SQL predicate, Oracle can determine which table partitions can only contain such data and then only access the table blocks via the index ROWIDs that have corresponding Data Object Ids of interest. This is how in the example in Part I Oracle was able to only access just the table block that belongs in the table partition of interest, effectively performing predicate filtering at the index level, without unnecessarily having to access the table blocks at all from partitions that are not of interest.

This enables Global Indexes to have almost Local Index like performance in scenarios where the table partition key is specified in SQL predicates. Local Indexes do have the advantage of potentially having a reduced BLEVEL in that if you have say 100 table partitions, each Local Index would only have to be approx. 1/100 the size of the single, Non-Partitioned Index (although Global Indexes can in turn be partitioned if individual index size were problematic, even if the table were not partitioned). Additionally, Local Indexes don’t have to concern themselves with having to read through unnecessary index entries if index entries associated with a specific subset of table partitions were only of interest.

However, Global Indexes have a key performance advantage over Local Indexes which I’ll discussed in Part III.

 

Advertisements

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie) October 4, 2018

Posted by Richard Foote in Global Indexes, Local Indexes, Non-Partitioned Indexes, Oracle Indexes, Partitioned Indexes, Partitioning.
3 comments

jean genie

When it comes to indexing a partitioned table, many automatically opt for Local Indexes, as it’s often assumed they’re simply easier to manage and more efficient than a corresponding Global Index.

Having smaller index structures that are aligned to how the table is partitioned certainly has various advantages. The focus in this little series is on the “more efficient” claim.

A key point that many miss is that a Non-Partitioned Index on a Non-Partitioned table is not exactly the same beast as a Non-Partitioned Index on a Partitioned Table. The purpose of this initial post is to illustrate this difference.

Let’s begin by creating a Non-Partitioned table that has a number of years worth of data:

SQL> CREATE TABLE big_ziggy (id number, album_id number, country_id number, release_date date, total_sales number);

Table created.

SQL> INSERT INTO big_ziggy
     SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
            mod(rownum,200000)+1
     FROM dual CONNECT BY LEVEL  2000000;

2000000 rows created.

SQL> commit;
      
Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY');

PL/SQL procedure successfully completed.

So we have a 2M row table with about 8 years worth of data (based on the RELEASE_DATE column) and a TOTAL_SALES column that has some 200,000 distinct columns throughout this period.

Let’s next create a standard Non-Partitioned index based on the TOTAL_SALES column:

SQL> create index big_ziggy_total_sales_i on big_ziggy(total_sales);

Index created.

If we now run a query to access the 10 rows with a value equal to 42:

SQL> SELECT * FROM big_ziggy WHERE total_sales = 42;

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
   1400041         42         42 24-AUG-18          42
    400041         42         42 28-JAN-12          42
   1000041         42         42 20-JUL-17          42
   1800041         42         42 28-JAN-12          42
    600041         42         42 15-JUN-16          42
    800041         42         42 03-MAR-13          42
   1200041         42         42 07-APR-14          42
   1600041         42         42 12-MAY-15          42
    200041         42         42 12-MAY-15          42
        41         42         42 24-AUG-18          42

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1252095634

---------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name                    | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                         |   10 |   250 |      13 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BIG_ZIGGY               |   10 |   250 |      13 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BIG_ZIGGY_TOTAL_SALES_I |   10 |       |       3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  14 consistent gets
   0 physical reads
   0 redo size
1184 bytes sent via SQL*Net to client
 624 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  10 rows processed

We notice we need 14 consistent gets to access these 10 rows, 4 gets for index block accesses and 10 gets to access the relevant rows from the table blocks (as we have a terrible clustering due to the relevant data being distributed throughout the table).

If we run a query where we’re only interested in accessing data only within a specific year:

SQL> SELECT * FROM big_ziggy WHERE total_sales = 42 and release_date
between '01-JAN-2017' and '31-JUL-2017';

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
   1000041         42         42 20-JUL-17          42

Execution Plan
----------------------------------------------------------
Plan hash value: 1252095634

---------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name                    | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                         |    1 |    25 |      13 (0) | 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BIG_ZIGGY               |    1 |    25 |      13 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BIG_ZIGGY_TOTAL_SALES_I |   10 |       |       3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RELEASE_DATE">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RELEASE_DATE"<=TO_DATE(' 2017-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
 14 consistent gets
  0 physical reads
  0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

Even though we now only return the one row,  notice we still have to perform the same 14 consistent gets. That’s because the RELEASE_DATE column is NOT part of the index, so we still need to fetch all 10 matching rows with TOTAL_SALES=42 and then filter out those that don’t have a RELEASE_DATE of interest. The note above in the predicate information shows we now have this additional filtering taking place.

Let’s run the same queries on a table with identical data, but this time on a table that is partitioned based on the RELEASE_DATE column, with a partition for each years worth of data:

SQL> CREATE TABLE big_bowie(id number, album_id number, country_id number, release_date date, total_sales number)
2 PARTITION BY RANGE (release_date)
3 (PARTITION ALBUMS_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
4 PARTITION ALBUMS_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
5 PARTITION ALBUMS_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
6 PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
7 PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
8 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
9 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
10 PARTITION ALBUMS_2018 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie
     SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
            mod(rownum,200000)+1
     FROM dual CONNECT BY LEVEL  2000000;

2000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');

PL/SQL procedure successfully completed.

Again, we create a standard, Non-Partitioned Index:

SQL> create index big_bowie_total_sales_i on big_bowie(total_sales);

Index created.

If we now run the equivalent of the first query:

SQL> SELECT * FROM big_bowie WHERE total_sales = 42;

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
    400041         42         42 28-JAN-12          42
   1800041         42         42 28-JAN-12          42
    800041         42         42 03-MAR-13          42
   1200041         42         42 07-APR-14          42
   1600041         42         42 12-MAY-15          42
    200041         42         42 12-MAY-15          42
    600041         42         42 15-JUN-16          42
   1000041         42         42 20-JUL-17          42
        41         42         42 24-AUG-18          42
   1400041         42         42 24-AUG-18          42

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1761527485

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                    | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |                         |   10 |   250 |      13 (0) | 00:00:01 |        |       |
|  1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE               |   10 |   250 |      13 (0) | 00:00:01 |  ROWID | ROWID |
|* 2 | INDEX RANGE SCAN                           | BIG_BOWIE_TOTAL_SALES_I |   10 |       |       3 (0) | 00:00:01 |        |       |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  14 consistent gets
   0 physical reads
   0 redo size
1184 bytes sent via SQL*Net to client
 624 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  10 rows processed

We get the exact same performance, with the same 14 consistent gets necessary to access the 10 rows of interest.

If we now run the equivalent of the second query:

SQL> SELECT * FROM big_bowie WHERE total_sales = 42 and release_date between '01-JAN-2017' and '31-JUL-2017';

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
   1000041         42         42 20-JUL-17          42

Execution Plan
----------------------------------------------------------
Plan hash value: 1081241859

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                    | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |                         |    1 |    25 |      13 (0) | 00:00:01 |        |       |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE               |    1 |    25 |      13 (0) | 00:00:01 |      7 |     7 |
|* 2 | INDEX RANGE SCAN                           | BIG_BOWIE_TOTAL_SALES_I |   10 |       |       3 (0) | 00:00:01 |        |       |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RELEASE_DATE"=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  0 physical reads
  0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

We notice a key difference. Even though it’s equivalent to the same index as in the previous Non-Partitioned table and even though the index only contains just the TOTAL_SALES column, the number of consistent gets has dropped from 14 to just 5 consistent gets.

In this example, Oracle has clearly not had to fetch the rows from the table that do not match the RELEASE_DATE of interest. Even though the predicate information is listing the requirement for filtering to take place, this filtering has clearly been performed within the index, without having to actually fetch any of the rows that aren’t of interest.

The index is able to only access the row(s) of interest from the Partitioned Table…

This is the little “hidden efficiency” of Global Indexes on Partitioned Tables, which is what we effectively have here.

In Part II, I’ll discuss how Oracle does this additional filtering within the index and why understanding this is important in deciding which type of index to deploy, as from a “performance” perspective, Global Indexes are often the preferred option.

12c Partial Indexes For Partitioned Tables Part II (Vanishing Act) July 12, 2013

Posted by Richard Foote in 12c, Local Indexes, Oracle Indexes, Partial Indexes, Partitioning.
3 comments

In Partial Indexes Part I, we looked at how it was possible with the 12c database  to create a Partial Index based on data from only selected table partitions. The resultant Partial Index can be either a Global or Local Index.

In Part I, we only really looked at Global Indexes, so let’s look at a Local Index example. Using the same Partitioned Table example as before:

SQL> create table pink_floyd (id number, status varchar2(6), name varchar2(30))
indexing off
partition by range (id)
(partition pf1 values less than (1000001),
partition pf2 values less than (2000001) indexing off,
partition pf3 values less than (maxvalue) indexing on);
Table created.

This time, we’ll create a Local Partial Index:

SQL> create index pink_floyd_status_i on pink_floyd(status)
local indexing partial;

Index created.

If we look at the details of the resultant Local Index:

SQL> select index_name, partition_name, num_rows, status, leaf_blocks from dba_ind_partitions where index_name = 'PINK_FLOYD_STATUS_I';

INDEX_NAME           PARTITION_NAME    NUM_ROWS STATUS   LEAF_BLOCKS
-------------------- --------------- ---------- -------- -----------
PINK_FLOYD_STATUS_I  PK1                      0 UNUSABLE           0
PINK_FLOYD_STATUS_I  PK2                      0 UNUSABLE           0
PINK_FLOYD_STATUS_I  PK3                1000000 USABLE          2513

We can see that for those table partitions with INDEXING OFF, the associated Local Indexes have simply been made UNUSABLE. Since Unusable Indexes consume no storage, there is effectively no corresponding index segment for these index partitions.

For the one and only PK3 table partition with INDEXING ON, its associated Local Index has been created as normal. So the end result is very similar to the previous Global Index example, only those rows from the table partitions with the INDEXING ON property are effectively being indexed.

There is one scenario in which the creation of a Partial Index is not permitted, that is in the creation of a Unique Index or a Non-Unique Index to police a Primary Key or Unique Key constraint. Some examples:

SQL> create unique index pink_floyd_id_i on pink_floyd(id)
indexing partial;
create unique index pink_floyd_id_i on pink_floyd(id) indexing partial
*
ERROR at line 1:

ORA-14226: unique index may not be PARTIAL

SQL> alter table pink_floyd add constraint pink_floyd_pk primary key(id)
using index (create index pink_floyd_id_i on pink_floyd(id) indexing partial);
alter table pink_floyd add constraint pink_floyd_pk primary key(id) using index
(create index pink_floyd_id_i on pink_floyd(id) indexing partial)
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

SQL> create index pink_floyd_id_i on pink_floyd(id) indexing partial;

Index created.

SQL> alter table pink_floyd add primary key(id);
alter table pink_floyd add primary key(id)
*
ERROR at line 1:

ORA-01408: such column list already indexed

It clearly doesn’t make sense to create a Partial Unique Index or on a Non-Unique Index policing a PK or Unique Key constraint as it would be impossible to use such an index to guarantee the required unique property. With missing index entries associated with non-indexed partitions, how can Oracle determine whether a value from new row already exists or not ? It can’t and hence Oracle doesn’t permit the creation of such a Partial Index.

Partial Indexes can potentially be extremely useful in reducing unnecessary storage requirements, reducing index maintenance overheads and in improving performance by reducing index block accesses.

But they’re only useful (possible) with Partitioned Tables.

I’ll next look at another cool index improvement introduced with the Oracle 12c Database that’s associated with Partitioning, Asynchronous Global Index Maintenance …

Local Index Issue With Partitioned PK and Unique Key Constraints December 20, 2007

Posted by Richard Foote in Constraints, Index Access Path, Local Indexes, Oracle Indexes, Partitioning, Performance Tuning, Unique Indexes.
12 comments

Nuno Souto (Noons) also asked a really interesting question on my Differences between Unique and Non-Unique Indexes blog entry (comment 4) that I thought it worthy of a separate blog entry to do the answer justice. The question was:

“Isn’t it still the case that unique indexes cannot be locally partitioned unless the partition key is part of the index key? Not sure if 11g removes this. If still so, that would weigh heavily in favour of non-unique indexing for PK on a table potentially requiring local index partitions.”

Simplistically, the answer to the first part is Yes it is still the case, even in 11g and the answer to the second part is No, it wouldn’t weigh heavily in favour of non-unique indexing for PK on a table requiring local index partitions. It wouldn’t actually be a consideration at all.

Let me explain why.

Firstly, there is a really really good reason why Oracle doesn’t allow us to create a Unique Index in which the Partition key is not part of a Local Index. It’s called protecting us from ourselves !!

Let’s start by mentioning constraints again.

Remember, the main reason we have indexes policing PK and Unique constraints is so that Oracle can very quickly and efficiently determine whether or not a new value already exists. Do a quick index look-up, is the value there, yes or no, allow the insert (or update), yes or no.

Just imagine for one moment what would happen if Oracle actually allowed us to create a Unique Local index in which the index didn’t include the partitioned column(s).

Lets say a table is Range Partitioned on column ‘A’ and we try and create a Unique Local index on just column ‘B’. Let’s assume we have (say) 500 table partitions meaning we must therefore have 500 local index partitions as well. When we insert a new value for our unique index for value B, it will attempt to do so in the corresponding local index partition as governed by the value A for the new row. However Oracle can’t just check this one index partition for uniqueness to ensure value of column B doesn’t already exist, Oracle would need to check all 500 index partitions because it would be possible for our new value of column B to potentially have previously been inserted into any of the other 499 partitions !!

Each and every insert into our partitioned table (partitioned by column A) therefore would require Oracle to check all (say)500 index partitions each and every time to check for duplicates of column B. Again, it’s important to understand that any given value of column B could potentially be in any of the 500 partitions, IF Oracle allowed us to create a Local Partitioned Index just on column B.

Checking all 500 index partitions looking for a specific value of column B would obviously be impractical, inefficient and totally un-scalable. Therefore Oracle doesn’t allow us to do this. It doesn’t allow us to create a Local index in which the indexed columns does’t include the partitioning columns as well.

This is actually a good thing.

If you want to create a Unique index in a partitioned table, you MUST either add all the partitioned columns and make it part of the LOCAL unique index (so that way each and every insert would only have to check the one local partition as this value is known now it’s part of the index) or you must create it as a GLOBAL index (in which again, Oracle only has to check the one index structure).

It actually makes a lot of sense to do this.

Moving onto the second part of the question. Let’s just use a Local Non-Unique index to police our PK constraints then.

Fortunately this isn’t allowed either for exactly the same reasons. You can’t create a Local Non-unique index to police a PK (or Unique) constraint if the Constraint does not also include the partitioned columns. Otherwise again, Oracle would need to check each and every index partition to determine whether the constraint has been violated or not.

If you attempt to use an existing Local Non-Unique index to police a PK or Unique constraint that does not contain the partitioned columns, you will get an error saying it can’t create the (by default Global index) because the useless Local Non-Unique index (from a policing the constraint point of view) already exists.

Again if you want to create a Non-Unique index to police a PK or Unique constraint you must either ensure the constraint includes all the partitioned columns in which case it can be Local or you must use a Global Non-Unique index.

In other words, the rules apply equally to both Unique and Non-Unique indexes.

So it’s not really a case of Oracle not allowing one to create a Local Unique index without including the partitioned columns (although that’s of course true) but really a case of Oracle not allowing a PK or Unique *constraint*  to be policed via *any* Local index (whether Unique or Non-Unique), unless the partitioned columns are also included.

Little demo to illustrate: Local Index Issue With Partitioned PK and Unique Key Constraints