Oracle with statement usage

Oracle with statements are common statements. The following describes the usage of Oracle with statements in detail. If you are interested in Oracle with statements, take a look.

When a certain part is used multiple times in a query, you can use the Oracle with statement to create a public temporary table. Because the subquery in the temporary memory table avoids repeated parsing, the execution efficiency will be improved a lot. Temporary tables are automatically cleared at the end of a query.

General syntax format:

 
 

  
  
with   
  
  
alias_name1 as    (subquery1),  
  
  
alias_name2 as    (subQuery2),  
  
  
……  
  
  
alias_nameN as    (subQueryN)  
  
  
select col1,col2…… col3   
  
  
     from alias_name1,alias_name2……,alias_nameN 
 
 

Example of an Oracle with statement:

 
 

  
  
SQL> WITH  
  
  
Q1 AS (SELECT 3 + 5 S FROM DUAL),  
  
  
    Q2 AS (SELECT 3 * 5 M FROM DUAL),  
  
  
    Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)  
  
  
SELECT * FROM Q3; 
 
 

Output result:

 
 

  
  
S M S+M S*M  
  
  
---------- ---------- ---------- ----------  
  
  
8 15 23 120  
  
  
 
 
 

Common ORACLE Data Types

How to query Oracle log files

Use of Oracle Parallel Query

Oracle index Optimization Design

16 oracle query date statements