RE Oracle 9 PK Tablespace

Toad Data Modeler
1

Hi,

I’d like to be able to choose to load the TableSpace for a PK so I can generate in the form;

CREATE UNIQUE INDEX ON table (cols) TABLESPACE tbs

rather than the USING INDEX form.

Thanks
Malcolm

2

Hello Malcolm,

Thanks for your suggestion. We’re thinking about it and would like to ask you for reason/advantage of this behaviour. If we understand well, also indexes would have to be created to all keys in the model.
Is it what you wish? Do we understand well?

Thanks in advance for the details.

Regards,

Vl. + TDM Team

3

Hi Vladka,

Sorry it looks like I lost some of the first post.
It is just the way the RE and DDL for the unique constraint works.
I’d like to be able to get the tablespace information and have the option to create a different DDL syntax.

I’ll write (all of) the issue;

We are using the following;
1.
CREATE UNIQUE INDEX idx1 ON tbl (id ) TABLESPACE tbs;
ALTER TABLE tbl (
ADD CONSTRAINT tbl_pk PRIMARY KEY (id)
USING INDEX TABLESPACE tbs );

The RE’d equivalent creates the following;
2.
CREATE UNIQUE INDEX idx1 ON tbl (id ) TABLESPACE tbs;
ALTER TABLE tbl (
ADD CONSTRAINT tbl_pk PRIMARY KEY (id)
USING INDEX idx1 );

There is probably little difference between the two since Oracle makes the association with the existing unique index. This association is being RE’d.
If the index has not been created in the correct tablespace Oracle will create it in 1 whereas it seems to complain in 2.

The main reason for us is historical.
I need to be able to demonstrate that I haven’t really changed anything by the migration to TDM.

Hope that clarifies things a bit.

Regards
Malcolm

4

Hello Malcolm,

Thanks for your response. To be honest, after your first post we understood more than now (or at least thought we understood…) :wink:

We have checked out both SQL commands you mention. Both of them have identical result. Moreover, we think the second one (the one that TDM creates) is more clear and more precisely shows how it is saved in the database (in this case it shows that PK tbl_pk uses index idx1).

We’re very sorry, we do not understand the reason for change.

Thanks in advance for your remarks.

Regards,

Vladka + Mario