Primary Key options for Partitioned Tables

[@ricardooberdanpereira793]

How one person can know so much about? Thanks again Connor.

[@fransc2086]

Great explanation! Thanks!

[@DatabaseDude]

Glad it was helpful!

[@dhirajtagadapallewar8475]

Very nicely explained. Please provide commands to try in test environment

[@marcinbadtke]

Thank you. Fantastic idea.

[@DatabaseDude]

Glad you liked it!

[@ErickTruter]

Great video! Partitions is great but I find often people don't understand the costs involved in using them and often don't use them correctly causing all partition scans.

[@DatabaseDude]

Very true!

[@zhekappp]

There is another possibly better approach besides creating additional table which requires some extra maintenance.
If the ID column contents will be always inserted like number YYYYDDDnnnnnnnnnnnnnnnnnnnnnnnnnn
where YYYYDDD is date for pr_date and nnnn part is just regular sequence we can partition the table based on ID column instead and enable local PK.
This will require application logic to adjust queries to use 'parametrized view' which will transform pr_date to the value looking same way as PK for the where condition for partition pruning when fetching not by PK.

[@DatabaseDude]

True, but the data modellers of the world will be up an arms about embedding information within a column :-)

[@zhekappp]

​@@DatabaseDude well, denormalization by propagating the date value to all the child tables seems worse to me.

[@ilpaolotopleggend6663]

Thanks !super video super issue super explanation

[@DatabaseDude]

Welcome 😊

[@ppaolucc]

Hi Connor, maybe I'm a bit wrong, however: if we would create a global partitioned index (I've seen here a creation of a Global non-partitioned one) we would be able to do partition pruning, even though an maintenance action on partitions (MOVE, DROP, SPLIT, TRUNCATE) would affect the validity of the entire index as well, correct? Thank you

[@DatabaseDude]

With older versions yes - no we have the ability to mark "parts" of the index not usable

[@gleytonr]

Thanks so much

[@DatabaseDude]

You're welcome!

[@Xavier-jf3zf]

And what about this approach: partition the table by range(pk), and use zone maps (for pruning by par_date). And optionally subpartition by par_date ?

[@DatabaseDude]

defintely an option, but zone maps are limited to engineered systems

[@steveandreassend8620]

Surely there is a case to make option 3 native to the product so it is all transparent? It seems to be the only elegant solution for this problem.

[@steveandreassend8620]

ALTER TABLE x TRACK PRIMARY KEY PARTITION; and do some sort of background predicate appending like RLS / VPD does.

[@DatabaseDude]

I certainly like the concept of that being a declarative part of the database

[@vikram4755]

Wow !

[@AnujSingh-vo7pf]

Hi Connor
08:15:40 SQL> select ORPHANED_ENTRIES ,status from dba_indexes where index_name='T_PAR_PK';
ORP STATUS
--- --------
NO VALID
Elapsed: 00:00:00.00
08:14:09 SQL> alter table t_par drop partition p3 update indexes;
Table altered.
Elapsed: 00:00:19.57
08:15:40 SQL> select ORPHANED_ENTRIES ,status from dba_indexes where index_name='T_PAR_PK';
ORP STATUS
--- --------
NO VALID
Elapsed: 00:00:00.00
Why aren't there any ORPHANED_ENTRIES in the index ?

[@DatabaseDude]

because you said "update indexes".

[@AnujSingh-vo7pf]

@@DatabaseDude please check video again
at 5:46 alter table t_par drop partition p3 update indexes;
at 5:59 YES

[@QueryTuner]

@@AnujSingh-vo7pf Did you populate the table partitions with some data ? If there is no data in them, then there will also no orphaned entries in the index.

[@AnujSingh-vo7pf]

@@QueryTuner
create table t_par
partition by range (par_date)
interval ( numtodsinterval(14,'DAY'))
(
partition p1 values less than ( date '2024-05-01' ),
partition p2 values less than ( date '2024-05-15' ),
partition p3 values less than ( date '2024-05-31' ),
partition p4 values less than ( date '2024-06-15' ),
partition p5 values less than ( date '2024-06-30' ),
partition p6 values less than ( date '2024-07-15' ),
partition p7 values less than ( date '2024-07-31' )
) as
select rownum pk,date '2024-04-20' +trunc(rownum/370000) par_date,
d.*
from dba_objects d,
(select 1 from dual
connect by level exec dbms_stats.gather_table_stats('','T_PAR');
SQL> exec dbms_stats.gather_table_stats('','T_PAR');
PL/SQL procedure successfully completed.
SQL> select num_rows from dba_tables where table_name='T_PAR' ;
NUM_ROWS
----------
28455200
SQL> select count(*) from T_PAR ;
COUNT(*)
----------
28455200
SQL>
set linesize 500 pagesize 300
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a80
SELECT table_name, partition_name, num_rows,high_value FROM dba_tab_partitions WHERE table_name='T_PAR'
ORDER BY 1,2;
SQL> create unique index t_par_pk on t_par(pk) parallel 8;
Index created.
SQL> SQL> alter index t_par_pk noparallel ;
Index altered.
SQL> alter table t_par add constraint t_par_pk primary key ( pk ) using index ;
Table altered.
SQL> select partitioned from dba_indexes where index_name='T_PAR_PK';
PAR
---
NO

[@AnujSingh-vo7pf]

@DatabaseDude