High Water Mark

It is a marker that shows you the point when you last inserted the data in the table. Let us simplify it with an example. Suppose you created a new table initially with no value. The high water mark will be at initial position say at 0. You made an insertion. The HWM will rise by one and will reach at position 1. Similarly as you will make more and more insertions the HWM will also rise; suppose you reached at a position where the value of HWM was 100. Now you started deleting values from the table. The value of HWM will not fall with the deletion of values from the table. So you can easily say that HWM shows the highest level of insertion you have ever done in that particular table.

Every time you give the command of select to fetch values from a table the oracle searches the table to HWM. It does not matter whether your last value is stored on position 10 or 90; if your HWM is 100 then oracle will search up to 100 positions. Normally for deleting values from the table you use the command of delete. This does not affect your HWM even if you have deleted all the values from the table. To bring the HWM to the initial position you can use the command:

SQL>truncate table table_name;

The high water mark is only meaningful in the table segment.

Row Structure

The way Oracle stores a row of a table is known as row structure. A row consists of two parts; one is header and the second is data. In the header part all the control information of that particular row is stored. Control information of a row consists of three parts. First is the links of row migration and row chaining. Secondly is the lock information. Locks are used so that no two transactions should update the row simultaneously. And the last thing is how many columns are there in that row. Every row in a table has same number of columns; so why there is a need for storing this information separately in each row. This is one of the features that only oracle provides. We will see the advantage of this in just a moment.

Next is the data portion. Data portion contains the actual data stored. Each entry of data has two parts. One is known as column length (CL) and the other is column value (CV). Oracle does not store the null values. This is the feature I talked about earlier. Suppose for a particular row there are five columns in which the last three has null values. Oracle does not store the null values altogether. Then how it knows that there is a null value? As I mentioned that oracle stores the number of columns for each row. So the value stored in the header will be 5 for that row but the actual values that are present are two. Oracle will realize here that the last three values are null. Real world databases do have a lot of null values. So oracle wastes a little space for storing the number of columns information in the header of every row but on the other hand it saves a lot of space by not storing the null values.

LOB Introduction

Char and Varchar2 data types are used to store text columns in an Oracle database. While for the normal requirements they are OK, but they are not as suitable when it comes to store large objects. By large objects I mean storing of entire PDF documents for example or storing of an image. Traditionally Oracle provided two data types for these situations namely as RAW and Long RAW. But the use of these data types was inherent to some limitations. The first one was that you cannot have more one column of RAW or Long RAW data types in one table. The storage capacity was limited to 2GB. And last you cannot perform SQL operations on the subset of data stored as RAW or Long RAW.

In Oracle 10g Release 1 and above (not sure of 9i) Oracle introduced Large Objects or LOB for short. The introduction of LOB data types culminates all the restrictions that were present in RAW and Long RAW data types. LOB itself is not a data type. Rather it is a category of four data types which are divided into two distinct categories. The categorization of LOB data types is based on their storage. The two categories are:

1. Internal

2. External

Internally stored LOB objects are those which are stored inside database. They are treated just like any other object inside database. And all SQL operations are valid for them that are valid for other data types. There are three data types in this category:

1. CLOB: It is used to store large documents and stands for Character Large Objects.
2. NCLOB: It is also used to store large documents which are in character format. But it stores these documents using a different format namely as National Character Set.
3. BLOB: This data type is used to store large objects which are in binary format. For example an image.

Externally stored large objects are those objects which are stored outside database but whose reference is maintained inside database. The storage of these objects is inherent to the limitations of Operating System files. Normal SQL operations performed on other internally stored objects will not be applicable to these externally stored objects. The only data type in this category is BFILE. The column of BFILE data type will be used only to store the location of large object file.