Menu
I am wondering if there is a generic way to create an "ordered index" with liquibase. Something that will produce these kind of SQL statement:
CREATE INDEX idx_name ON my_table (m_column DESC)
I need that for oracle, postgresql, mysql, and sql server.
If not, I'll have to do it manually with the SQL tag for each RDBM.
360
To create an index on the existing column or set of columns, follow these steps: Step 1: Add the createIndex Change Type to your changeset with the needed attributes as it is shown in the examples. Step 2: Deploy your changeset by running the update command.
In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions. Before each table scan, the index build must wait for existing transactions that have modified the table to terminate.
Index key columns are part of the b-tree of the index. Included columns are not. Take two indexes: CREATE INDEX index1 ON table1 (col1, col2, col3) CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3) index1 is better suited for this kind of query: SELECT * FROM table1 WHERE col1 = x AND col2 = y AND col3 = z.
A clustered index is an index which defines the physical order in which table records are stored in a database. Since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table. By default a clustered index is created on a primary key column.
I submitted a pull request that made it into Liquibase 3.4.0 that made it possible to specify descending key columns not only for indexes, but also for primary keys and unique constraints. This even works on databases with quoted column names like Microsoft SQL Server.
<createIndex tableName="my_table" indexName="my_index">
<column name="col1"/>
<column name="col2" descending="true"/>
</createIndex>
<addPrimaryKey tableName="my_table" columnNames="col1, col2 DESC"/>
<addUniqueConstraint tableName="my_table" columnNames="col1, col2 DESC"/>
Download a 3.4.1 or later package here.
OR
Use this Maven dependency
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>3.4.1</version>
</dependency>
AND
Be sure to update the referenced XSD to 3.4
<?xml version="1.0" ?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
...
</databaseChangeLog>
I've just looked through liquibase source code and haven't found any handling of column ordering for indexes. So I would recommend you to use sql and modifySql blocks (I believe most DBMSes have the same syntax for create index, so probably you don't need modifySql):
<changeSet id="1">
<sql>
<![CDATA[
CREATE INDEX idx_name ON my_table (m_column DESC)
]]>
</sql>
<!-- just for example -->
<modifySql dbms="mssql">
<replace replace="CREATE INDEX" with="CREATE NONCLUSTERED INDEX"/>
</modifySql>
</changeSet>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
