Use with statements to improve Oracle query efficiency

Source: Internet
Author: User

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.

  1. With
  3. -- Data is pre-processed once using subqueries, so that there is very little data. We can check rkxx quickly and repeatedly,
  5. Rkxx as (select sz, zt, sum (se) se -- tax type, status, Tax Amount
  7. From zsxx
  9. Where rkrq between '2014-02 'and '2014-06' -- condition, statistical date
  11. Group by sz, zt
  13. )
  15. Select sk. sz, sk. se, fk. se, znj. se
  17. From (select sz, sum (se) se from rkxx -- here we can directly query the results of the with subquery rkxx
  19. Where zt = '01'
  21. Group by sz) sk, -- Tax
  23. (Select sz, sum (se) se from rkxx
  25. Where zt = '02'
  27. Group by sz) fk, -- fine
  29. (Select sz, sum (se) se from rkxx
  31. Where zt = '03
  33. Group by sz) znj -- late payment
  35. Where sk. sz = fk. sz
  37. 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.