Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Re-writing Subqueries for Faster Performance

As mentioned in Chapter 19, subqueries can often be re-written to use a standard outer join, resulting in faster performance.  AS we may know, an outer join uses the plus sign (+) operator to tell the Oracle database to return all non-matching rows with NULL values.  Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a subquery.

select
   b.book_key
from
   book  b,
   sales s
where
   b.book_key = s.book_key(+)
and
   s.book_key IS NULL  
;

We will compare the execution plans for these types of queries in more detail in a later chapter, but for now, be aware that we can often re-write subqueries to improve the speed of the query and the resource demands on the Oracle database.

Next, let’s take a look at another class of subqueries where we place a subquery inside the FROM clause of the outer query.

2 – In-line views (subqueries in the FROM clause)

With an in-line view, an SQL statements is embedded inside the FROM clause of the SQL statement, just as if it were a table name.

col "Tablespace" for a13
col "Used MB"    for 99,999,999
col "Free MB"    for 99,999,999
col "Total MB"   for 99,999,999 

select
   df.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 dba_data_files
      group by tablespace_name) df,
   (select tablespace_name,
        round(sum(bytes) / 1048576) FreeSpace
      from dba_free_space
      group by tablespace_name) fs
where
   df.tablespace_name = fs.tablespace_name(+)
;

This is an SQL script that queries the data dictionary to see the amount of free space within the database tablespace in Oracle.  We will be using this script in  as a tool for Oracle database administration.

Note that there are no table names in the FROM clause.  Instead of table names, there are two subqueries, one against the dba_data_files view and another against the dba_free_space view.  These subqueries are given the aliases df and fs, and these aliases are used in the SELECT clause to get the desired columns.

In-line views are a powerful component of SQL because they allow us to combine several SQL queries into a single statement.

To see how this works, copy the above code and run it against your Oracle database.  You should see output similar to this:

SQL> @tsfree 

Tablespace        Used MB     Free MB    Total MB  Pct. Free                   
------------- ----------- ----------- ----------- ----------                   
CWMLITE                 6          14          20         70                   
DRSYS                   8          12          20         60                    EXAMPLE               153           0         153          0                    INDX                    0          25          25        100                    SYSTEM                240          85         325         26                    TOOLS                   7           3          10         30                    UNDOTBS                 1         199         200        100          USERS                   1          24          25         96                   
 

Next, let’s examine a new type of query that was introduced in Oracle 10g called the scalar subquery.  With a scalar subquery, subqueries can be placed inside the SELECT statement.

Here is another example from the sample database. This query returns each employee and their contribution to the total company salary as a percent.  The subquery returns the total company salary. 

While this approach is handy, this type query is extremely inefficient because a Cartesian product is formed since the subquery result must be joined with each row in emp.

select
   e.emp_last_name,
   to_char(round((emp_salary/t.totsal)*100,1),'99.9')||'%'
from
   emp e,
   (select sum(emp_salary) totsal from emp) t

EMP_LAST_NAME                  TO_CHA                                          
------------------------------ ------                                          
king                            18.9%                                           
jackson                          7.0%                                          
korn                             5.6%                                          
linus                            9.0%                                           
tokheim                         12.6%                                          
levender                         2.8%                                          
johnson                          6.2%                                          
baker                           10.2%                                          
coleman                         14.6%                                          
brannigan                       13.3%


Note that we can get the same result much more efficiently by replacing the in-line view with a temporary table:

create table t1 as
select sum(emp_salary) totsal from emp; 

select
   e.emp_last_name,
   to_char(round((emp_salary/t.totsal)*100,1),'99.9')||'%'
from
   emp e,
   t1  t
;


EMP_LAST_NAME                  TO_CHA                                          

------------------------------ ------                                          
king                            18.9%                                          
jackson                          7.0%                                          
korn                             5.6%                                          
linus                            9.0%                                          
tokheim                         12.6%                                           
levender                         2.8%                                          
johnson                          6.2%                                          
baker                           10.2%                                           
coleman                         14.6%                                          
brannigan                       13.3%     


At this pint in the course it is not necessary to fully appreciate the performance improvements of this re-write and this topic will be covered extensively in a later tutorial.  For now, just remember that multiple queries with temporary tables will often run faster than a single query with an in-line view. 

3 - Scalar Subqueries (subqueries in the SELECT clause)

Scalar subqueries allow us to place individual queries inside the SELECT clause, thereby combining many queries into a single query.

In the example below, we compute the total sales for all stores, the number of stores, the highest number of books sold for any store, the least number of books sold for any store, and the average number of books sold for all stores.

select
   (select sum(quantity) from sales) tot_sales,
   (select count(*) from store)      nbr_stores,
   (select max(quantity) from sales) most_sales,
   (select min(quantity) from sales) least_sales,
   (select avg(quantity) from sales) average_sales
from
   dual;

TOT_SALES NBR_STORES MOST_SALES LEAST_SALES AVERAGE_SALES                     
---------- ---------- ---------- ----------- -------------                     
    110550         10       9900          10        1105.5  

Using the dual table

Note that the outer query references the dummy table called “dual”  the dual table is used in Oracle when you need to run SQL that does not logically have a table name.  For example, we can select our current user ID from dual and the current date from dual.

select user from dual; 

USER
------------------------------                                                  PUBS                                                                           

select sysdate from dual; 

SYSDATE
---------                                                                      
27-MAY-02                                                                        

We can also use the dual table for date arithmetic. This last query displays the day of the week for any date in the past 1,000 years. To see the day of the week that you were born, copy this query into your c”\Burleson directory, add your birth date, and run the query see what day of the week you were born on.

select
      to_char(to_date('25-MAR-1956','dd-mon-yyyy'),'day')
from dual;
 

TO_CHAR(T                                                                      
---------                                                                      
sunday     


SQL Aggregation

The SQL sum function is used whenever we want to sum a series of values within the Oracle database.  The basic format of a summary SQL statement is as follows:

select
   column1,
   sum(column2)
from
   table1,
   table2
where
   table1.key = table2.key
group by
   column1;


Note that a sum is most often used when we join multiple tables together and we want to sum a numeric data column.  There is also a rule in SQL that all columns preceding the sum must be included in the GROUP BY clause of the SQL statement.

Here is a simple example from your sample database.  Suppose that we want the names of all of the job descriptions and the sum of all salaries for each job:

select
   job_name,
   sum(emp_salary)
from
   emp,
   job
where
   emp.job_key = job.job_key
group by
   job_name
;

Here is the output from the sample database:

JOB_NAME                                           SUM(EMP_SALARY)             
-------------------------------------------------- ---------------             
Editor                                                      108000             
Manager                                                     190666              Marketer                                                     73000              Salesperson                                                 130000     

Just remember the simple rule when you perform a sum:  You must GROUP BY all columns except the column that you are summing.


For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.