Use with statements to improve Oracle query efficiency

How can Oracle query efficiency be improved? This is a frequently asked question. The following describes how to use the with statement to improve the Oracle query efficiency for your reference.

In oracle, the select query statement can use with, which is a subquery. oracle puts the results of the subquery into a temporary table and can be used repeatedly.

Example: Note: This is an SQL statement, not a pl/SQL statement, which can be directly executed in jdbc.

 
 

  
  
With
  
  
 
  
  
-- Data is pre-processed once using subqueries, so that there is very little data. We can check rkxx quickly and repeatedly,
  
  
 
  
  
Rkxx as (select sz, zt, sum (se) se -- tax type, status, Tax Amount
  
  
 
  
  
From zsxx
  
  
 
  
  
Where rkrq between '2014-02 'and '2014-06' -- condition, statistical date
  
  
 
  
  
Group by sz, zt
  
  
 
  
  
)
  
  
 
  
  
Select sk. sz, sk. se, fk. se, znj. se
  
  
 
  
  
From (select sz, sum (se) se from rkxx -- here we can directly query the results of the with subquery rkxx
  
  
 
  
  
Where zt = '01'
  
  
 
  
  
Group by sz) sk, -- Tax
  
  
 
  
  
(Select sz, sum (se) se from rkxx
  
  
 
  
  
Where zt = '02'
  
  
 
  
  
Group by sz) fk, -- fine
  
  
 
  
  
(Select sz, sum (se) se from rkxx
  
  
 
  
  
Where zt = '03
  
  
 
  
  
Group by sz) znj -- late payment
  
  
 
  
  
Where sk. sz = fk. sz
  
  
 
  
  
And sk. sz = znj. sz
 
 

In this way, data is pre-processed with and then queried and analyzed on the processed data without accessing the original table, which is highly efficient.

Note: For pre-processed data, you must add as few conditions as possible to the with subquery, so that the Oracle query efficiency will be faster.
 

How to query the current Oracle system time

ORACLE system table and data dictionary View

In-depth analysis of Oracle ERP system module

Three methods for querying duplicate records in Oracle

Lock oracle records when querying records