Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    30

    Unanswered: Joining Tables : Faster with ROW_ID ?

    I have a registry which stores all the rows that were inserted/updated on a particular day for all tables, so I will be wanting to join this table to all of my tables at some stage to do extraction queries to get only the entities that have changed...

    This table is referenced by the table-name and the primary key is it referring too, but, can anyone tell me what the quickest way to join this table to multiple tables would be ? Obviously indexing the table-name and primary key in the registry plus the primary keys in each table would be fast, but if I can store the internal ROW_ID in the registry as an internal pointer to rows in each table ( would still need to store the table-name obviously ), would that be faster to join on as it is internal to Oracle ?

    Thanks,
    Adam

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > would that be faster to join on as it is internal to Oracle ?
    Yes it would be faster until it breaks.
    As a general rule of thumb, ROW_ID should NEVER be stored as permanent data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Before you continue to think that ROW_ID is a silver bullet to the performance issue, consider the following.
    Everybody wants s/w to be as fast & effiecient as possible.
    If using ROW_ID could be made 'reliable', then why isn't Oracle or ANY major s/w vendor using ROW_ID in their application?

    Here is a free clue, a row can "move" from its original block to a different block.

    Guess what happens to the code that depends on finding the data in the original location?

    The best solution is unique keys in every table that need to be joined to each other; regardless of 1:1, 1:M, or M:M joins.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •