Home » RDBMS Server » Server Administration » Oracle Data Blocks clarification
Oracle Data Blocks clarification [message #208964] Tue, 12 December 2006 14:36 Go to next message
wtolentino
Messages: 410
Registered: March 2005
Senior Member

+------------+
|            |\
|            | \
|  Segment   |  \
|            |------+
+------------+      |
 \|      | | \      |
  |      | |  \     |+--+
  |Extent| | Extent ||__|
  |      | |    \   ||__|
  +------+ +--------+|__|
      |__| |__| |__| |__|
      |__| |__| |__| |__|
      |__| |__| |__| |__|
      |__| |__| |__| |__|
      |  | |  | |  | |  |
      +--+ +--+ +--+ +--+

      |                 |
      -------------------
               |
               v
           Data Blocks   


From the oracle10g database concepts manual it defines the data blocks as:
At the finest level of granularity, Oracle stores data in data blocks (also called logical
blocks, Oracle blocks, or pages). One data block corresponds to a specific number of
bytes of physical database space on disk.


Looking thru the Data Block Format at the database concept manual:

+---------+
|         | --> Common and Variable Header
+---------+
|         | --> Table Directory
+---------+
|         | --> Row Directory
+---------+
|         | --> Free Space
|         |
+---------+
|         | --> Row Data
|         |
|         |
|         |
|         |
+---------+

The definition it has given for Row Directory is as:
This portion of the data block contains information about the actual rows in the block
(including addresses for each row piece in the row data area).
After the space has been allocated in the row directory of a data block’s overhead, this
space is not reclaimed when the row is deleted. Therefore, a block that is currently
empty but had up to 50 rows at one time continues to have 100 bytes allocated in the
header for the row directory. Oracle reuses this space only when new rows are inserted
in the block.


Now suppose I have this row of data from the table emp:

SQL> select rowid, empno, ename, job, hiredate, sal
  2    from emp;

ROWID                   EMPNO ENAME      JOB       HIREDATE         SAL
------------------ ---------- ---------- --------- --------- ----------
AAD45OAAJAAAF1mAAA       7566 JONES      MANAGER   02-APR-81       2975
AAD45OAAJAAAF1mAAB       7902 FORD       ANALYST   03-DEC-81       3000
AAD45OAAJAAAF1mAAC       7839 KING       PRESIDENT 17-NOV-81       5000
AAD45OAAJAAAF1mAAD       7698 BLAKE      MANAGER   01-MAY-81       2850
AAD45OAAJAAAF1mAAE       7782 CLARK      MANAGER   09-JUN-81       2450
AAD45OAAJAAAF1mAAF       7369 SMITH      CLERK     17-DEC-80        800
AAD45OAAJAAAF1mAAG       7499 ALLEN      SALESMAN  20-FEB-81       1600
AAD45OAAJAAAF1mAAH       7521 WARD       SALESMAN  22-FEB-81       1250
AAD45OAAJAAAF1mAAI       7654 MARTIN     SALESMAN  28-SEP-81       1250
AAD45OAAJAAAF1mAAJ       7788 SCOTT      ANALYST   09-DEC-82       3000
AAD45OAAJAAAF1mAAK       7844 TURNER     SALESMAN  08-SEP-81       1500
AAD45OAAJAAAF1mAAL       7876 ADAMS      CLERK     12-JAN-83       1100
AAD45OAAJAAAF1mAAM       7900 JAMES      CLERK     03-DEC-81        950
AAD45OAAJAAAF1mAAN       7934 MILLER     CLERK     23-JAN-82       1300

14 rows selected.


Questions:
1. looking at the above sample rows at the 1st row for rowid AAD45OAAJAAAF1mAAA
   is this info stored in the Data Block Row Directory?
2. Are those data on the 1st row for rowid AAD45OAAJAAAF1mAAA such as those empno 7566,
   ename JONES, job MANAGER, hiredate 02-APR-81, and sal 2975 are stored in the Data Block
   Row Data? 
3. Are they (rows) put together into one Row Data (occupying the same data block row data)? 
4. Or each column values in a rows are put into multiple data block row data and still have
   the same rowids?



Thank you in advance.
Re: Oracle Data Blocks clarification [message #208968 is a reply to message #208964] Tue, 12 December 2006 15:17 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
See row migration and row chaining.
http://www.akadia.com/services/ora_chained_rows.html

Also see inline and out of line blob/clob storage.
http://www.oracle.com/technology/products/database/application_development/pdf/lob_performance_guidelines.pdf


Previous Topic: connection error
Next Topic: Denormalized columns
Goto Forum:
  


Current Time: Fri Sep 20 05:49:40 CDT 2024