Have you moved recently? If you moved yourself, you probably found that having more people working
together resulted in reducing the overall time that it took to complete the job. Of course, there was no
doubt some overhead involved in getting everyone to work together, and there may have been a point
where getting even more people involved actually increased the time to complete the job.
Oracle implements a similar type of work sharing called parallelism. Parallelism makes it possible for Oracle
to split up the work of a specific SQL statement among multiple worker tasks to reduce the elapsed time to
complete the SQL operation.
Oracle has had the capability since version 7, although the scope of this functionality has been continuously
improved. Oracle now supports parallel operations for queries, updates, and inserts, as well as for backup,
recovery, and other operations. Parallelism for batch-type jobs like loading data is also supported. Parallel
operations are only available with the Enterprise Edition of Oracle 10g Database.
Oracle implements parallelism on the server, without requiring any particular code to work. This final
section will look at how Oracle implements parallel operations to improve performance.
Managing Parallel Server Processes
The Oracle database has a pool of parallel server processes available for execution. Oracle automatically
manages the creation and termination of these processes. The minimum number of parallel processes is
specified in the initialization parameter PARALLEL_MIN_SERVERS. As more parallel execution processes
are requested, Oracle starts more parallel execution processes as they are needed, up to the value specified
in the PARALLEL_MAX_SERVERS initialization parameters. The default for this parameter is 5, unless the
PARALLEL_AUTOMATIC_TUNING parameter is set to TRUE, in which case the default is set to the value of
the CPU_COUNT parameter times 10.
Friday, January 21, 2011
Using Bind Variables
The previous performance tip centered around how the Oracle database returns data to the user. This next
tip has to do with the exact way that the Oracle database processes SQL statements. Unlike the previous
tip, which did not affect your application code, this area requires that you implement your code in a par-
ticular way to reduce the use of resources and improve the performance of your Oracle database.
Earlier in this chapter, we discussed how Oracle stores SQL statements in the shared pool area of memory.
When the Oracle database receives an SQL statement, it checks the shared pool to see if an optimized execution plan already exists for the statement. If the plan exists in the pool, the plan is simply retrieved from memory, which is much more efficient than reoptimizing the statement. Use of the shared pool helps Oracle to scale for large numbers of users and perform well with any load.
If you have to reoptimize every statement, each statement adds to the overall workload of the target Oracle
database. This means that the overhead on your system increases with the number of statements and that,
eventually, you will run into resource limitations that will decrease performance. If every statement has to
go through the complete cycle of execution, your scalability will be limited.
Fortunately, a real-world application is not a series of unique SQL requests. In fact, most applications use
the same SQL statements over and over again. Theoretically, this would mean that the repeated SQL will
be picked up from the shared pool, which is much less expensive from a resource standpoint. Since Oracle
needs exclusive access to some resources when optimizing an SQL statement, optimizing SQL statements
cannot be done in parallel, so the more optimizations Oracle has to do, the greater the elapsed time for a
query to be processed.
The way to reduce this potential bottleneck is to help your Oracle database to avoid performing hard
parses as much as possible. You can help this to occur by using bind variables to help re-use SQL
statements.
Remember that the method used to identify a statement is a comparison of the hash algorithm created
from the statement. The value of this hash is derived from the characters in the statement.
Consider the following two statements:
SELECT ENAME FROM EMP WHERE EMP_ID = 7
SELECT ENAME FROM EMP WHERE EMP_ID = 5
You can quickly tell that these two statements should use identical optimizer plans. If the execution plan
for the first statement is still in the shared pool, Oracle should use it, right? Unfortunately, Oracle may not
be able to find the plan, since the hash value created by the second statement will very likely be different
from the hash value created by the first statement, based on the different characters.
At this point, bind variables come to the rescue. A bind variable is a placeholder in an SQL statement.
Oracle can process a statement containing a bind variable but can only execute the statement when it
receives the value of the variable.
You identify a bind variable by preceding it with a colon in the SQL statement. The previous SQL statements
could both be represented by the single SQL statement following, which uses a bind variable.
SELECT ENAME FROM EMP WHERE EMP_ID = :n_EmpID
If you use this syntax twice, instead of using the two different SQL statements shown previously, Oracle
will be able to retrieve the execution plan from the shared pool, saving lots of resources.
The statements used to illustrate the use of bind variables previously are not that complex, so you may
doubt how much the use of bind variables could help. But remember that your own SQL statements are
considerably more complex, where the creation of an execution plan could likely be the biggest resource
hog in the entire sequence of SQL processing. Also, remember that not only will your application likely
repeat the same SQL statement over and over again but that this effect is multiplied by the use of your
application by many different users.
Those of you who think of yourself as primarily as programmers and don’t give no never mind about
databases may be rising up in protest at this point. There is some common wisdom that says the code
required to use bind variables, a PreparedStatement call in Java, executes slower than the code to execute
a hard-coded statement, a Statement call in Java. We certainly can’t argue this simple fact (although oth-
ers have), but remember that there is more to performance than simply the time required to execute a call.
The use of bind variables can have an enormous impact on the time Oracle needs to return data to the
user. Since performance is an aggregate, it doesn’t make sense to save time in the execution of a single
call if those savings are going to be outweighed by slower performance from the database.
The bottom line is that you should train yourself to use bind variables whenever you have SQL statements
that only differ in the value of a particular item in the statement and that are repeatedly executed in your
application.
tip has to do with the exact way that the Oracle database processes SQL statements. Unlike the previous
tip, which did not affect your application code, this area requires that you implement your code in a par-
ticular way to reduce the use of resources and improve the performance of your Oracle database.
Earlier in this chapter, we discussed how Oracle stores SQL statements in the shared pool area of memory.
When the Oracle database receives an SQL statement, it checks the shared pool to see if an optimized execution plan already exists for the statement. If the plan exists in the pool, the plan is simply retrieved from memory, which is much more efficient than reoptimizing the statement. Use of the shared pool helps Oracle to scale for large numbers of users and perform well with any load.
If you have to reoptimize every statement, each statement adds to the overall workload of the target Oracle
database. This means that the overhead on your system increases with the number of statements and that,
eventually, you will run into resource limitations that will decrease performance. If every statement has to
go through the complete cycle of execution, your scalability will be limited.
Fortunately, a real-world application is not a series of unique SQL requests. In fact, most applications use
the same SQL statements over and over again. Theoretically, this would mean that the repeated SQL will
be picked up from the shared pool, which is much less expensive from a resource standpoint. Since Oracle
needs exclusive access to some resources when optimizing an SQL statement, optimizing SQL statements
cannot be done in parallel, so the more optimizations Oracle has to do, the greater the elapsed time for a
query to be processed.
The way to reduce this potential bottleneck is to help your Oracle database to avoid performing hard
parses as much as possible. You can help this to occur by using bind variables to help re-use SQL
statements.
Remember that the method used to identify a statement is a comparison of the hash algorithm created
from the statement. The value of this hash is derived from the characters in the statement.
Consider the following two statements:
SELECT ENAME FROM EMP WHERE EMP_ID = 7
SELECT ENAME FROM EMP WHERE EMP_ID = 5
You can quickly tell that these two statements should use identical optimizer plans. If the execution plan
for the first statement is still in the shared pool, Oracle should use it, right? Unfortunately, Oracle may not
be able to find the plan, since the hash value created by the second statement will very likely be different
from the hash value created by the first statement, based on the different characters.
At this point, bind variables come to the rescue. A bind variable is a placeholder in an SQL statement.
Oracle can process a statement containing a bind variable but can only execute the statement when it
receives the value of the variable.
You identify a bind variable by preceding it with a colon in the SQL statement. The previous SQL statements
could both be represented by the single SQL statement following, which uses a bind variable.
SELECT ENAME FROM EMP WHERE EMP_ID = :n_EmpID
If you use this syntax twice, instead of using the two different SQL statements shown previously, Oracle
will be able to retrieve the execution plan from the shared pool, saving lots of resources.
The statements used to illustrate the use of bind variables previously are not that complex, so you may
doubt how much the use of bind variables could help. But remember that your own SQL statements are
considerably more complex, where the creation of an execution plan could likely be the biggest resource
hog in the entire sequence of SQL processing. Also, remember that not only will your application likely
repeat the same SQL statement over and over again but that this effect is multiplied by the use of your
application by many different users.
Those of you who think of yourself as primarily as programmers and don’t give no never mind about
databases may be rising up in protest at this point. There is some common wisdom that says the code
required to use bind variables, a PreparedStatement call in Java, executes slower than the code to execute
a hard-coded statement, a Statement call in Java. We certainly can’t argue this simple fact (although oth-
ers have), but remember that there is more to performance than simply the time required to execute a call.
The use of bind variables can have an enormous impact on the time Oracle needs to return data to the
user. Since performance is an aggregate, it doesn’t make sense to save time in the execution of a single
call if those savings are going to be outweighed by slower performance from the database.
The bottom line is that you should train yourself to use bind variables whenever you have SQL statements
that only differ in the value of a particular item in the statement and that are repeatedly executed in your
application.
Retrieval Performance
The final, and in many ways the most important, event in the SQL processing cycle is the return of data to
the application that requested it. On one level, the speed of this final step is determined by your network,
since the amount of bits that can move from the server to the client is ultimately limited by the amount of
network bandwidth. The effects of this potential limitation cannot be overcome. But you can affect how
much work the Oracle database performs before it starts returning data to the user.
Well, how much work does Oracle have to do before it returns data? You would think the answer to this
question would be self-evident — “As much as it needs to do to get the data.” But how much data does
Oracle need to get before it starts to send data back to the application?
As developers, we tend to think of the end results of an action. Auser requests data, so he or she obviously
must want all of that data to do what he or she has to do. But users tend to be more, shall we say, immediate
in their outlook. For us, performance is the time it takes to complete an SQL operation, such as a query. For a user, performance is how long they wait before something comes back to them.
You can take advantage of this dichotomy by setting the way that Oracle returns data. You can specify
that Oracle should start returning rows to the user as soon as it gets the rows, or you can specify that
Oracle will only start returning rows to the user once it has collected all the rows for the query.
You instruct your Oracle database as to which approach to take by setting a parameter called OPTIMIZER_MODE.
The two settings for OPTIMIZER_MODE that are relevant to this example are ALL_ROWS and FIRST_ROWS, which tell Oracle to only return data once all rows have been fetched or as soon as it can, respectively. You can also use either one of these values as a hint for a particular query.
The best choice for this parameter obviously depends on your application. If a user is unable to do any
work until he or she receives all the data, or if you don’t want the user to do any work until he or she
receives all the data, the ALL_ROWS parameter is the right choice. In applications that typically fetch data
for the user to peruse and possible use, FIRST_ROWS may deliver better perceived performance without
much logical downside. If your application is not retrieving large amounts of data, this particular optimizer
choice shouldn’t really affect performance.
Regardless of the setting of this parameter, there are some times when Oracle will properly wait until it
has retrieved all rows until it returns any rows. One case is when a query includes aggregate values.
Oracle knows that it has to get all the rows before it can calculate aggregate values, so no rows will be
returned until all rows have been retrieved and the calculations performed.
Another case is when you ask for the rows to be returned in sorted order. Normally, Oracle cannot
return the first rows until the sort has been performed, since the sort determines what the first row is,
not the order that the rows are retrieved from the database. The exception to this rule is when the query
has requested a sort order that is already implemented in an index. For instance, a user may request
employee names in alphabetical order based on the last name, and there is an index that sorts the rows
on that criterion. The Oracle database knows that the index has already sorted the rows, so it does not
have to sort them and the first rows can be returned as soon as they are retrieved.
the application that requested it. On one level, the speed of this final step is determined by your network,
since the amount of bits that can move from the server to the client is ultimately limited by the amount of
network bandwidth. The effects of this potential limitation cannot be overcome. But you can affect how
much work the Oracle database performs before it starts returning data to the user.
Well, how much work does Oracle have to do before it returns data? You would think the answer to this
question would be self-evident — “As much as it needs to do to get the data.” But how much data does
Oracle need to get before it starts to send data back to the application?
As developers, we tend to think of the end results of an action. Auser requests data, so he or she obviously
must want all of that data to do what he or she has to do. But users tend to be more, shall we say, immediate
in their outlook. For us, performance is the time it takes to complete an SQL operation, such as a query. For a user, performance is how long they wait before something comes back to them.
You can take advantage of this dichotomy by setting the way that Oracle returns data. You can specify
that Oracle should start returning rows to the user as soon as it gets the rows, or you can specify that
Oracle will only start returning rows to the user once it has collected all the rows for the query.
You instruct your Oracle database as to which approach to take by setting a parameter called OPTIMIZER_MODE.
The two settings for OPTIMIZER_MODE that are relevant to this example are ALL_ROWS and FIRST_ROWS, which tell Oracle to only return data once all rows have been fetched or as soon as it can, respectively. You can also use either one of these values as a hint for a particular query.
The best choice for this parameter obviously depends on your application. If a user is unable to do any
work until he or she receives all the data, or if you don’t want the user to do any work until he or she
receives all the data, the ALL_ROWS parameter is the right choice. In applications that typically fetch data
for the user to peruse and possible use, FIRST_ROWS may deliver better perceived performance without
much logical downside. If your application is not retrieving large amounts of data, this particular optimizer
choice shouldn’t really affect performance.
Regardless of the setting of this parameter, there are some times when Oracle will properly wait until it
has retrieved all rows until it returns any rows. One case is when a query includes aggregate values.
Oracle knows that it has to get all the rows before it can calculate aggregate values, so no rows will be
returned until all rows have been retrieved and the calculations performed.
Another case is when you ask for the rows to be returned in sorted order. Normally, Oracle cannot
return the first rows until the sort has been performed, since the sort determines what the first row is,
not the order that the rows are retrieved from the database. The exception to this rule is when the query
has requested a sort order that is already implemented in an index. For instance, a user may request
employee names in alphabetical order based on the last name, and there is an index that sorts the rows
on that criterion. The Oracle database knows that the index has already sorted the rows, so it does not
have to sort them and the first rows can be returned as soon as they are retrieved.
Receiving Data
Aquery is a request for data. In response to a query, the Oracle database prepares a result set that satisfies
the conditions of the query. An application retrieves the rows of the result set by performing a series of
fetch operations to return rows.
When you are using Java to access Oracle data, the Java API implements a pre-fetch. A pre-fetch returns
a designated number of rows with a fetch command, when needed. By default, the pre-fetch returns 10
rows, but you can change the default for an individual connection programmatically.
When a fetch is first executed for a result set, the return includes 10 rows from the result set. For the next
nine fetch operations, the data is already residing at the client. In this scenario, the 11th fetch result would
retrieve another 10 rows.
the conditions of the query. An application retrieves the rows of the result set by performing a series of
fetch operations to return rows.
When you are using Java to access Oracle data, the Java API implements a pre-fetch. A pre-fetch returns
a designated number of rows with a fetch command, when needed. By default, the pre-fetch returns 10
rows, but you can change the default for an individual connection programmatically.
When a fetch is first executed for a result set, the return includes 10 rows from the result set. For the next
nine fetch operations, the data is already residing at the client. In this scenario, the 11th fetch result would
retrieve another 10 rows.
Submitting SQL Statements
Submitting a statement is a single line of application code, but the Oracle database performs a series of
actions in response to this submission before it responds to the request. The individual actions performed
depend on the type of SQL statement submitted—a data definition language (DDL) statement for creating
or modifying database objects, a write operation (INSERT, UPDATE, or DELETE), or a query using the
SELECT verb. The actions taken for each of these are detailed in the following table.
actions in response to this submission before it responds to the request. The individual actions performed
depend on the type of SQL statement submitted—a data definition language (DDL) statement for creating
or modifying database objects, a write operation (INSERT, UPDATE, or DELETE), or a query using the
SELECT verb. The actions taken for each of these are detailed in the following table.
Thursday, January 20, 2011
Establishing a Cursor
Once a connection is established, the next step is to open a cursor for your SQL statements. Acursor is a
connection to a specific area in the Program Global Area (PGA) that contains information about a specific
SQL statement.
The cursor acts as the intermediary between the SQL statements your application submits and the actions
of the Oracle database as a result of those statements. The cursor holds information about the current state
of the SQL statement, such as the parsed version of the SQL statement. For statements that return multiple
rows of data, the cursor keeps track of where you are in terms of returning rows from the result set. This
important piece of information is also called a cursor, in that the cursor for a particular result set is the
pointer to the current row. But the cursor exists before any rows have been returned for a query, which is
why advancing the cursor to the next row at the start of retrieving data places the cursor on the first row
in the result set.
You don’t necessarily have to explicitly open a cursor to execute an SQL statement, because the Oracle
database can automatically take care of this for you. As with most aspects of application programming,
explicit control over cursor creation and use will give you more options for implementing your logic effi-
ciently. You may want to open more than one cursor to improve the operation of your application. For
instance, if you have an SQL statement that is repeatedly used, you might want to open a cursor for the
statement so that Oracle will not have to re-parse the statement. Of course, the Oracle database uses its
own internal caches to hold parsed versions of SQL statements also, so creation of individual cursors
may not be necessary for optimal performance in many cases.
connection to a specific area in the Program Global Area (PGA) that contains information about a specific
SQL statement.
The cursor acts as the intermediary between the SQL statements your application submits and the actions
of the Oracle database as a result of those statements. The cursor holds information about the current state
of the SQL statement, such as the parsed version of the SQL statement. For statements that return multiple
rows of data, the cursor keeps track of where you are in terms of returning rows from the result set. This
important piece of information is also called a cursor, in that the cursor for a particular result set is the
pointer to the current row. But the cursor exists before any rows have been returned for a query, which is
why advancing the cursor to the next row at the start of retrieving data places the cursor on the first row
in the result set.
You don’t necessarily have to explicitly open a cursor to execute an SQL statement, because the Oracle
database can automatically take care of this for you. As with most aspects of application programming,
explicit control over cursor creation and use will give you more options for implementing your logic effi-
ciently. You may want to open more than one cursor to improve the operation of your application. For
instance, if you have an SQL statement that is repeatedly used, you might want to open a cursor for the
statement so that Oracle will not have to re-parse the statement. Of course, the Oracle database uses its
own internal caches to hold parsed versions of SQL statements also, so creation of individual cursors
may not be necessary for optimal performance in many cases.
Connecting to a Database
The first step in accessing an Oracle database from your application is to establish a connection to the
database. This connection is the path that acts as the path from your client application to a shadow process
in the Oracle database that handles the SQL requests from your application.
Normally, a connection goes from a client machine over a network to a server machine. The connection is
implemented on top of a network protocol, such as TCP/IP. The connection request is actually received by
the Oracle Listener, which listens on a port for connection traffic. Once the connection is established, the
Listener passes requests to the shadow process associated with the connection.
If you are using Java as your program language, the connection will be executed with a driver, which is
software designed to handle the complexities of communication over the network.
A connection to an Oracle database is always created on behalf of a user. Consequently, establishing a
connection requires identifying and authenticating that user with a username and password.
A connection to an Oracle database is referred to as a session. The session is the overriding context for all
SQL interactions that take place. When a connection is either terminated or is lost for any other reason,
the context for that session, including any information in any uncommitted transactions within that session,
is lost.
Every session is supported by a shadow process on the Oracle server. Normally, this means that every
session has its own process. But, as you can imagine, each shadow process uses some server memory
resources, so the scalability of an individual Oracle instance might be limited by the number of sessions
it can support. To address this issue, Oracle has a feature known as shared servers, which was referred to
as multithreaded servers, or MTS, before Oracle9i.
When a connectioncomes into the Listener, it passes the request to a dispatcher. The dispatcher assigns the request to a session that can be shared. Once the request is completed, the session becomes available to service other requests.
Use of shared servers is completely transparent to your application. A single Oracle instance can use a
combination of shared servers and dedicated servers—the instance has different connection identifiers
for a dedicated session or a shared session.
When should you use a shared server? As with most issues involving performance, the answer depends
on the particulars of your particular implementation.
If you are running out of memory because of a large number of connections, shared servers can help to
address the problem by lowering the overall amount of memory required for sessions. Obviously, some
overhead is also involved with the use of a dispatcher and the functions it performs. This overhead can
be balanced against the more limited use of resources required by the shared server architecture.
Typically, a shared server can do the most good for applications that require periodic access to the database,
since the connections used by these applications will not be performing work much of the time, and as such
they are candidates to be shared with other sessions. Abrowsing application would fit this description, while
a heads-down transaction processing application would not. The good news is that you can switch between
using shared sessions and dedicated sessions by simply changing the connection parameters, so it is fairly
easy to test the effect of shared sessions on your overall application performance.
You have to establish a connection to your Oracle database instance before you can send any SQL to the
instance, but you do not necessarily have to create a connection each time you want to use one. You can
reuse connections inside of an application, or a portion of an application, or you can use connection pooling
to maintain a set of connections that a user can grab when he or she needs it. Using a connection manager
with Java
database. This connection is the path that acts as the path from your client application to a shadow process
in the Oracle database that handles the SQL requests from your application.
Normally, a connection goes from a client machine over a network to a server machine. The connection is
implemented on top of a network protocol, such as TCP/IP. The connection request is actually received by
the Oracle Listener, which listens on a port for connection traffic. Once the connection is established, the
Listener passes requests to the shadow process associated with the connection.
If you are using Java as your program language, the connection will be executed with a driver, which is
software designed to handle the complexities of communication over the network.
A connection to an Oracle database is always created on behalf of a user. Consequently, establishing a
connection requires identifying and authenticating that user with a username and password.
A connection to an Oracle database is referred to as a session. The session is the overriding context for all
SQL interactions that take place. When a connection is either terminated or is lost for any other reason,
the context for that session, including any information in any uncommitted transactions within that session,
is lost.
Every session is supported by a shadow process on the Oracle server. Normally, this means that every
session has its own process. But, as you can imagine, each shadow process uses some server memory
resources, so the scalability of an individual Oracle instance might be limited by the number of sessions
it can support. To address this issue, Oracle has a feature known as shared servers, which was referred to
as multithreaded servers, or MTS, before Oracle9i.
When a connectioncomes into the Listener, it passes the request to a dispatcher. The dispatcher assigns the request to a session that can be shared. Once the request is completed, the session becomes available to service other requests.
combination of shared servers and dedicated servers—the instance has different connection identifiers
for a dedicated session or a shared session.
When should you use a shared server? As with most issues involving performance, the answer depends
on the particulars of your particular implementation.
If you are running out of memory because of a large number of connections, shared servers can help to
address the problem by lowering the overall amount of memory required for sessions. Obviously, some
overhead is also involved with the use of a dispatcher and the functions it performs. This overhead can
be balanced against the more limited use of resources required by the shared server architecture.
Typically, a shared server can do the most good for applications that require periodic access to the database,
since the connections used by these applications will not be performing work much of the time, and as such
they are candidates to be shared with other sessions. Abrowsing application would fit this description, while
a heads-down transaction processing application would not. The good news is that you can switch between
using shared sessions and dedicated sessions by simply changing the connection parameters, so it is fairly
easy to test the effect of shared sessions on your overall application performance.
You have to establish a connection to your Oracle database instance before you can send any SQL to the
instance, but you do not necessarily have to create a connection each time you want to use one. You can
reuse connections inside of an application, or a portion of an application, or you can use connection pooling
to maintain a set of connections that a user can grab when he or she needs it. Using a connection manager
with Java
Tuesday, January 18, 2011
The Processing Cycle for SQL
To process an SQL statement you submit to your Oracle database, the database goes through several
phases as part of the execution of the statement:
❑ Connect to the database, to establish communication between your client application and
the Oracle database server.
❑ Create a cursor, to hold the context for an SQL statement.
❑ Submit an SQL statement to the Oracle database for processing, which includes checking
the validity of the statement and potentially creating an optimized execution plan for it.
❑ Receive data back from the Oracle database in response to the submission of a query.
phases as part of the execution of the statement:
❑ Connect to the database, to establish communication between your client application and
the Oracle database server.
❑ Create a cursor, to hold the context for an SQL statement.
❑ Submit an SQL statement to the Oracle database for processing, which includes checking
the validity of the statement and potentially creating an optimized execution plan for it.
❑ Receive data back from the Oracle database in response to the submission of a query.
XML Data Type
Store XML in CLOB XMLType
With the CLOB XMLType you can store XML documents as a Character Large OBject.Using the CLOB XMLType storage there is no need for validation of the XML.
The XML does have to be well-formed, and it's optional to validate the XML document against an XML schema or DTD yourself.
With this type Oracle implicitly stores the XML in a CLOB column. The original XML is preserved including whitespace All restrictions on a CLOB column apply also to this XMLType.
Use CLOB storage for XMLType when:
- you are interested in storing and retrieving the whole document.
- you do not need to perform piece-wise updates on XML documents.
Code examples:
create table XmlTest( id number
, data_xml XMLType)
XmlType data_xml STORE AS CLOB;
insert into XmlTest(id, data_xml) values
( 1
, XMLType('<company>
<department>
<id>10</id>
<name>Accounting</name>
</department>
</company>'));
Date Data Types
The third major category of data types is the DATE data type. As with numeric data types, Oracle stores all
dates in a single internal representation. However, the format used for entering and returning dates is
determined by the NLS_DATE_FORMAT value for the instance. You can set this value in the initialization file
for the instance, or you can change it with an ALTER SESSION command for an individual database session.
The default format used by into Oracle for dates is DD-MMM-YY HH:MI:SS, where DD is a two-digit
representation of the day number, MMM is a three-character representation of the month name, and YY
is a two-digit representation of the year. HH, MI, and SS are two-digit representations of hours, minutes,
and seconds, respectively. If you do not specify a time portion of a date, these values default to 0.
Oracle does store dates with four-digit centuries, although you have to use a date format other than the
default format to enter and display four-digit centuries.
The three date data types are as follows:
❑ DATE—Includes the day, month, year, hour, minute and second specification.
❑ TIMESTAMP(n)—Can extend the standard DATE data type with fractional seconds. The number
of decimal places for the fractional seconds component is indicated with the n variable.
❑ TIMESTAMP(n) WITH [LOCAL] TIMEZONE—Includes the time zone of either the database server
or the client (when using the LOCAL keyword).
You can perform simple arithmetic on date values, where integers represent whole days and fractions
represent portions of days. If COLTIME contains a value of December 31, 2003, at 6 P.M., COLTIME + 1
would equal January 1, 2004, at 6 P.M., and COLTIME + .5 would equal January 1, 2004, at 6 A.M.
Oracle9i introduced two new date data types used for date arithmetic.
❑ INTERVAL YEAR TO MONTH — Can store an interval of years and months
❑ INTERVAL DAY TO SECOND — Can store an interval of days, hours and seconds
You can use the TO_DATE function to covert character values into dates. Oracle includes a set of extended date
functions that allow you to manipulate dates, such as creating a date value from character representations of
days, months, and years.
dates in a single internal representation. However, the format used for entering and returning dates is
determined by the NLS_DATE_FORMAT value for the instance. You can set this value in the initialization file
for the instance, or you can change it with an ALTER SESSION command for an individual database session.
The default format used by into Oracle for dates is DD-MMM-YY HH:MI:SS, where DD is a two-digit
representation of the day number, MMM is a three-character representation of the month name, and YY
is a two-digit representation of the year. HH, MI, and SS are two-digit representations of hours, minutes,
and seconds, respectively. If you do not specify a time portion of a date, these values default to 0.
Oracle does store dates with four-digit centuries, although you have to use a date format other than the
default format to enter and display four-digit centuries.
The three date data types are as follows:
❑ DATE—Includes the day, month, year, hour, minute and second specification.
❑ TIMESTAMP(n)—Can extend the standard DATE data type with fractional seconds. The number
of decimal places for the fractional seconds component is indicated with the n variable.
❑ TIMESTAMP(n) WITH [LOCAL] TIMEZONE—Includes the time zone of either the database server
or the client (when using the LOCAL keyword).
You can perform simple arithmetic on date values, where integers represent whole days and fractions
represent portions of days. If COLTIME contains a value of December 31, 2003, at 6 P.M., COLTIME + 1
would equal January 1, 2004, at 6 P.M., and COLTIME + .5 would equal January 1, 2004, at 6 A.M.
Oracle9i introduced two new date data types used for date arithmetic.
❑ INTERVAL YEAR TO MONTH — Can store an interval of years and months
❑ INTERVAL DAY TO SECOND — Can store an interval of days, hours and seconds
You can use the TO_DATE function to covert character values into dates. Oracle includes a set of extended date
functions that allow you to manipulate dates, such as creating a date value from character representations of
days, months, and years.
Numeric Data Types
the Oracle database stores all numbers in the same internal format. As a
developer, you may define numeric values in your applications with all sorts of numeric characteristics,
such as DECIMAL, INT, or SMALLINT, but the storage of those values is limited by the data types of the
columns in the database.
NUMBERThe NUMBER data type is the basic numeric data type used in the Oracle database. Data with this data
type is stored with up to 38 digits of precision. You can specify the precision and the scale of a NUMBER
column with the syntax:
NUMBER(p,s)
where p is the precision of the column and s is the scale of the column. p can be any number up to 38.
The s variable can be either positive, which indicates the number of digits to the right of the decimal
point that will be stored, or negative, which will round the number up by the number of places to the
left of the decimal point. If you do not define a scale, the scale is assumed to be 0. The following table
shows the effect of precision and scale on a value.
developer, you may define numeric values in your applications with all sorts of numeric characteristics,
such as DECIMAL, INT, or SMALLINT, but the storage of those values is limited by the data types of the
columns in the database.
NUMBERThe NUMBER data type is the basic numeric data type used in the Oracle database. Data with this data
type is stored with up to 38 digits of precision. You can specify the precision and the scale of a NUMBER
column with the syntax:
NUMBER(p,s)
where p is the precision of the column and s is the scale of the column. p can be any number up to 38.
The s variable can be either positive, which indicates the number of digits to the right of the decimal
point that will be stored, or negative, which will round the number up by the number of places to the
left of the decimal point. If you do not define a scale, the scale is assumed to be 0. The following table
shows the effect of precision and scale on a value.
Character Data Types
The Oracle database supports a number of different data types for storing character data. All of these data
types interpret data entered and retrieved as character values, but each of the data types has additional
characteristics as well.
CHARCHAR columns can have a length specification after the data type or are assigned the default length of 1.
A CHAR column will always use the assigned space in the database. If a column is defined as CHAR(25),
every instance of that column will occupy 25 bytes in the database. If the value entered for that column
does not contain 25 bytes, it will be padded with spaces for storage and retrieval.
VARCHAR2 and VARCHARVARCHAR2 columns also can have a length specification, but this data type only stores the number of
characters entered by the user. If you define a column as VARCHAR2(25) and the user only enters 3 char-
acters, the database will only store those three characters. Because of this distinction, there may be some
issues with comparison between a CHAR column and a VARCHAR2 column.
The following SQL code snippet adds the same value, ‘ABC’, to two columns: ABCCHAR, which is described
as a CHAR column with 20 characters, and ABCVARCHAR, which is described as a VARCHAR2 column with 20
characters.
INSERT INTO TEST_TABLE COLUMNS(ABCCHAR, ABCVARCHAR) (‘ABC’, ‘ABC’);
IF ABCCHAR = ABCVARCHAR
This final test will fail, since it is comparing ABCCHAR, which has a value of “ABC “ with ABCVARCHAR,
which has a value of “ABC”. These comparison problems could be resolved by truncating the ABCCHAR
column, but it is much easier to just avoid them by using VARCHAR2 for normal character columns. The
VARCHAR2 data type will also save storage space, which improves overall performance.
The VARCHAR data type is synonymous with the VARCHAR2 data type, but Oracle recommends using
VARCHAR2 to protect against possible future changes in the behavior of the VARCHAR data type.
NCHAR and NVARCHAR2The Oracle database can store character data from many different languages, including Chinese or Japanese,
which require 2 bytes of storage for each character. These double-byte languages use the NCHAR and
NVARCHAR2 data types, which allow for the extended storage needed for the characters in these languages.
Prior to Oracle9i, using these data types would automatically indicate that each character required 2
bytes of storage. With Oracle9i, you can specify the storage for a column either in bytes or as a number
of characters. If you define a column with characters, Oracle automatically allocates the appropriate
number of bytes for storage of the data.
LONGThe CHAR and NCHAR data types have a limit of 2,000 characters, while the VARCHAR, VARCHAR2, and
NVARCHAR data types have a limit of 4,000 characters. The LONG data type allows you to enter up to 2 GBs
of data in a single column. The use of the LONG data type has been superceded by the CLOB and NCLOB
data types described next, since they have fewer restrictions on their use than the older LONG data type.
BLOB, CLOB, and NCLOBLike the LONG data type, these three data types, collectively referred to a LOB data types, are designed to
hold more that 4,000 characters. The BLOB data type holds binary data, the CLOB data type holds charac-
ter data, and the NCLOB data type holds double-byte information, just as the NVARCHAR2 data type does.
With Oracle10g, a single CLOB or NCLOB data type can hold up to 128 TBs (!) of data; prior to that release,
the same data types could hold up to 4 GBs of data.
The data for a column of this data type is not stored in an actual row; instead, the column holds a pointer
to the location of the data. LOB columns can participate in transactions, but if you want to manipulate the
data in one of these columns, you have to use specific calls in the PL/SQL built-in package DBMS_LOB. The
use of this package is beyond the scope of this book, since many readers may not be using these long data
types in their applications, but it is covered in the Oracle documentation set.
BFILEThe BFILE data type allows you to have a column that points to a storage location outside of the Oracle
database’s internal storage. You can also attain some of this functionality by using external tables, which
were introduced in Oracle9i. Both BFILEs and external tables can only be used for reading data, and
BFILEs cannot be used in transactions.
types interpret data entered and retrieved as character values, but each of the data types has additional
characteristics as well.
CHARCHAR columns can have a length specification after the data type or are assigned the default length of 1.
A CHAR column will always use the assigned space in the database. If a column is defined as CHAR(25),
every instance of that column will occupy 25 bytes in the database. If the value entered for that column
does not contain 25 bytes, it will be padded with spaces for storage and retrieval.
VARCHAR2 and VARCHARVARCHAR2 columns also can have a length specification, but this data type only stores the number of
characters entered by the user. If you define a column as VARCHAR2(25) and the user only enters 3 char-
acters, the database will only store those three characters. Because of this distinction, there may be some
issues with comparison between a CHAR column and a VARCHAR2 column.
The following SQL code snippet adds the same value, ‘ABC’, to two columns: ABCCHAR, which is described
as a CHAR column with 20 characters, and ABCVARCHAR, which is described as a VARCHAR2 column with 20
characters.
INSERT INTO TEST_TABLE COLUMNS(ABCCHAR, ABCVARCHAR) (‘ABC’, ‘ABC’);
IF ABCCHAR = ABCVARCHAR
This final test will fail, since it is comparing ABCCHAR, which has a value of “ABC “ with ABCVARCHAR,
which has a value of “ABC”. These comparison problems could be resolved by truncating the ABCCHAR
column, but it is much easier to just avoid them by using VARCHAR2 for normal character columns. The
VARCHAR2 data type will also save storage space, which improves overall performance.
The VARCHAR data type is synonymous with the VARCHAR2 data type, but Oracle recommends using
VARCHAR2 to protect against possible future changes in the behavior of the VARCHAR data type.
NCHAR and NVARCHAR2The Oracle database can store character data from many different languages, including Chinese or Japanese,
which require 2 bytes of storage for each character. These double-byte languages use the NCHAR and
NVARCHAR2 data types, which allow for the extended storage needed for the characters in these languages.
Prior to Oracle9i, using these data types would automatically indicate that each character required 2
bytes of storage. With Oracle9i, you can specify the storage for a column either in bytes or as a number
of characters. If you define a column with characters, Oracle automatically allocates the appropriate
number of bytes for storage of the data.
LONGThe CHAR and NCHAR data types have a limit of 2,000 characters, while the VARCHAR, VARCHAR2, and
NVARCHAR data types have a limit of 4,000 characters. The LONG data type allows you to enter up to 2 GBs
of data in a single column. The use of the LONG data type has been superceded by the CLOB and NCLOB
data types described next, since they have fewer restrictions on their use than the older LONG data type.
BLOB, CLOB, and NCLOBLike the LONG data type, these three data types, collectively referred to a LOB data types, are designed to
hold more that 4,000 characters. The BLOB data type holds binary data, the CLOB data type holds charac-
ter data, and the NCLOB data type holds double-byte information, just as the NVARCHAR2 data type does.
With Oracle10g, a single CLOB or NCLOB data type can hold up to 128 TBs (!) of data; prior to that release,
the same data types could hold up to 4 GBs of data.
The data for a column of this data type is not stored in an actual row; instead, the column holds a pointer
to the location of the data. LOB columns can participate in transactions, but if you want to manipulate the
data in one of these columns, you have to use specific calls in the PL/SQL built-in package DBMS_LOB. The
use of this package is beyond the scope of this book, since many readers may not be using these long data
types in their applications, but it is covered in the Oracle documentation set.
BFILEThe BFILE data type allows you to have a column that points to a storage location outside of the Oracle
database’s internal storage. You can also attain some of this functionality by using external tables, which
were introduced in Oracle9i. Both BFILEs and external tables can only be used for reading data, and
BFILEs cannot be used in transactions.
Monday, January 17, 2011
Data Organization
Tablespaces
The tablespace is another logical organizational unit used by the Oracle database, which acts as the
intermediary between logical tables and physical storage. Every table or index, when created, is placed
into a tablespace. Atablespace can contain more than one table or index, or the table or index can be
partitioned across multiple tablespaces, as described later in the section on partitions. A tablespace maps
to one or more files, which are the physical entities used to store the data in the tablespace. A file can
only belong to one tablespace. A tablespace can contain row data or index data, and a table and an index
for a table can be in different tablespaces.
Atablespace has a number of attributes, such as the block size used by the tablespace. This block size is the
smallest amount of data retrieved by a single I/O (input/output) operation by the Oracle database. Your
database administrator (hopefully) selected an appropriate block size for a tablespace, which depends on a
wide range of factors, including the characteristics of the data and the block size of the underlying operating
system.
intermediary between logical tables and physical storage. Every table or index, when created, is placed
into a tablespace. Atablespace can contain more than one table or index, or the table or index can be
partitioned across multiple tablespaces, as described later in the section on partitions. A tablespace maps
to one or more files, which are the physical entities used to store the data in the tablespace. A file can
only belong to one tablespace. A tablespace can contain row data or index data, and a table and an index
for a table can be in different tablespaces.
Atablespace has a number of attributes, such as the block size used by the tablespace. This block size is the
smallest amount of data retrieved by a single I/O (input/output) operation by the Oracle database. Your
database administrator (hopefully) selected an appropriate block size for a tablespace, which depends on a
wide range of factors, including the characteristics of the data and the block size of the underlying operating
system.
Prior to Oracle9i, you could only have a single block size for an entire database. With Oracle9i, multiple
block sizes are supported within a single database, although each block size must have its own data
buffers in the SGA.
block sizes are supported within a single database, although each block size must have its own data
buffers in the SGA.
A tablespace is the basic administrative unit in an Oracle database. You can take a tablespace online or
offline (that is, make it available or unavailable through the Oracle database instance) or back up and
recover a tablespace. You can make a tablespace read-only to prevent write activity to the tablespace.
You can also use a feature called transportable tablespaces as a fast way to move data from one database
to another. The normal way to take data out of one Oracle database and move it to another Oracle
database is to export the data and then import the data. These processes operate by reading or writing
each row. A transportable tablespace allows you to move the entire tablespace by simply exporting
descriptive information about the tablespace from the data dictionary. Once you have exported this
information, you can move the files that contain the tablespace and import the descriptive information
into the data dictionary of the destination database (this is described in detail in Chapter 6, “The Oracle Data
Dictionary.” This method of moving data is significantly faster than importing and exporting data.
offline (that is, make it available or unavailable through the Oracle database instance) or back up and
recover a tablespace. You can make a tablespace read-only to prevent write activity to the tablespace.
You can also use a feature called transportable tablespaces as a fast way to move data from one database
to another. The normal way to take data out of one Oracle database and move it to another Oracle
database is to export the data and then import the data. These processes operate by reading or writing
each row. A transportable tablespace allows you to move the entire tablespace by simply exporting
descriptive information about the tablespace from the data dictionary. Once you have exported this
information, you can move the files that contain the tablespace and import the descriptive information
into the data dictionary of the destination database (this is described in detail in Chapter 6, “The Oracle Data
Dictionary.” This method of moving data is significantly faster than importing and exporting data.
The 10g release of the Oracle database includes the ability to use transportable tablespaces between
Oracle databases on different operating system platforms.
Oracle databases on different operating system platforms.
Segments and Extents
Individual objects in the database are stored in segments, which are collections of extents. Data blocks are
stored in an extent. An extent is a contiguous piece of disk storage. When Oracle writes data to its data
files, it writes each row to a data block that has space available for the data. When you update information,
Oracle tries to write the new data to the same data block. If there is not enough space in that block, the
updated data will be written to a different block, which may be in a different extent.
If there are no extents that have enough space for new information, the Oracle database will allocate a
new extent. You can configure the number and size of extents initially and subsequently allocated for
a tablespace.
stored in an extent. An extent is a contiguous piece of disk storage. When Oracle writes data to its data
files, it writes each row to a data block that has space available for the data. When you update information,
Oracle tries to write the new data to the same data block. If there is not enough space in that block, the
updated data will be written to a different block, which may be in a different extent.
If there are no extents that have enough space for new information, the Oracle database will allocate a
new extent. You can configure the number and size of extents initially and subsequently allocated for
a tablespace.
Partitions
As mentioned previously, you can use partitions to spread data across more than one tablespace. Apartition
is a way to physically segregate the data in a table or index based on a value in the table or index. There are
four ways you can partition data in an Oracle database:
❑ Range partitioning, which divides data based on a range of values, such as putting customers
with a last name starting with the letters A–G in one partition, the customers with last names
starting with the letters H–M in another partition, and so on.
❑ Hash partitioning, which divides data based on the result of a hashing algorithm. Hash parti-
tioning is used when range partitioning would result in unbalanced partitions. For instance, if
you are partitioning a table based on a serial transaction identifier, the first partition will com-
pletely fill before the second partition is used. A hash algorithm can spread data around more
evenly, which will provide greater benefits from partitioning.
❑ Composite partitioning, which uses a hash algorithm within each specific range partion.
❑ List partitioning, which partitions based on a list of values. This type of partitioning is useful when a
logical group, such as a list of states in a region, cannot be easily specified with a range description.
is a way to physically segregate the data in a table or index based on a value in the table or index. There are
four ways you can partition data in an Oracle database:
❑ Range partitioning, which divides data based on a range of values, such as putting customers
with a last name starting with the letters A–G in one partition, the customers with last names
starting with the letters H–M in another partition, and so on.
❑ Hash partitioning, which divides data based on the result of a hashing algorithm. Hash parti-
tioning is used when range partitioning would result in unbalanced partitions. For instance, if
you are partitioning a table based on a serial transaction identifier, the first partition will com-
pletely fill before the second partition is used. A hash algorithm can spread data around more
evenly, which will provide greater benefits from partitioning.
❑ Composite partitioning, which uses a hash algorithm within each specific range partion.
❑ List partitioning, which partitions based on a list of values. This type of partitioning is useful when a
logical group, such as a list of states in a region, cannot be easily specified with a range description.
Partitions are defined when you create a table or an index. Since each partition of a table can be placed in
a different tablespace, you can perform maintenance on a single partition, such as backup and recovery
or moving tablespaces.
a different tablespace, you can perform maintenance on a single partition, such as backup and recovery
or moving tablespaces.
Partitions can also contribute to the overall performance of your Oracle database. The Oracle query optimizer is aware of the use of partitions, so when it creates a plan to execute a query, the plan will skip partitions that have been eliminated from the query with selection criteria. In addition, partitions can be in different tablespaces, which can be in different parts of your disk storage, which can reduce disk head contention during retrievals.
Real Application Clusters
As you know, a single server can have one or more CPUs within it. You can also use a technique called
clustering to combine more than one server into a larger computing entity. The Oracle database has sup-
ported the ability to cluster more than one instance into an single logical database for over 15 years.
Oracle combines multiple instances into a cluster. Since Oracle9i, Oracle has called these clusters Real Application Clusters, commonly referred to as RAC.
As Figure 1-2 shows, RAC instances share a common source of data, which allows RAC to provide
greater database horsepower for scalability while also providing higher levels of availability.
One of the best features of RAC is that you, as an application developer, don’t even have to think about
it, since a RAC implementation uses the same API and SQL that a single instance uses. You also don’t
have to worry about whether your Oracle application will be deployed to a single instance or a cluster
when you are designing and creating your application, since there are virtually no specific performance
considerations that only affect a RAC installation. As a developer, you don’t really have to know anything
more about Real Application Clusters.
clustering to combine more than one server into a larger computing entity. The Oracle database has sup-
ported the ability to cluster more than one instance into an single logical database for over 15 years.
Oracle combines multiple instances into a cluster. Since Oracle9i, Oracle has called these clusters Real Application Clusters, commonly referred to as RAC.
As Figure 1-2 shows, RAC instances share a common source of data, which allows RAC to provide
greater database horsepower for scalability while also providing higher levels of availability.
it, since a RAC implementation uses the same API and SQL that a single instance uses. You also don’t
have to worry about whether your Oracle application will be deployed to a single instance or a cluster
when you are designing and creating your application, since there are virtually no specific performance
considerations that only affect a RAC installation. As a developer, you don’t really have to know anything
more about Real Application Clusters.
Subscribe to:
Posts (Atom)