Details

    • Type: Explanation
    • Status: Obsolete
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
    • Explanation:
      Hide

      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base:

      https://community.exasol.com/t5/database-features/rowid/ta-p/821

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Background

      Questions:

      Is the ROWID always assigned in an ascending order?
      Is it possible to determine the sequence of inserted records with the ROWID?

      Explanation

      The ROWIDs of a table are managed by the DBMS. They are assigned in an ascending order per node and stay distinct within a table. For different tables, they could be the same.

      DML statements such as UPDATE, DELETE, TRUNCATE or MERGE might internally reorder data storage, invalidating and reassigning all the ROWIDs. Contrary to that, structural table changes such as adding a column leave the ROWIDs unchanged. Altering distribution keys of or reorganizing a table will certainly reassign ROWID values.

      Therefore the ROWID can't be used to determine the exact sequence or age of a record, it is designed to be a short term identifier for rows to be used for duplicate elimination.

      The ROWID pseudo column can only be used on table objects, not on views or subselects.

      Show
      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base: https://community.exasol.com/t5/database-features/rowid/ta-p/821 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Background Questions: Is the ROWID always assigned in an ascending order? Is it possible to determine the sequence of inserted records with the ROWID? Explanation The ROWIDs of a table are managed by the DBMS. They are assigned in an ascending order per node and stay distinct within a table. For different tables, they could be the same. DML statements such as UPDATE, DELETE, TRUNCATE or MERGE might internally reorder data storage, invalidating and reassigning all the ROWIDs. Contrary to that, structural table changes such as adding a column leave the ROWIDs unchanged. Altering distribution keys of or reorganizing a table will certainly reassign ROWID values. Therefore the ROWID can't be used to determine the exact sequence or age of a record, it is designed to be a short term identifier for rows to be used for duplicate elimination. The ROWID pseudo column can only be used on table objects, not on views or subselects.
    • Category 1:
      SQL
    • Category 2:
      ETL / Data Integration

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: