Interested in how can I use Rowid in queries?
I found that it is used to quickly delete duplicate records in Table:
delete from your_table Where Rowid NOT IN SELECT MIN (ROWID) From your_table Group by column1, column2, column3);
Tell me how else you can use this property? Or is it no longer practical, but a theorical property for understanding the database device?
Answer 1, Authority 100%
Rowid is the physical line of the line, so access to the rowid line is the fastest way .
Rowid is unique for each line, so it can be used as a surrogate primary key in different queries.
Oracle ensures that the string exists its RowID will not change.
But, constantly relying on Rowid as the primary key dangerous.
First, the string can move (DELETE + INSERT) as a result of various actions (import \ export, move the row in the partitioned table when it changes the value of its partition key columns, move the table (ALTER TABLE MOVE), compression of the table (Alter Table Shrink ), etc.).
Secondly, after removing the rowid row, it can be rewriting any new line.
Answer 2, Authority 100%
Basic applications of pseudo-colonel values
- provide the fastest recording access path.
- provide information about the place of storage of the recording (see the description of the package DBMS_ROWID )
- they are unique to each entry in the table (see example in question).
For what these values should not be used:
You Should Not Use Rowid AS The Primary Key Of A Table.
Rowid are relevant only at the current time, but there is no guarantee of their invariabry. They should not be used for primary keys, or stored in the database for some other purposes.
Important note on the uniqueness of pseudo-colonel values
Usually, A Rowid Value Uniquely Identifies A Row in the Database. However, Rows in Different Tables That Are Stored Together In The Same Cluster Can Have the Same Rowid.
They are unique to the table, but not for the database as a whole.
Practical application in SQL query is relatively not frequent.
In PL / SQL code opposite – every day. For example, to avoid recording recording in subsequent static queries (quick access to the already found record).
so for explicit instructions in the query:
create table t1 (ID Integer Primary Key, Memo Varchar2 (96)); INSERT INTO T1 SELECT ROWNUM, 'MEMO' || ROWNUM FROM XMLTable ('1 to 9'); Declare. Type Ridarr Is Table of Rowid; r ridarr; CUR SYS_REFCURSOR; Begin. OPEN CUR FOR SELECT ROWID FROM T1 WHERE ID IN (3.6); Fetch Cur Bulk Collect Into R; FORALL IX IN INDICES OF R Update T1 Set Memo = Memo || '*' where Rowid = R (IX); end;
And so implicitly in the predicate
Current of , which defines the current entry by
declare m t1.memo% type; Cursor Cur Is. SELECT MEMO FROM T1 WHERE ID IN (3.6) FOR UPDATE; Begin. Open Cur; LOOP FETCH CUR INTO M; Exit WHEN CUR% NotFound; Update T1 Set Memo = M || '#' Where Current of Cur; End Loop; end;