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.

PostgreSQL: How to set number of parallel workers in a session

SET max_parallel_workers_per_gather=num;

Default is num=2, which will give you 3 processes in total (1 master + 2 workers). If set to 3, you will have 5 processes in total. This parameter can be set on a per session basis.

Remember the global cap on parallelism that is represented by the parameter max_parallel_workers. Default is 8. max_parallel_workers_per_gather cannot exceed max_parallel_workers, which can only be changed in the configuration file and requires a full restart of the postgres server.

The documentation can be found here

See also "When can parallel query be used?" in the documentation

This post is based on the PostgreSQL 11 server.

How to find the number of huge pages to set on a PostgreSQL server

Thanks to Ibrar Ahmed for posting the article "Tune Linux Kernel Parameters For PostgreSQL Optimization"

Log on to the server as the os user that owns the PostgreSQL server. On my server, this user is called "postgres":

su - postgres

Create a file called find_hp.sh. Insert the following:

#!/bin/bash
pid=`head -1 $PGDATA/postmaster.pid`
echo "Pid:            $pid"
peak=`grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'`
echo "VmPeak:            $peak kB"
hps=`grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'`
echo "Hugepagesize:   $hps kB"
hp=$((peak/hps))
echo Set Huge Pages:     $hp

Make sure the environment variable $PGDATA is set. Give the script execution rights:

chmod 755 find_hp.sh

Execute it, and it will tell you how many huge pages you need:

 ./find_hp.sh
Pid:             128678
VmPeak:          68986484 kB
Hugepagesize:    2048 kB
Set Huge Pages:  33684

I can now proceed to allow for 33684 huge pages on my system with

sysctl -w vm.nr_hugepages=33684

How to loop through tables in a schema in PostgreSQL and show estimated number of rows

To loop through all tables in a schema called "myschema", in a database called "proddb01" you can put the following in a script called "find_rows.sh":

for a in `echo "\t \dt+ myschema.*" | psql proddb01 | awk -F '[|]' '{ print $2 }'`; do
 echo "SELECT relname, reltuples::BIGINT AS estimate FROM pg_class WHERE relname='$a';" | psql proddb01
done

chmod 755 find_rows.sh
./find_rows.sh

Example output:

        relname           | estimate
----------------------------+----------
 table1                   |        0
(1 row)

           relname            | estimate
------------------------------+----------
 table2                       | 65525596
(1 row)

        relname        | estimate
-----------------------+-----------
 table3                | 153588080
(1 row)

      relname       | estimate
--------------------+----------
 table4             |        1
(1 row)

How to turn off output generated by psql

I guess this could be viewed as the equivalent to oracles "set" statements in sqlplus, for example "set heading off verify off feedback off echo off":

proddb01=# \dn
           List of schemas
           Name           |  Owner
--------------------------+----------
 sales                    | postgres
 hr                       | postgres
 manufacturing            | postgres
 public                   | postgres
 (4 rows)

Turn off unneccessary output like this:

proddb01=# \t
Tuples only is on.

Try again:

proddb01=# \dn
 sales                    | postgres
 hr                       | postgres
 manufacturing            | postgres
 public                   | postgres

How to extract estimated number of rows from a PostgreSQL table

prod=# SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE relname='yourtable';
 estimate
----------
 42223028
(1 row)

To include the name of the tables in case you want to check several tables in one go:

prod=# select relname,reltuples::BIGINT AS estimate FROM pg_class WHERE relname in ('mytable','yourtable');

         relname         | relowner | estimate
-------------------------+----------+----------
            mytable      |    16724 |        0
          yourtable      |    16724 |        0

Unlike Oracle, PostgreSQL is converting all strings to lowercase, so you should not use WHERE relname='YOURTABLE', but stick to lower case.