4 Replies Latest reply: Feb 26, 2013 8:15 AM by John Spencer RSS

    How to create special column which represents result of a query

    Andreas S.
      Hi all,

      I need your help once more.
      The situation is the following:

      I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.
      I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.

      My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).
      So I could easily drop old data by dropping old partitions and tablespaces.

      Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.

      My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.
      The problem of this idea is that i have to update billions of rows.

      It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.
      Is there any way to "link" this information to a column in MESSAGE table?

      I mean something like this:

      alter table MESSAGE
      add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;

      Is there some possibility like that?
      Would the partitioning on this column / statement work?
      Would the value of the keep_message be calculated on runtime?
      If so will the performance influence be noticeable?
      If so will the performance also sink if the application is querying all rows except the keep_message?

      Kind regards,
      Andreas