What Is The Difference Between Oracle Table And Oracle Segment?
DBAs use these two concepts interchangeably: table and segment, more precisely table segment. Why is that?
When we think of tables, we think of the data inside that table. However when we think of the properties of the table, such as size, blocks, storage, etc. , we think about the table segment. When a user is asking us how big a table is, we do not correct them, to use the proper terminology of segment…So this is why we use these two things as if they were the same.
However, they are not the same.
A table is a basic unit of storage for the Oracle database. The table’s data is stored in rows and columns. You create a table by specifying one or more columns. Usually a table is created empty, unless you use the CTAS method. When you insert data into the table, you are creating rows for the table. Tables have names and an owner, they belong to a schema. You can select from a table to get the rows within the table, however you cannot determine the size of the table in KB/MB/GB. To see information about a table, you check DBA_TABLES data dictionary view.
A segments is a a logical storage structure within a tablespace, made up of extents. We can have table segments and index segments. A table segment is associated with a table or table partition, and an index segment is associated with an index or index partition. The segment has extents allocated, extents have data blocks, as a result we can measure the segment size in KB/MB/GB and the number of blocks in a segment. You cannot create a segment explicitly, there is no CREATE SEGMENT statement. You cannot select from a segment to see the rows within the segment, because the segment is just a logical storage structure. To see information about a segment, you check DBA_SEGMENTS data dictionary view.
The table and the segment associated with the table have the same name! So the columns table_name and segment_name in the data dictionary, have the same name, and this is how they are related! When you want to see the size of a table, you need to check the size of the table segment that is associated with your table.
Usually the users you will be talking to, will ask you about the size of a table, ie. How big is the table?, How many data blocks does a table have? How many rows? and so on. Please, don’t correct them, they don’t know about tables and segments! But you do! You know that rows are retrieved from tables, and segments have data blocks!
Now the question is when you create a table, is a segment also automatically created? Join me next week to talk about DEFERRED SEGMENT CREATION to find out!
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !
