RelationalDBDesign RelationalDBDesign


ANSI SQL Extensions  «Prev 

Making a datablock 100% full

There is a trick to making a data file 100% full. Remember that Oracle always allocates one data file header block at the beginning of each data file, and we must account for this data block if we want to pack the file to 100%.
For example, assume that we want to create a one-megabyte data file. The file size would be 1,048,576 bytes.
However, we would not be able to place a table into this data file with an INITIAL extent of 1M, because of the annoying header block. However, if we make the data file 1M+8K (assuming we have an 8K db_block_size), then we will be able to allocate a 10-megabyte extent into this data file, and fill it to 100%.
Here we see the importance of NULL values when joining a table row with NULL values from a non-existent row value. Without the NVL clause, outer joins would be very difficult to manage.

Using the Null-Value Clause with Oracle SQL

The null value clause of Oracle SQL arises from the three valued logic that is the foundation of the SQL language. Essentially, three valued logic understands that a missing value (NULL inside Oracle), is NOT the same as the default for that data type.
For example, a NULL salary for an employee is not the same thing as a salary of zero.
Therefore, we need to be able to display data from Oracle tables, and quickly convert null values.

SELECT 
    ename, 
    NVL(TO_CHAR(SALARY), 'NOT YET ASSIGNED') "SALARY"
 FROM 
    employee;
ENAME SALARY
 ---------- -------------------------------------
 
 WOOLLY     85,000
 MAMMOTH    101,000
 SABERTOOTH NOT YET ASSIGNED