4 Replies Latest reply: Aug 4, 2014 4:54 AM by Santa.Pericles RSS

    tablespace as defined in index template is not used for manually defined indexes

    Santa.Pericles

      Hello,

      in version 4.0.1.836 I have defined under Preferences/Data modeler/Model/Physical/use index template   (I'm using the german version, I hope I translated properly) an index templates which contains a value for the tablespace attribute.

      For the indexes which are generated automatically (defined in the properties of my model under Properties/DDL/automatic index generation") this works fine. There was a thread about this already Tablespace for auto-generated indexes. This works fine. However, for indexes which I add manually to a table there is no tablespace clause generated in the generated ddl script. Is this a bug or am I missing something?

       

      Thanks for your help,

      Jochen

        • 1. Re: tablespace as defined in index template is not used for manually defined indexes
          David Last-Oracle

          Hello Jochen,

           

          For manually defined Indexes, the Index Template is applied when the Index is initially created.  I suspect you may have created your Indexes before you set up the Index Template?

           

          (For automatically generated Indexes, the Index Template values at the time of the DDL generation are used.)

           

          David

          • 2. Re: tablespace as defined in index template is not used for manually defined indexes
            Santa.Pericles

            Hello David,

            thanks for your reply. After I read your answer I took my some time to experiment a little bit, therefore my reply is a little late, sorry for that.

             

            I checked that the index template is properly defined and activated in the preferences/data modeler/model/physical/oracle.

            Then I created a new model with only one table and three columns, one is the PK, the others a varchar and number.

            The model specific settings are under Properties/DDL/automatic index generation to generate an index for the PK automatically.

             

            Then I generated the DDL and the index for the PK shows up with the tablespace clause.

            I added a new index on the varchar column.

            I generated the DDL and the index for the PK column shows up with the tablespace clause, whereas the index for varchar column shows up without the tablespace clause.

            Then I added a physical model to the relational one and in the physical model I added the tablespace which is referred to in the index template.

            I generated the DDL again and now both indexes show up with the proper tablespace clause.

             

            So to me it seems that for manually created indexes the tablespace clause is only generated when the tablespace is found in the physical model, which I think is not correct. If a tablespace clause can be generated for automatically generated indexes without  a physical model it should possible also for manually created indexes.

             

            What do you think?

             

            Thank you,

             

            Jochen

            • 3. Re: tablespace as defined in index template is not used for manually defined indexes
              David Last-Oracle

              Hi Jochen,

               

              Yes.  You're right.  For manually defined Indexes, the Index Template is applied when the Index is initially created.  As these properties are held in the Physical Model, it is only effective if the relevant Physical Model is open when the Index is initially created.  The Tablespace defined in the Template is only applied if the Physical Model contains a Tablespace with that name.

               

              I guess it is a bit inconsistent that for automatically generated indexes the Tablespace clause is generated even when the Physical Model is not open.

               

              David

              • 4. Re: tablespace as defined in index template is not used for manually defined indexes
                Santa.Pericles

                Hi David,

                 

                thanks for the confirmation. If there is anything I can do so that the behaviour of the index types is unified please let me. My personnel preference would be that the manually generated index behaves the same way as automatically generated one, because like that I wouldn't need to create physical model behind.

                 

                Thanks a lot,

                 

                Jochen