3 Replies Latest reply on Jul 17, 2012 9:03 AM by mschreib_il - oracle

    TimesTen IMDB and Partitioning

      We are looking at TimeTen IMDB as a performance enhancement on our current 11gR2 database.

      Some of our tables are using Interval Partitioning. I am wondering if TimeTen IMDB can be used to cache the latest (most recent) partitioning. Can anyone offer insight, advice or and example?
        • 1. Re: TimesTen IMDB and Partitioning
          I'm checking this for you. In the v7 docs it states you can cache the partitioned table, but not individual partitions. The v11 doc wording changed - see Oracle® In-Memory Database Cache User's Guide -> 11 Compatibility Between TimesTen and Oracle -> Schema objects but I believe it's indicating essentially the same thing.

          "TimesTen does not recognize some of the schema objects that are supported in Oracle. TimesTen returns a syntax error when a statement manipulates or uses these objects and passes the statement to Oracle. The unsupported objects are:

          1 person found this helpful
          • 2. Re: TimesTen IMDB and Partitioning
            I've had it confirmed that the same information is unfortunately still true in 11 - we can't cache individual partitions.
            • 3. Re: TimesTen IMDB and Partitioning
              mschreib_il - oracle
              I believe that the problem here is that triggers cannot be defined on an individual partition of an Oracle partitioned table. When a TimesTen user creates a read-only cache group against an Oracle base table, the TimesTen cache agent creates a trigger against the base table which fires on each DML operation against the base table. So in order to create a trigger on a specific partition of a partitioned table, we would have to be able to define a trigger specifically against that partition, and I do not believe that this is possible.

              It seems to me that there a couple of possible workarounds here:

              (1) Assuming that the table is range-partitioned, we could define the cache group with a where clause which matches the key range of the specific partition: so that even if the cache group is defined on the entire table, in actuality all cache operations will focus on the single partition (and the Oracle compiler knows how to do partition elimination so that only that partition would be accessed). I'm not sure if the where clause in the cache group definition is reflected in the trigger that will be created, so possibly the trigger will end up firing for every DML on the entire table: this would have to be checked out.

              (2) Does anyone know if a PL/SQL trigger has the ability to identify the partition of the table which is causing the trigger to fire? If so, then we would be able (manually) to modify the cache group trigger to only execute if it were firing in the specific partition of interest.

              The other comment here is that this discussion applies to read-only cache groups which are incrementally refreshed or refreshed manually. If you are considering using a write-through cache groups (either SWT or AWT), then it might be possible to define a synonym on the table partition and then create the cache group against the partition. However, even if you could do this, it would be your responsibility in the TimesTen application code to make sure that any inserts to that partition obeyed the range or other partition rules of that partition.