7

I'm trying to use Liquibase for our project. We mainly use Oracle database and some other database less often. I'm trying to figureout how to specify column order in case of indexes. Below is a typical create index change set.

<createIndex indexName="PK_xxxxxxx" tableName="xxxxx" unique="true">
   <column name="column_1"/>
   <column name="column_2"/>
   <column name="column_3"/>
</createIndex>

When it comes to performance and application scalablity, column order in index matters a lot. Can you please guide me if there is a way to specify same while creating index?

PS: As per column tag documentation, attributes afterColumn, position exists and they are applicable only for create table I assume. Here is what documentation says about it.

If used in an 'addColumn' command, this attribute allows you to control where in the table column order the new column goes. Only one of beforeColumn, afterColumn or position are allowed. Since 3.1

Divyang Desai
  • 7,483
  • 13
  • 50
  • 76
Vijayakumar Udupa
  • 1,115
  • 1
  • 6
  • 15
  • The documentation you are quoting is referring to the "addColumn" command. You are using the createIndex command. – Peter Henell Nov 18 '16 at 12:31
  • @PeterHenell Yes, during create table, column order is allowed using 'AfterColumn' or 'Position' attributes. I was wondering if same can be used in conjunction with 'CreateIndex' as well. – Vijayakumar Udupa Nov 21 '16 at 06:15
  • @a_horse_with_no_name, Thank you. Typically XML is 'order less', means if I use other standard tools for XML manipulation while generating these XML files, there is no guarantee that columns will stay in order. That was reason for this question. – Vijayakumar Udupa Nov 21 '16 at 06:18

1 Answers1

8

Liquibase will use the order of columns as listed in the createIndex tag - very much like the DBMS uses the order specified in the create index statement.

The following changeset:

<changeSet author="arthur" id="1">
  <createTable tableName="foo">
    <column name="col_1" type="integer"/>
    <column name="col_2" type="integer"/>
    <column name="col_3" type="integer"/>
  </createTable>
  <createIndex indexName="ix_one" tableName="foo">
    <column name="col_1"/>
    <column name="col_2"/>
    <column name="col_3"/>
  </createIndex>
  <createIndex indexName="ix_two" tableName="foo">
    <column name="col_3"/>
    <column name="col_2"/>
    <column name="col_1"/>
  </createIndex>
  <createIndex indexName="ix_three" tableName="foo">
    <column name="col_2"/>
    <column name="col_3"/>
    <column name="col_1"/>
  </createIndex>
</changeSet>

will produce the following statements (when e.g. run with updateSQL):

CREATE TABLE public.foo (col_1 INT, col_2 INT, col_3 INT);

CREATE INDEX ix_one ON public.foo(col_1, col_2, col_3);

CREATE INDEX ix_two ON public.foo(col_3, col_2, col_1);

CREATE INDEX ix_three ON public.foo(col_2, col_3, col_1);
  • Hi, I've tested it earlier. While liquibase respects order of elements, other XML processing tools don't. For example, I use python to generate XMLs which will be input for liquibase. The standard python XML manipulation library does not follow "STRICT" parsing, instead tries to sort tags alphabetically. This is a problem for me. May be in future versions, there will be attribute like position for create index as well, which should solve this problem. Thanks again for the help and clarification. – Vijayakumar Udupa Nov 21 '16 at 09:11
  • Well, those "other XML processing tools" you are using are buggy then. The order of elements in XML ***is*** relevant. You can even define a _required_ order in an XML schema (XSD) - that wouldn't be possible if that wasn't relevant to XML –  Nov 21 '16 at 09:15
  • That's partially true. order in XML matters only if XSD says so. If not, for a normal XML, order does not matter. Here is link for such a discussion. [does-xml-care-about-the-order-of-elements](http://stackoverflow.com/questions/4328867/does-xml-care-about-the-order-of-elements) – Vijayakumar Udupa Nov 21 '16 at 09:21
  • That link is talking about invalid orders. If you have certain tags in the wrong order that makes an invalid document that fails to be valid.. in a validator. Order doesn't matter in xhtml or liquibase files. The order of your statements don't matter in a python function. You can sort those and make perfectly valid documents (as long as you don't sort stuff like the contents of table tags which have a valid order). When you do that though you change the semantics of those documents. Validators don't validate semantics. – user988346 Jul 08 '20 at 14:28