EnterpriseDB: Why All the Data Types
Oracle Tips by Burleson Consulting
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.
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.
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
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.
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.
have already seen some DDL in the discussion of Data Types above.
The CREATE TABLE statement in each section is a DDL statement.
are a limited number of DDL commands that you will use on a regular
basis: CREATE, ALTER, DROP, GRANT, and REVOKE.
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.
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
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
also a firm believer in examples. I will try to provide an example
for the most common uses of these commands.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.