Skip to Content
0

Setting Tablespacefor Indexes

Jul 06, 2017 at 07:59 AM

85

avatar image
Former Member

Hi all,

we have the following requirement: there are few types of tables (we are on data-valut on Oracle12c), each of those table types has his own tablespace.

Now the customer wants to have a similar logic for the indexes.

Let's say, we have a satellite table, this table has a tablespace SAT_xxx. There are some indexes in that table, those indexes shall be assigned to a tablespace IDX_SAT_xxx.

There is also another table, the hub table, this has a tablespace HUB_xxx. The indexes in that table, shall be assigned to a tablespace IDX_HUB_xxx.

How to do that?

P.S.

We are generating that data-vault model from an LDM model.

Thanks,

Rafal

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

avatar image
Former Member Jul 10, 2017 at 06:52 AM
0

Hi Phillip,

thanks, but this is not the solution. As I already wrote: "We are generating that PDM data-vault model from an LDM model".

So ... we must be able to set it during generation time (in the Transformations).

For the tables, we have a template model, with pre-set variables for table tablespaces. Those variables are then replaced by the real ones at generation time (in the Transformation itself).

BR,

Rafal

Share
10 |10000 characters needed characters left characters exceeded
Phillip Lam
Jul 07, 2017 at 04:19 PM
0

Open the index's property. In Physical Options(Common), Click dropdown for Tablespace to select a target.


capture.png (31.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Phillip Lam
Jul 11, 2017 at 10:51 PM
0

It's possible to run transformation to get desired result.

In LDM, create 2 exteneded attributes in table level. One stores tablespace name for table. Another stores tablespace name for index.

Use pre-transformation to pass the extended attribute values to PDM.

In PDM, create post-transformation at model level. It uses the two tablespace names to create tablespaces

For each table, call SetPhysicalOptionValue to set tablespace.

For each table, find its index. Index also has function SetPhysicalOptionValue. Set tablespace name to the index.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 20, 2017 at 08:41 AM
0

Hi Philipp,

thanks, we have tried out this approach, but found out, indexes (at least for PK and AK) are created after the transformations... so, we would have to have an additional method, one hast to run after the generation in order to set the tablespaces for those indexes.

Is there a way, I can create the indexes during the transformation LDM -> PDM? What have of course set the option PKAutoIndex = true in the DBMS File...

BR,

Rafal


ok.jpg (65.8 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 21, 2017 at 07:24 AM
0

Yes, have tried that. More or less, everything is 'post', since 'pre' would work only in case I'm generation PDM -> PDM. The problem is, the indexes are build up after all transformations have been executed. I think this is how PD works....

The only workaround I found, is to define an Event Handler on Index level in the DBM Extension, and put all the logic there...

Share
10 |10000 characters needed characters left characters exceeded
George McGeachie Jul 20, 2017 at 10:31 AM
0

Have you tried adding the automation to a 'post' transformation, so it amends the PDM AFTER it has been generated from the LDM?

Share
10 |10000 characters needed characters left characters exceeded