Size of stored ROWID
Andrew Clarke, April 30, 2002 - 3:43 am UTC
I'm trying to estimate the rollback segment size for a very large insert transaction (potentially 20 million rows).
Does Oracle store the ROWID in its binary form (13 bytes) or in character form (18 bytes)? I would guess the former, but I don't know where to look to find definitive answer.
cheers, APC
April 30, 2002 - 7:33 am UTC
You'll never do it that way. The amount of rollback generated will be a function of the indexes as well.
Best bet:
o run your insert transaction for a subset of the rows, say 1,000, then 10,000 then 100,000
o look at your used_ublk in v$transaction after each one (rollback after each insert)
o extrapolate up to 20,000,000 with those three observations.
ROWID
Shajan Joseph, April 30, 2002 - 10:14 am UTC
Tom, is this correct?
In a non-clustered table, ROWID will be unique. On the other hand, tables belong to same cluster and their rows can have the same ROWID.
April 30, 2002 - 11:42 am UTC
No really, not strictly speaking in a semantic sense.
In a TABLE (clustered or otherwise) a rowid will alwyas be UNIQUE.
In a database, a rowid does not have to be UNIQUE, you can have the same rowid for two different tables..
How is that
A reader, May 01, 2002 - 6:04 am UTC
Tom,
If i understood right what you are trying to say. You mean that there will be two tables inside one block.
This was discussed in one of the groups and thrashed by HJR and You saying that there can be only one table in a Block.
So Now the rowid will change sice the Block is going to change. so how can a database have the same rowid for different tables.
Your comments pls.
Thanks.
Regards,
Ganesh R
May 01, 2002 - 7:28 am UTC
Exactly.
You'll have to point me to that discussion, I know that is false. There can be dozens of tables using a single block -- the data dictionary is full of it -- I spend many pages in my book explaining that. Give me the link into google.com that shows this discussion.
I don't know what you mean by:
"So Now the rowid will change sice the Block is going to change."
at all. As for "how can a database ..." -- the example above shows HOW. You need an object_name+rowid to get a row. The block format includes a table and row directory on it. If you have my book -- read the chapter on tables, i go into this in detail.
One Segment Inside a Block.
A reader, May 01, 2002 - 8:44 am UTC
Tom,
Sorry About this but i am not able to lay my hands on that particular thread.
Can i say that A Block can Have Only One Segment inside it. If it is a Heap Table than there will be only table/Segment Right inside the Block. When you take a Cluster it is still one Segment Inside the Block But more than One Table Sharing the same Block and Hence can have the same rowid.
Your comments Pls.
Thanks.
Regards,
Ganesh R
May 01, 2002 - 9:10 am UTC
Ahh - that was the discussion - it was about SEGMENTS, not about tables.
I believe I made an over generalization regarding that with regards to SEGMENTS.
A cluster is a segement.
A table created in a cluster is not.
A table created normally (no cluster) is a segment (or may be made up of segments/partitions)
A segment/ block is accurate. If you were to complete the example above with:
1* select segment_name, segment_type from user_segments
a@ORA817DEV.US.ORACLE.COM> /
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
EMP_DEPT_CLUSTER CLUSTER
EMP_DEPT_CLUSTER_IDX INDEX
it shows the EMP table and DEPT table are *not* segments -- the CLUSTER is.
Thanks a Ton !!!
A reader, May 01, 2002 - 9:17 am UTC
Thanks...
Regards,
Ganesh R
Fine
A reader, May 01, 2002 - 9:32 am UTC
Dear Tom,
So can we conclude that if it is not clusters, than rowid would still be unique accross tables?
Thanks,
May 01, 2002 - 9:46 am UTC
yes.
Sagi, October 02, 2002 - 8:46 am UTC
Hi Tom!
Is it true that accessing a table is faster than a clustered table? I read this very long ago in some book. But today when i was trying your example i was surprised to see that accessing clustered tables is faster than normal table.
Say we had the below two tables in SCOTT shcema:
Default EMP & DEPT Tables and
EMP1 & DEPT1 (Clustered Tables -- as shown by you in the example)
I say "SET TIMING ON" at SQLPLUS
I find that
SELECT * FROM EMP1 is faster than
SELECT * FROM EMP...
So it shows that accessing the clustered tables are faster. Is there any performance issues say if the tables are huge. Would you suggest to have clustered tables created in Production Environment?
Finaly, the table
---- Picked below from your example ---
create table emp1
cluster emp_dept_cluster(deptno)
as
select * from scott.emp
is this a cluster table. If not then can you explain regarding it.
Regards,
Sagi
October 02, 2002 - 10:39 am UTC
just keep reading that chapter on tables in my book - over and over.
If one table type was "FASTER" then some other table type for all operations -- tell me -- why would we have it?
All tables are not created equal. They have different characteristics. Your job -- understand what these characteristics are, know when they might apply to a problem at hand (eg: don't run out and make EVERY table an IOT), and use them after benchmarking and testing to destruction.
It would be an utter waste of time for Oracle to have invented a really slow table.
As my book says "you use clustered tables each and every day of your life with Oracle. the data dictionary is built on them".
Read about the different types....
Think about how they are implemented....
Apply them to particular problems when they are relevant....