DBMS_ROWID and IOTs [message #341121] |
Sat, 16 August 2008 17:35 |
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 #341125 is a reply to message #341123] |
Sat, 16 August 2008 18:56 |
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 |
|
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
|
|
|