What is the difference between dynamic and static partition pruning

From the documentation:

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning.

* Static pruning occurs at compile-time, with the information about the partitions accessed being known beforehand.
* Dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand.

A sample scenario for static pruning is a SQL statement containing a WHERE condition with a constant literal on the partition key column:

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = 1001;

There are some exceptions to this rule, listed here, for example if the partition pruning occurs using the results of a sub-query.

An exampel of Oracle using dynamic pruning is when operators or functions are used in the predicat (WHERE-condition). Also, statements that use bind variables against partition columns result in dynamic pruning:

var b1 number;
exec :B1 := 1001;

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = :B1;

How to view a specific property for a databasen using dgmgrl

DGMGRL> show database 'PROD_STB' 'DbFileNameConvert';

How to set timing in a posgreSQL session

Open session in psql:

psql mydb
psql (11.11)
Type "help" for help.

Toggle timing on/off like this:

mydb=# \timing
Timing is on.
mydb=#  \timing
Timing is off.

How to add a different looking index to your interim table during online redefinition

The procedure REGISTER_DEPENDENT_OBJECT in the DBMS_REDEFINITION package lets you add a dependent object to your interim table during online redefinition, and then "plug it into" your redefintion process.

In my case, I was redefining an incorrectly partitioned table. The interim table was also partitioned, but this time correctly. During the phase where I call the COPY_TABLE_DEPENDENTS procedure to transfer the existing dependant objects (triggers, indexes, constraints etc) from the original table to the interim table, it failed with the message

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2761

This was because I was using the directive dbms_redefinition.cons_orig_params in my code:

BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'SALES',
orig_table=>'DAILYSALES',
int_table=>'DAILYSALES_INTERIM',
copy_indexes=>dbms_redefinition.cons_orig_params,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
END;
/

As such, the redefinition process attempted to transfer the partitioned index from the original table which had a different partitioning scheme, to the new table who was partitioned differently and had fewer partitions.

As a workaround, I recreated the index directly on the interim table:

CREATE INDEX SALES.SEARCHIX1_INTERIM ON SALES.DAILYSALES_INTERIM
(COL1, COL2 DESC)
TABLESPACE DATA2
LOCAL
PARALLEL ( DEGREE 5 INSTANCES 1 );

Then, "plug it into" the redefinition procedure:

exec DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('SALES','DAILYSALES','DAILYSALES_INTERIM',2,'SALES','SEARCHIX1','SEARCHIX1_INTERIM');

Finally, change the directive in your COPY_TABLE_DEPENDENTS procedure from

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

And run the COPY_TABLE_DEPENDENTS procedure again.
When done, finish the redefinition by calling the FINISH_REDEF_TABLE procedure, and you'll see that the interim index on the interim tables has been nicely transfered during the switch.

The Oracle 18c documentation can be found here
Another good source is HOW TO USE DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (Doc ID 1304838.1 from Oracle Support)

How to find out if a parameter in PostgreSQL is dynamic or not

Oracle has both dynamic and static parameters, meaning that the former can be set in a session and while the database is up, while the latter cannot.

To find out if a parameter in PosgreSQL is dynamic, you can use the query

select context from pg_settings where name = 'enable_parallel_hash';

Result:

 context
---------
 user
(1 row)

"user" means that the parameter can be set in a user's session. Thanks to Daniel Westerman for writing the useful article where I found this useful information.