Scalar Sub Query Caching

SCALAR SUB QUERY CACHING


A subquery in the FROM clause is called an inline view.A subquery in the WHERE clause is called a nested subquery.

The SQL WITH clause only works on Oracle 9i release 2 and beyond.
Formally, the WITH clause was called subquery factoring.

Depending on the release of Oracle in use, the global temporary tables (GTT) might be a better solution than the WITH clause because indexes can be created on the GTT for faster performance.


What exactly is a scalar subquery? 

It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used.

Oracle makes use of a HASH Table for caching the results of the Scalar subQuery so that it doesn't have to fire the same scalar subQuery each time for the same value passed to the Scalar sub Query.The HASH Table is capable of storing values from 1..255 (Oracle 10G and 11G) and will look in that hash table slot to see if the answer exists. So Depending on the distinct number of input values passed to a Scalar Sub Query, Oracle tries to fetch the data from the Hash Table instead of firing the Sub Query again but this is limited to the number of entries permitted to be stored in the HASH Table.

The Scalar Sub Query Caching helps in reducing the number of times plsql Functions are called from a SQL. So there occurs a context switching between the SQL Engine and the PLSQL Engine. Context Switching is one the reasons for Performance Hindrance.

But Scalar SubQueries involving PLSQL Functions requires the PLSQL Function to be deterministic so that the PLSQL Functions always returns the same output for the same input.

SUB QUERY FACTORING


Making use of the With Clause , Oracle makes use of a optimization approach where by the Query specified in the With Clause is executed only once and this data is stored in the temporary Tablespace( Similar to creating a Global Temporary Table). So irrespective of the number of times the Query within the WITH CLAUSE is called, it will be Executed only once thereby limiting the number of times the data is accessed from disk thereby reducing the execution time of the Query.

On tiny datasets, the time involved in the temporary table setup can take longer than the original query itself so is not a particularly useful mechanism.

Any subqueries we define must be referenced at least once otherwise Oracle will raise an Exception.

SUBQUERY FACTORING AND DML


Subquery factoring is part of the SELECT statement itself, which means it can be used anywhere a SELECT is used. For example, the following statement formats are all valid.

INSERT INTO table_name
WITH subquery_name AS (
        SELECT ...
        FROM   ...
        )
SELECT ...
FROM   subquery_name;

UPDATE table_name
SET    column_name = ( WITH subquery_name AS (
                               SELECT ...
                               FROM   ...
                               )
                       SELECT ...
                       FROM   subquery_name );

DELETE
FROM   table_name
WHERE  column_name IN ( WITH subquery_name AS (
                                SELECT ...
                                FROM   ...
                                )
                        SELECT ...
                        FROM   subquery_name );

INSERT INTO table_name
VALUES ( (WITH subquery_name AS (
                  SELECT ...
                  FROM   ...
                  )
          SELECT ...
          FROM   subquery_name) );


         
SUB QUERIES CAN BE INCLUDED IN VIEWS but there is a small "gotcha". Some DDL-generators wrap the view's SQL in parentheses and this raises an exception when subqueries are used, as follows.

SQL> CREATE VIEW view_with_subquery
  2  AS
  3     (
  4      WITH subquery_name AS (
  5         SELECT SYSDATE AS date_column
  6         FROM   dual
  7         )
  8      SELECT date_column
  9      FROM   subquery_name
 10     );
   )
   *
ERROR at line 10:
ORA-32034: unsupported use of WITH clause
The correct way to code this is to remove the outer parentheses from the SQL, as follows.

SQL> CREATE OR REPLACE VIEW view_with_subquery
  2  AS
  3     WITH subquery_name AS (
  4        SELECT SYSDATE AS date_column
  5        FROM   dual
  6        )
  7     SELECT date_column
  8     FROM   subquery_name;

View created.


http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

The above link gives info on the Scalar Sub query Caching in Oracle Explained by Tom Kyte.