- Introduction
- Availability Features
- Environmental and Hardware Features
- Database Engine
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Database Engine
As with earlier editions of Oracle, the underlying database engine continues to grow and incorporate new features to ensure that developers, administrators, and businesses get the best performance, security, and reliability from the RDBMS. Oracle 9i's database engine supports a number of new features since 8i, and 9i Release 2 has added even more features.
New Datatypes
Oracle 9i supports the native storage of XML inside a table with the new datatype XMLType. XML documents can be stored as XML-compliant documents (object-relational storage) using the XML Document Object Model (DOM), or as character large object types (CLOB), which preserves XML whitespace, carriage returns, and formatting.
Oracle 9i also brings two new date/time datatypes (TIMESTAMP and INTERVAL), which are extensions of the current date/time datatypes. These new datatypes allow for greater accuracy and support for other collation settings.
Databases and applications can now be time zone specific, meaning that formatting is applied defined by the time zone, reducing the need for code to reformat the time. For example, to apply the U.S. Pacific time zone (GMT -8 hours) to a TIMESTAMP datatype you would use the following:
TIMESTAMP '2002-07-23 13:00:00 -8:00'
Alternatively, you could use the named value parameter, which allows you to pass in the name of the time zone that you want:
TIMESTAMP '2002-07-23 13:00:00 US/Pacific'
Flashback Query
As an administrator, I've heard it numerous times: A developer (or user) sheepishly admits, "I just did a DELETE statement without a WHERE clause on the production database!" Breathe deeply, gently pry your hands from around the developer's neck, and relax. Oracle 9i introduces the flashback query, which allows you to look at how data existed at a specific point in time, before that terrible update. You can simply revert the database to a point in time that you want to view, by executing the DBMS_FLASHBACK package.
For example, to set the database back in time 15 minutes (the default retention time), assuming that the current time is 12:45 you would use this:
EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(TIMESTAMP '2002-07-24 12:30:00')
You can then perform a SELECT on the table that has been updated and review the data that has changed.
As you can imagine, this is a very powerful feature, but be warned that it does have some pitfalls. To learn more, see Oracle's online help. For some code samples and how to use the flashback query functionality, as well as an overview of the limitations, see Jonathan Gennick's article Flashback Queries Go Back and Recover.
Online Table Redefinition
Oracle 9i supports a number of online features, tasks that DBAs can administer while the database is still in use by users. One of the better features is online table redefinition. This allows a column to be dropped, a new column to be added, or the underlying datatype to change while the table is still in use!
How many times have developers created changes to applications in development and testing environments, and then expect it to all work well when migrating to production, only to find that Bob changed table X to have an XMLType as the column for storage, without reflecting this change in his build script? Suddenly that small portion of the application breaks, and your next outage window is not until tomorrow morning. By using this feature of 9i, however, you can dynamically change the table's structure, allowing the application to work again, and the users won't ever know!
How does it work? Oracle literally takes a copy of the current table, creates the new table with the changes, and when you're ready, you switch the users to the new table. Cut-over time is very minimal, and has a very low threshold of impact on users.
First-N Rows Optimization
When users are searching for data, they're generally looking only for the first few rows in a database. When you search for information on the web, you probably only look at the first few pages returned. After that you redefine your search and start again.
A new feature of Oracle 9i allows the first few rows (specified by a parameter, such as 10) to be returned very quickly from a large data set (more than 100,000 rows). This results in the user seeing the first rows of the search very quickly, and allows him or her to make the judgment call of sticking with that search and reviewing the rest of the records, or starting a new search.
There are three ways to use this new feature, but I'll focus on building the first-N rows into a SELECT statement. Simply decide how many rows you want to return. Let's assume that you want 20:
SELECT /*+ FIRST_ROWS(20) */ Status FROM QS_CS.ORDER_STATUS_TABLE
This will optimize the query for returning the first 20 rows.
Now add a WHERE clause:
WHERE StatusID < 21
You should see a performance difference in the first 20 rows, compared to retrieving other portions of the result set.
Graphical Explain Plan
As a DBA, it's your responsibility to ensure that the database performance is outstanding, and to make sure that you know what happens inside your database. Until now, getting the information about how a query was executing, what cost it had on a table, and where the bottlenecks were wasn't easy; you practically had to be a psychic. In comes the Graphical Explain Plan, a tool that's an extension of Enterprise Manager, designed to summarize the execution plans that Oracle 9i uses to perform those DML statements. This will allow you to spot developer code that's causing problems, and to resolve those bottlenecks that seem to never go away. The Graphical Explain Plan is available in the optional Tuning Pack for Oracle 9i.
To take a look at the Graphical Explain Plan and what it actually offers, check out Oracle's online demo.