 |
|
Overview of Oracle SQL Extensions
Oracle Tips by Burleson Consulting |
This chapter will investigate some of the SQL
extensions that are unique to Oracle. While Oracle SQL standard
adheres to the ANSI standard for the SQL languages, Oracle has added
numerous features to improve the productivity of SQL. While many of
these features are a significant departure from the ANSI standard
for SQL, there are numerous Oracle-centric extensions to SQL that
greatly enhance the usability of Oracle SQL as a development
language. These SQL extensions can be categorized into three general
areas:
-
In-line Views – In an exciting
departure from the ANSI standard, Oracle allows queries to be
substituted in the place of table names in the FROM clause. This
non-ANSI extension to SQL is extremely useful when you need to
compare ranges of summed table values in a single query.
-
Built-in functions (BIFs) – Oracle
offers a wealth of built-in functions that will transform the
display format of column values. These BIFs are most often used to
transform DATE datatypes and to manipulate character columns.
-
Object-oriented extensions –
Starting with Oracle8, Oracle enhanced SQL to allow for the use of
abstract datatypes, nested tables, and repeating items within
table columns.
Each of these areas
improve the functionality of Oracle SQL but they also impact the way
that Oracle services the execution plans for these classes of
statements. For each of these areas we will explore the syntax
change to Oracle SQL and look at how the Oracle professional can
tune the statements for maximum efficiency. Let’s begin by
looking at in-line views.
Oracle in-line
views
In a radical departure from the SQL standard,
Oracle allows a query to be substituted in the place of a table name
in the SQL from clause. This is a fascinating extension to
SQL because it allows for queries to be formed in a variety of
exotic ways. It is also mind-boggling because it is very difficult
to imagine a result set being treated as a table name in an SQL
from clause.
To see how this works, let’s start with a
simple example. Below are two examples of a simple SQL query, both
of which count the number of customers in the Southern region. The
first query uses standard SQL.
select
count(*)
from
customer
where
region= ‘south’;
This same query can be written with an in-line view as
shown below.
select
count(*)
from
(select * from customer where region= ‘south’)
;
A common use for in-line views in Oracle SQL is to
simplify complex queries by removing join operations and condensing
several separate queries into a single query. The best example of
the in-line view is the common Oracle Remote DBA script that is used to
show the amount of free space and used space within all Oracle
tablespaces. Let’s take a close look at this SQL to see how it
works. Carefully note that the from clause in this SQL query
specifies two sub-queries that perform summations and grouping from
two views, Remote DBA_data_files, and Remote DBA_free_space.
tsfree.sql
column
"Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
colimn "Total MB" format 99,999,999
select
fs.tablespace_name
"Tablespace",
(df.totalspace - fs.freespace)
"Used MB",
fs.freespace
"Free MB",
df.totalspace
"Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
Remote DBA_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
Remote DBA_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
This SQL quickly compares the sum of the total
space within each tablespace to the sum of the free space within
each tablespace. Here is a sample of the output:
SQL> @tsfree
Tablespace
Used MB Free MB Total MB
Pct. Free
------------- ----------- ----------- ----------- ----------
RANNOD
6 44
50 88
RANNOX
5 45
50 90
RBOOKX
5 0
5 0
SGROUPD
2 8
10 80
SGROUPX
2 8
10 80
BRBS
68 32
100 32
RDRUSERD
2 18
20 90
Basically, this query
needs to compare the sum of total space within each tablespace with
the sum of the free space within each tablespace. In ANSI standard
SQL, it is quite difficult to compare two result sets that are
summed together in a single query. (Figure 1) Without the use
of an in-line view, several separate SQL queries would need to be
written, one to compute the sums from each view and another to
compare the intermediate result sets.
Figure 1: Comparing the sums of two grouped
queries with SQL
This use of in-line views becomes even more
convoluted when we remember that the Remote DBA_free_space and
Remote DBA_data_files views are built upon underlying internal Oracle
structures. Regardless of the complexity, the tsfree.sql
script runs very quickly to get the tablespace report. The execution
plan for this simple query is mind-boggling as shown in the listing
below. We will learn about interpreting the output from Oracle
execution plans in Chapter 4, but this listing is shown so you can
appreciate the complexity of the Oracle optimizer in determining the
best execution plan for this query. Obviously, the Oracle optimizer
is performing a very sophisticated analysis of the query to arrive
at this access method.
OPERATION
--------------------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
819
MERGE JOIN
1
VIEW
1
SORT
GROUP BY
1
VIEW
Remote DBA_FREE_SPACE
1
UNION-ALL
1
NESTED LOOPS
1
NESTED LOOPS
1
TABLE ACCESS
FULL
TS$
1
TABLE
ACCESS
CLUSTER
FET$
2
INDEX
UNIQUE SCAN
I_FILE2
2
NESTED LOOPS
2
NESTED LOOPS
1
TABLE ACCESS
FULL
TS$
1
FIXED TABLE
FIXED INDEX #1
X$KTFBFE
2
INDEX
UNIQUE SCAN
I_FILE2
2
SORT
JOIN
2
VIEW
1
SORT
GROUP BY
1
VIEW
Remote DBA_DATA_FILES
1
UNION-ALL
1
NESTED LOOPS
1
NESTED LOOPS
1
FIXED TABLE
FULL
X$KCCFN
1
TABLE ACCESS
BY INDEX ROWID
FILE$
2
INDEX
UNIQUE SCAN
I_FILE1
1
TABLE ACCESS
CLUSTER
TS$
2
INDEX
UNIQUE SCAN
I_TS#
1
NESTED LOOPS
2
NESTED LOOPS
1
NESTED LOOPS
1
FIXED TABLE
FULL
X$KCCFN
1
TABLE ACCESS
BY INDEX ROWID
FILE$ 2
INDEX
UNIQUE SCAN
I_FILE1
1
FIXED TABLE
FIXED INDEX #1
X$KTFBHC
2
TABLE ACCESS
CLUSTER
TS$
2
INDEX
UNIQUE SCAN
I_TS#
1
Again, it is not necessary to fully understand this
execution plan other than to appreciate all of the complex work that
the SQL optimizer has done to formulate the best execution plan for
this query.
Next, let’s look at another class of extensions to
Oracle SQL that has been with us since the earliest release of
Oracle. By using built-in functions, Oracle SQL has been extended to
allow for the easy manipulation of column data.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
