Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24525

sql_mode="oracle" does not support "rowid" pseudo column

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      Each row of data in oracle database has a unique identifier(or rowid), which is usually used to access the data inside the oracle. Rowid requires 10 bytes of storage space and uses 18 characters to display. This value indicates the physical location of the row in the oracle database. You can use rowid in a query to indicate that the value is included in the query result.

      It takes 10 bytes or 80 binary bits to save the rowid. The 80 binary bits are:

      • The data object number indicates the number of the database object to which this row belongs. Each data object is uniquely assigned a number when the database is created, and this number is unique. The data object number occupies approximately 32 bits.
      • Corresponding to the file number, indicating the number of the file where the row is located, and each file label in the table space is unique. The position occupied by the file number is 10 bits.
      • The block number indicates the position of the block of the file where the line is changed. The block number requires 22 bits.
      • The row number indicates the specific position of the row in the row directory. The row number requires 16 bits.
        This adds up to 80 bits.

      Oracle's physical extension ROWID has 18 bits, and each bit is coded with 64 bits, which are represented by 64 characters A~Z, a~z, 0~9, +, /. A means 0, B means 1,...Z means 25, a means 26,...z means 51, 0 means 52,..., 9 means 61, + means 62, and / means 63.

      select rowid,empno from scott.emp;
       
      ROWID EMPNO
      ------------------ ----------
      AAAR3sAAEAAAACXAAA 7369
      AAAR3sAAEAAAACXAAB 7499
      AAAR3sAAEAAAACXAAC 7521
      AAAR3sAAEAAAACXAAD 7566
      
      

      Through the dbms_rowid package, you can directly get the information contained in the specific rowid:

      SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid) row_number from emp;
       
      OBJECT_ID    FILE_ID BLOCK_ID ROW_NUMBER
       
      ---------- ---------- ---------- ----------
       
           73196    4     151  0
           73196    4     151  1
           73196    4     151  2
           73196    4     151  3
           73196    4     151  4
      

      Oracle doc: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ROWID-Pseudocolumn.html#GUID-F6E0FBD2-983C-495D-9856-5E113A17FAF1

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              woqutech.com woqutech.com
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration