Data Types In Oracle SQL - Part 4 - LOB And RowId Data Types
The built in LOB data types in Oracle are
1. BLOB
2. CLOB
3. NCLOB
4. BFILE
These data types enables to store large amounts of unstructured data such as text, images, videos, sounds in binary or character format. They also provide efficient, random, piecewise access to data.
Difference between LOB data types and LONG and LONG RAW datatypes:
A table can contain multiple LOB columns, but only one LONG column.
The maximum size of LONG column is 2 gigabytes, but the maximum size of a LOB can be 128 terabytes depending on the database block size.
LOBs support random access to data, but LONGs support only sequential access.
Tables with LOB columns can be replicated, but tables with LONG columns cannot.
From Oracle 9i, LOB datatypes can be stored inline i.e within a table, out-of-line i.e within a tablespace, using a LOB locator, or in an external file( BFILE data types).
BLOB Datatype :
The BLOB datatype stores unstructured binary data in the database.
BLOBs can store up to 128 terabytes of binary data.
BLOBs participate fully in transactions, changes made to a BLOB value can be committed or rolled back.
CLOB and NCLOB Datatypes :
The CLOB and NCLOB datatypes store up to 128 terabytes of character data in the database.
CLOBs store database character set data, and NCLOBs store Unicode national character set data.
CLOBs and NCLOBs participate fully in transactions, so changes made to a CLOB or NCLOB can be committed or rolled back.
Both fixed width and variable width character sets are supported.
BFILE Datatype :
The BFILE datatype stores unstructured binary data in operating-system files outside the database.
A BFILE column or attribute stores a file locator that points to an external file containing the data.
The amount of BFILE data that can be stored is limited by the operating system.
BFILEs are read only and supports only random reads.
BFILEs doesn’t participate in transactions.
ROWID Datatypes :
Each row in the database has an address.
The rows address can be queried using the pseudo column ROWID.
Oracle database uses rowids internally for the construction of indexes.
Rowids are the fastest means of accessing particular rows.
They can be used to see how a table is organized.
They are unique identifiers for rows in a given table.
You can also create tables with columns defined using the ROWID datatype, for example, you can define an exception table with a column of datatype ROWID to store the rowids of rows in the database that violate integrity constraints.
Columns defined as ROWID behaves like other table columns, and each column requires six bytes to store pertinent column data.
A Rowid contains following information about a row.
In which data block the row resides in the datafile.
The position of the row in the data block. position for first row is always 0.
In which datafile the row resides.
Note :
One should not use rowid as the primary key of a table. The rowid may change incase of deletion and re-insertion of row. Oracle database may reassign the rowid of a deleted record to a new row.
You cannot insert, update or delete the value of a rowid.
1. BLOB
2. CLOB
3. NCLOB
4. BFILE
These data types enables to store large amounts of unstructured data such as text, images, videos, sounds in binary or character format. They also provide efficient, random, piecewise access to data.
Difference between LOB data types and LONG and LONG RAW datatypes:
A table can contain multiple LOB columns, but only one LONG column.
The maximum size of LONG column is 2 gigabytes, but the maximum size of a LOB can be 128 terabytes depending on the database block size.
LOBs support random access to data, but LONGs support only sequential access.
Tables with LOB columns can be replicated, but tables with LONG columns cannot.
From Oracle 9i, LOB datatypes can be stored inline i.e within a table, out-of-line i.e within a tablespace, using a LOB locator, or in an external file( BFILE data types).
BLOB Datatype :
The BLOB datatype stores unstructured binary data in the database.
BLOBs can store up to 128 terabytes of binary data.
BLOBs participate fully in transactions, changes made to a BLOB value can be committed or rolled back.
CLOB and NCLOB Datatypes :
The CLOB and NCLOB datatypes store up to 128 terabytes of character data in the database.
CLOBs store database character set data, and NCLOBs store Unicode national character set data.
CLOBs and NCLOBs participate fully in transactions, so changes made to a CLOB or NCLOB can be committed or rolled back.
Both fixed width and variable width character sets are supported.
BFILE Datatype :
The BFILE datatype stores unstructured binary data in operating-system files outside the database.
A BFILE column or attribute stores a file locator that points to an external file containing the data.
The amount of BFILE data that can be stored is limited by the operating system.
BFILEs are read only and supports only random reads.
BFILEs doesn’t participate in transactions.
ROWID Datatypes :
Each row in the database has an address.
The rows address can be queried using the pseudo column ROWID.
Oracle database uses rowids internally for the construction of indexes.
Rowids are the fastest means of accessing particular rows.
They can be used to see how a table is organized.
They are unique identifiers for rows in a given table.
You can also create tables with columns defined using the ROWID datatype, for example, you can define an exception table with a column of datatype ROWID to store the rowids of rows in the database that violate integrity constraints.
Columns defined as ROWID behaves like other table columns, and each column requires six bytes to store pertinent column data.
A Rowid contains following information about a row.
In which data block the row resides in the datafile.
The position of the row in the data block. position for first row is always 0.
In which datafile the row resides.
Note :
One should not use rowid as the primary key of a table. The rowid may change incase of deletion and re-insertion of row. Oracle database may reassign the rowid of a deleted record to a new row.
You cannot insert, update or delete the value of a rowid.
Comments
Post a Comment