This discussion is archived
4 Replies Latest reply: Feb 26, 2013 6:15 AM by John Spencer RSS

How to create special column which represents result of a query

AndreasS. Newbie
Currently Being Moderated
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
  • 1. Re: How to create special column which represents result of a query
    Toon.Koppelaars2 Newbie
    Currently Being Moderated
    Why not simply introduce a third new table for messages to be kept?

    So:
    - VEHICLE, with the KEEP_DATA column Y/N.
    - MESSAGE_UNKEEP
    - MESSAGE_KEEP

    Vehicles with KEEP_DATA = Y have messages in third table.
    Other vehicles have messages in second table.

    Optionally build a UNION-ALL view if your UI requires it.
    Optionally build Instead-Of triggers if your UI requires it.
    Or, better provision transactional API's for your UI...
  • 2. Re: How to create special column which represents result of a query
    AndreasS. Newbie
    Currently Being Moderated
    Thanks for the quick answer.

    If possible would like not to change the application-server.
    Currently the application-server is accessing the tables directly, not by view.

    But I will keep your idea in mind if there are no other satisfying solutions.

    Regards,
    Andreas
  • 3. Re: How to create special column which represents result of a query
    Karthick_Arp Guru
    Currently Being Moderated
    What is your DB version?
    The problem of this idea is that i have to update billions of rows.
    If this is your underlying problem then if you are in 11g and above you can use [url http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_parallel_ex.htm]DBMS_PARALLEL_EXECUTE and split your update into multiple chunks and execute it parallel.
    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) ;
    As far as i know such a thing is not possible.
  • 4. Re: How to create special column which represents result of a query
    John Spencer Oracle ACE
    Currently Being Moderated
    Is your requirement that all messages for some vehicles must be kept, or is it some messages for some vehicles?

    If it is the former, then keep_data is an attribute of the vehicle, and the column should be added to the vehicle table. If it is the latter, then you need to add it to the messages table. If you have to add it to the messages table, then I would likely only update the messages that require keeping and leave the others null, rather than try to update the whole table with Y or N.

    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points