SQL> select regexp_substr('Hello *1 Connor World','\*1 Connor') from dual; REGEXP_SU --------- *1 Connor

Excellent point. I'll pass this on to the team because it might be something we can detect and at least warn about

True. I am too a dinosaur when it comes to breaking habits

For 100s of millions, I'm probably looking at insert-append (a lot depends on whether you can handle the implications of that)

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

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

My pleasure

Using 'compatible' for this case seems to be a big hammer to me.

it doesn’t have to be the only option but having myriad of new features which are never turned on on new installs (or a lot of useless non default init Params) is also pretty ugly when there is a good option for changing defaults. Will the dbca template get those switches?

Glad you liked it!

you're welcome

check out Snapper by Tanel Poder (free) tanelpoder.com/snapper/

Greetings from Perth but on my way to Germany (DOAG)

Careful with unconditional idle timeouts, some connection pools are not good at keeping connections equally busy

Sorry, that got left on the editing floor for brevity. Idle session timeout in profiles.

Glad you enjoyed it

Yes, in SQL

indeed

You're welcome

last resort

Look for "oh_bulkmig" in my github rep under "office hours" github.com/connormcd

oink :-)

PL/SQL was just the simple example - the same principle applies anywhere. But fair point :-)

Cloud changes that equation I think ... because you are literally saving real money

My pleasure!

Glad you liked it

You're welcome!

excellent point!

because you said "update indexes".

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

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

@@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 <=400 );SQL> 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

@DatabaseDude

glad it helped

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

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

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

Glad it was helpful!

You're welcome!

Glad you liked it!

Welcome 😊

Very true!

I'm hoping we de-deprecate it (if that makes sense)