Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_ROWID and IOTs
DBMS_ROWID and IOTs [message #341121] Sat, 16 August 2008 17:35 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
I'm reading on index-organized tables. Then I just tried to see the block number's of each rows in it, but I can't seem to get this to work. Can someone point me what I'm doing wrong? Thanks!


create table t1 (
   x int,
   y int,
   z varchar2(100),
   constraint t_pk primary key (x) 
)
organization index
tablespace indx

insert into t1
select object_id, data_object_id, object_name
from all_objects
where rownum <= 100;

SQL> select dbms_rowid.rowid_block_number(rowid, 'SMALLFILE') rbn, x, y
  2  from t1
  3  /
select dbms_rowid.rowid_block_number(rowid, 'SMALLFILE') rbn, x, y
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'

SQL> select dbms_rowid.rowid_block_number(rowid) rbn
  2  from t1;
select dbms_rowid.rowid_block_number(rowid) rbn
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'


Re: DBMS_ROWID and IOTs [message #341123 is a reply to message #341121] Sat, 16 August 2008 18:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
IOT's use universal ROWIDs. Per the docs:

"DBMS_ROWID is not to be used with universal ROWIDs (UROWIDs)."
Re: DBMS_ROWID and IOTs [message #341125 is a reply to message #341123] Sat, 16 August 2008 18:56 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi, thanks for that! I'll read on that. Just another question, is there a way to check if a row is moved to another block because of one new record inserted to that block? because from what I've understand, oracle will insert that row to that block if it belongs to that series of rows even though the block is full. hope I've explained it clearly. thanks!
Re: DBMS_ROWID and IOTs [message #341141 is a reply to message #341125] Sun, 17 August 2008 00:45 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I assume the question is still for IOT.
From doc:
Quote:
Each logical rowid used in a secondary index includes a physical guess, which identifies the block location of the row in the index-organized table at the time the guess was made; that is, when the secondary index was created or rebuilt.

Quote:
When a row's physical location changes, the logical rowid remains valid even if it contains a guess, although the guess could become stale and slow down access to the row. Guess information cannot be updated dynamically. For secondary indexes on index-organized tables, however, you can rebuild the index to obtain fresh guesses[...].
Collect index statistics with the DBMS_STATS package or ANALYZE statement to keep track of the staleness of guesses[...].
When you collect index statistics with the DBMS_STATS package or ANALYZE statement, Oracle checks whether the existing guesses are still valid and records the percentage of stale/valid guesses in the data dictionary[...].

So the answer is no for a particular row, you can just have statistics information.

Regards
Michel
Previous Topic: Parent - Child query
Next Topic: Pack decimal
Goto Forum:
  


Current Time: Tue Dec 03 15:52:14 CST 2024