8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

A Query processing in a distributed database management system requires the transmission of data between the computers in a network. A distribution strategy for a query is the ordering of data transmissions and local data processing in a database system. Representthe query using a graph data structure called a query graph. The DBMS must then devise an execution strategyfor retrieving the result of the query from the database files. A query typically has many possible execution strategies, and the process of choosing a suit­ able one for processing a query is known as query optimization. Query Processing would mean the entire process or activity which involves query translation into low level instructions, query optimization to save resources, cost estimation or evaluation of query, and extraction of data from the database. A portal for computer science studetns. It hosts well written, and well explained computer science and engineering articles, quizzes and practice/competitive programming/company interview Questions on subjects database management systems, operating systems, information retrieval, natural language processing, computer networks, data mining, machine learning, and more. Query Processing & Optimization Task: Find an e cient physical query plan (aka execution plan) for an SQL query Goal: Minimize the evaluation time for the query, i.e., compute query result as fast as possible Cost Factors: Disk accesses, read/write operations, I/O, page transfer (CPU time is typically ignored) Dept. Of Computer Science UC.

Home » Articles » 12c » Here

The APPROX_COUNT_DISTINCT function was added, but not documented, in Oracle 11g to improve the speed of calculating the number of distinct values (NDV) when gathering statistics using the DBMS_STATS package. Oracle Database 12c Release 1 (12.1) documented it for the first time, making it a supported feature. Oracle Database 12c Release 2 (12.2) extends the concept of approximate query processing by the addition of new functions and transparent conversion to approximate query processing.

Related articles.

Approximate Functions

In Oracle Database 12c Release 2 (12.2) the following functions provide approximate results.

The documentation states they 'obtain approximate results with negligible deviation from the exact result'. If you are writing a new query or refactoring an existing query and approximate results are acceptable, you can use them explicitly.

Convert Exact to Approximate

Having the new approximate query processing is great, but what do you do about all the existing code you have that uses the original calls? You could refactor your code, or you could ask Oracle to convert your exact calls to approximate calls instead.

Oracle Database 12c Release 2 (12.2) includes three new parameters that control approximate query processing, which can be set at the system or session level.

• APPROX_FOR_AGGREGATION : Setting this to TRUE is the equivalent of setting APPROX_FOR_COUNT_DISTINCT to TRUE and APPROX_FOR_PERCENTILE to ALL.
• APPROX_FOR_COUNT_DISTINCT : Setting this to TRUE converts COUNT(DISTINCT ...) calls to APPROX_COUNT_DISTINCT calls.
• APPROX_FOR_PERCENTILE : This can be set to NONEPERCENTILE_CONT, PERCENTILE_CONT DETERMINISTIC, PERCENTILE_DISC, PERCENTILE_DISC DETERMINISTIC, ALL, ALL DETERMINISTIC.

We can do a simple test to prove to ourselves this works. Remember, estimated execution plans are not always representative of the actual plans used by a query.

Build a large table.

Turn on the approximate query processing and get the estimated execution plan for a regular COUNT(DISTINCT ...) query.

We can see from the output the estimated plan includes a SORT AGGREGATE APPROX operation.

Query Processing In Dbms Ppt

Create a new session and run the same test without enabling the approximate query processing.

Query Processing In Dbms

We can see from the output below the estimated execution plan no longer contains the approximate query processing.

Here are some examples of setting the parameters at the session and system level.

Query Transformation

If you want to see the associated query transformation you can perform a 10053 trace and look at the resulting trace file. As an example, run the following.

Open the trace file displayed by the v$DIAG_INFO query and search for the term 'Final query after transformations'. You will see something like this.

Approximate Query Processing and Materialized Views

You can use approximate query processing functions in materialized views, which can subsequently be used for query rewrites.

Create a materialized view based on the test table we used in the previous section, using a query containing the APPROX_COUNT_DISTINCT function.

We check the approximate query processing and query rewrite parameters for the session, then check the estimated execution plan for a query against the base table using the APPROX_COUNT_DISTINCT function.

Query Processing In Dbms Slideshare

We can see a query rewrite was done to use the materialized view.

Let's see what happens if we use a conventional COUNT(DISTINCT ...) query against the base table.

We can see the estimated execution plan used the base table, rather than performing a rewrite to use the materialized view.

This time we will enable approximate query processing and try again.

We can see the estimated execution plan used the materialized view, rather than the base table.

Query Processing In Dbms Pdf

There are some restrictions associated with fast refreshes of materialized views containing approximate query processing functions listed here.

For more information see:

Query Processing In Dbms Ppt

Hope this helps. Regards Tim...