1

Using Druid to generate SQL script for Oracle, I have seen a difference between the version 3.10 and 3.13 and I would like to understand what going on.

SQL generated by Druid 3.10 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

SQL generated by Druid 3.13 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE),
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

As you can see, the only difference is that Druid don't generate anymore a USING INDEX TABLESPACE &&TSINDEX after the UNIQUE constraint.

I suspect that there is a reason for why Druid don't generate anymore this USING INDEX thing on the UNIQUE constraint (could have something to do with the fact that there already a USING INDEX on the PRIMARY constraint), but I would like to understand why.

I have two questions then :

  1. Can this cause some error on Oracle ?
  2. Do this change something for the index used by the unique constraint ? Not in the right table space ?
  • Are you experiencing some error when you run either of the statements? Also, what version of Oracle? – Phil Sumner Jul 1 '15 at 10:09
  • Nop, don't have any error. I am just asking to know what's going on behind this change in the script generation. We are using Oracle 10g. – vdubus Jul 1 '15 at 12:00
3

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error.

http://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52225

  • If you specify schema.index, then Oracle attempts to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, then Oracle
    returns an error.

  • If you specify the create_index_statement, then Oracle attempts to create the index and use it to enforce the constraint. If Oracle
    cannot create the index or cannot use the index to enforce the
    constraint, then Oracle returns an error.

  • If you neither specify an existing index nor create a new index, then Oracle creates the index. In this case:

    • The index receives the same name as the constraint.

    • If table is partitioned, then you can specify a locally or globally partitioned index for the unique or primary key constraint.

  • Ok, I was expecting that this won't create any issue on the database. But do you have an idea why Druid remove this statement as if this wasn't of any used ? I read something telling that Oracle will try to find a correct index. Could it be that in this specific case, it will use the one defined on the primary key ? – vdubus Jul 1 '15 at 12:16
  • It can not use the primary key index, the columns referenced in the unique constraint are different. I do not know Druid, but if a seperate tablespace can be defined per index, maybe not specifying it will just use the table default, leaving out the USING INDEX TABLESPACE from the DDL. – Balazs Papp Jul 1 '15 at 12:33
  • I can't find the cited text in the referenced pate. Is it possible to refine the link? – miracle173 Jul 3 '15 at 10:34
  • Updated the link. – Balazs Papp Jul 3 '15 at 11:34
  • thank you.I found this comment by accident. If youd add a´@miacle173 to a comment I will get a notification. dba.stackexchange.com/editing-help#comment-reply – miracle173 Jul 5 '15 at 5:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.