EnterpriseDB: Why All the Data Types Oracle Tips by Burleson Consulting

You may be wondering why we would have multiple data types.  Many beginners think that storing everything as TEXT will save them time and effort.  Actually, the opposite is true.

EnterpriseDB provides a large set of functionality that is data type dependant.  If you subtract TEXT from TEXT what is the result?  If you subtract a number from a date, the result is a new date less than the existing date.  The database recognizes that a NUMBER can be subtracted from a DATE.  How would the database know if a TEXT can be subtracted from a TEXT?

Defining your data types correctly also helps with performance.  If you did want to subtract a TEXT field that stored numeric data from a TEXT field that stored DATE data, you would need to convert the numeric to a NUMBER and the date to a DATE.  You would have to do that to every column in your database whenever you wanted to treat it like the data it really is.  That is a huge performance hit.

It's not just performance.  Can you imagine what your code would look like if you had to hand code conversions every time you accessed any of your columns?  That is a maintenance nightmare.

It is very important to choose the best data type for your data. Sometimes it's easy (money for example) but other times it's not (XML, for example).  It's worth spending a little bit of time to make sure you get it right.

Database Objects and the Data Definition Language

There are many objects that can be referenced in a database:  SEQUENCE, TABLE, CONSTRAINT, INDEX, VIEW, STORED CODE, SYNONYM, ROLE, TRIGGER, and DOMAIN.   There are more objects than these but these are the objects you will use most often and these are the ones I will cover in this section.

In this section, I will provide a brief definition of each of the above listed object types.  We will then use these objects, and I will provide examples of these objects, throughout the remainder of this chapter.  Many of these objects will also be used in later chapters.

Intermingled with the discussion of the various object types, will be a discussion of Data Definition Language (DDL) commands.  DDL is the language that you will use to create database objects and to physically manipulate the physical database environment.

We have already seen some DDL in the discussion of Data Types above.  The CREATE TABLE statement in each section is a DDL statement.

There are a limited number of DDL commands that you will use on a regular basis: CREATE, ALTER, DROP, GRANT, and REVOKE.

In this section, in addition to providing a brief definition of each type of object, I will show you how to use each of the main DDL commands and show you how they operate on the various object types.

This section will NOT show you every possible combination of DDL and object type.  For the full, complete, exact syntax of the DDL commands, see the EnterpriseDB documentation.  This section will give you examples of the more common scenarios and will get you started.

I will list the CREATE syntax for most object types.  I will only list ALTER or DROP syntax if it is significantly different from the CREATE syntax or if it will help simplify the explanation of that command. 

I am also a firm believer in examples.  I will try to provide an example for the most common uses of these commands.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.