Understanding Table Types- Tables and Constraints
The Oracle database supports a vast and robust variety of table types. These various types are described in Table 7-1.
Table 7-1. Oracle Table Type Descriptions
This chapter focuses on the table types that are most often used: heap organized, index organized, and temporary tables. Partitioned tables are used extensively in data warehouse environments and are covered separately in Chapter 11, and external tables are covered in Chapter 15. For details on table types not covered in this book, see the SQL Language Reference Guide, in the Oracle Database documentation.
Understanding Data Types
When creating a table, you must specify the column names and corresponding data types. As a DBA, you should understand the appropriate use of each data type.
Application issues, performance, and the accuracy of data can be affected by the wrong choice of data type. For instance, if a character data type is used when a date data type should have been used, this causes needless conversions and headaches when attempting to do date math and reporting.
Compounding the problem, after an incorrect data type is implemented in the production environment, it can be difficult to modify data types, as this introduces a change that might break the existing code. Once you go wrong, it is extremely tough to backtrack and choose the right course. It is more likely you will end up with hack upon hack as you attempt to find ways to force the ill-chosen data type to do the job that it was never intended to do.
Having said that, Oracle supports the following groups of data types:
• Character
• Numeric
• Date/time
• ROWID
• LOB
• JSON
• Boolean
• XML
The following sections provide a brief description of each and usage recommendations.
Note Specialized data types, anytype types, spatial types, media types, and user-defined types are not covered in this book. Oracle Database can handle any data type and workload; the documentation can provide more information on ones not included in these chapters.
