BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter