如何在Java中使用Liquibase对表和索引使用不同的表空间 [英] How to use different tablespaces for tables and indexes with liquibase in Java

问题描述

我正在Spring Boot(2.1.8)Java(1.8)应用程序中使用liquibase(3.6.3)来管理数据库(Oracle 12.2)模式更改.我的liquibase变更集不包含表空间"参数，并且所有数据(包括索引)都在默认表空间"USERS"中创建.现在，我需要将所有现有索引重新定位到另一个表空间"INDX"，并在默认的"USERS"表空间而不是新的"INDX"表空间中创建所有将来的索引.

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.

所以，我的问题:

1. 如何将旧索引从"USERS"表空间迁移到"INDX"表空间?

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

如何在"INDX"表空间中创建新索引?我知道列/约束元素的"primaryKeyTablespace"参数和createIndex元素的表空间"参数，还有其他解决方案吗?可以为所有集中索引指定'INDX'表空间吗?

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?

谢谢！

推荐答案

如果要集中表空间的定义，请在更改日志的开头使用属性.

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.

在Postgres中通常不使用表空间-至少在Oracle中不需要它们，因此您可以将其保留为空.

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.

然后在 createIndex 更改中使用该属性:

Then use that property in the the createIndex change:

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

要修改现有索引以使用其他表空间，您将必须使用数据库特定的SQL(请参阅pmdba的答案).您必须使它依赖于dbms:

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>

这篇关于如何在Java中使用Liquibase对表和索引使用不同的表空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！