What s New in Oracle SQL?

What's New in Oracle SQL?

When we wrote the first edition of this book, Oracle9 i had just come out, and we managed to cover some of the interesting and new features in that release of the database. Now, Oracle Database 10 g has just been released, and there are even more new features to talk about:

• A new, MODEL clause has been added to the SELECT statement, enabling you to write queries that perform spreadsheet-like calculations against multidimensional arrays created from data you select from the database.

• Oracle has added support for using regular expressions from SQL, and with a vengeance. Not only can you use regular expressions to select data, but also to manipulate data in various, useful ways. For example, you can perform regular expression search-and-replace operations. No other database vendor that we know of offers such powerful, regular expression functionality.

• XML is everywhere these days, and that hasn't gone unnoticed in the world of SQL. The ANSI/ISO folk have created the SQL/XML standard, which defines mechanisms for selecting relational data and presenting it in XML form. Oracle supports this standard, which involves several, new SQL functions. Oracle also now supports XML as a native data type.

These are just the big features, which, of course, we cover in this second edition. In addition, we cover many small updates to Oracle SQL, such as the multiset union operators that enable you to perform set operations involving nested table collections.

Finally, we've worked carefully together as a team, not only with each other, but also with our editor, to ensure that all examples in this book are drawn from a single data set. You'll be able to download that data set from this book's catalog page. You can then use it to follow along with our examples.

Objectives of This Book

The single most important objective of this book is to help you harness the power of Oracle SQL to the maximum extent possible. You will learn to:

• Understand the features and capabilities of the SQL language, as implemented by Oracle.

• Use complex SQL features, such as outer joins, correlated subqueries, hierarchical queries, grouping operations, and analytical queries.

• Use DECODE and CASE to implement conditional logic in your SQL queries.

• Write SQL statements that operate against partitions, objects, and collections, such as nested tables and variable arrays.

• Use the new SQL features introduced in Oracle Database 10 g , such as regular expressions and interrow calculations.

• Use best-practices to write efficient, maintainable SQL queries.

One topic that is important to us and many of our readers, but which is not explicitly discussed in this book is SQL tuning. Tuning tips are sprinkled throughout the book, but we do not include a chapter on tuning for the following reasons:

• Tuning is a large topic, and reasonable coverage of SQL tuning would easily double or triple the size of this book.

• There are already many excellent Oracle-specific and general-purpose tuning books on the market, whereas there are very few books (in our opinion, exactly one) that thoroughly explore the feature set of Oracle SQL.

• In many ways, mastery of Oracle's SQL implementation is the most important tool in your tuning toolkit.

With this book under your belt, you will be less likely to write SQL statements that perform badly , and you will be able to employ multiple strategies to rework existing statements.