4 Replies Latest reply on May 11, 2007 8:42 AM by 576889

    talbes without timestamp

      according to ILMA doc, tables that do not have data column could not be managed by ILMA, my problem is: I have a table that has data stamp, and several tables that associate to the above that table but without data column, of course I would like to shrift the associated tables together with the table with data stamp. what should I do?

      or generally , use ILM technology, how do I manage a tables without time stamp ?
        • 1. Re: talbes without timestamp
          Jack Raitto-Oracle
          Currently, the ILM Assistant can manage tables that contain a date column. This is necessary to correlate the age of the data within the table against the calendar. We have plans to add support for tables that do not directly contain a date column, specifically where a date value can be obtained through a foreign key join such as you describe (and possibly other scenarios as well).

          Your best option today is to denormalize the date column into each of the foreign key tables. This denormalization allows you to partition the foreign key table by the denormalized date column, which then allows storage tier migration via the ILM Assistant. Adding support to the ILM Assistant to support tables that do not contain a date column is non-trivial. And while we do plan to add such support, I cannot today project when that support will be available.
          • 2. Re: talbes without timestamp
            Thanks a lot Jack for your answer.

            but the idea of "denormalize the date column into each of the foreign key tables" maybe could not help us out this problem, actually we could not use only the time stamp to shrift data to new partition, we also need to check if the transaction is finished (a billing process).

            generally in oracle ILM (not in ILMA), I see in some doc that except for "data", an id or even a hybrid classification are also supported. (doc "Implementing ILM using Oracle Database 10g"), But I could not find any samples. According to your knowledge is it possible to use another key other than data, (this of course not provided by ILMA).

            Does Oracle 10g have SQL support for moving certain rows (with some kind tag) into certain partition ?

            many thanks in advance

            • 3. Re: talbes without timestamp

              Since Oracle ILM is based around partitioning you can implement a solution using any valid partitioning key and method.

              However, if you want to manage this environment using the ILM Assistant then only range partition by date is currently supported.

              • 4. Re: talbes without timestamp
                thank you very much Lilian,

                I will give a try and post a feedback in forum,

                a nice week end!