Published 27 Oct, 2022

Java - How to use different tablespaces for tables and indexes with liquibase in Java

Category Java
Modified : Nov 28, 2022
69

I'm using liquibase (3.6.3) in Spring Boot (2.1.8) java (1.8) application for managing database (Oracle 12.2) schema changes. My liquibase changesets does not contain 'tablespace' params and all data (including indexes) is creating in default tablespace 'USERS'. Now I need to relocate all existing indexes to another tablespace 'INDX' and create all future indexes not in default 'USERS' tablespace, but in new 'INDX' tablespace.

So, my questions:

  1. How can I migrate old indexes from 'USERS' tablespace to 'INDX' tablespace?

  2. How can I create new indexes in 'INDX' tablespace? I know 'primaryKeyTablespace' param for column/constraint element and 'tablespace' param for createIndex element, is there any other solutions? Possibly I can specify 'INDX' tablespace for all indexes centralized?

Thank you!

Answers

There are 2 suggested solutions here and each one has been listed below with a detailed description. The following topics have been covered briefly such as Java, Indexing, Oracle, Liquibase, Tablespace. These have been categorized in sections for a clear and precise explanation.

47

If you want to centralize the definition of the tablespace, use a property at the start of your change log.

<property name="index_tablespace" value="INDEX" dbms="oracle"/>
<property name="index_tablespace" value="" dbms="postgresql"/>

You can also use another property to define the tablespace for tables.

Typically tablespaces aren't used in Postgres - at least not in the way you need them in Oracle, so you can just leave that empty.

Then use that property in the the createIndex change:

<createIndex indexName="idx_firstname" tableName="person" tablespace="$"/>

To modify an existing index to use a different tablespace, you will have to use database specific SQL (see pmdba's answer). You have to make that dependent on the dbms:

<changeSet id="fix-index-tablespace" author="arthur" dbms="oracle">
  <sql> 
    alter index ...
  </sql>
</changeSet>

1

The answer to your first question is to rebuild the indexes as follows:

alter index [index_name] rebuild tablespace [tablespace_name];

This will lock your table for the amount of time it takes to complete the rebuild. If you need to keep the table available for updates, use the "online" command:

alter index [index_name] rebuild tablespace [tablespace_name] online;

If there's a lot of data in the index, you can add the "parallel" clause to speed things up. In general don't set the degree of parallelism higher than the number of CPU cores on your system:

alter index [index_name] rebuild tablespace [tablespace_name] online parallel [x];

For your second question on default tablespaces, you may be able to handle this a different way depending on your version of Oracle. Oracle 19c has a new feature that allows the database to index your tables automatically (i.e. you don't have to do anything!), which you can read about here: https://blogs.oracle.com/oraclemagazine/autonomous-indexing

If you are using an older version of Oracle, then you will be limited to whatever configuration options are available in liquibase.