16-09-2021

Query Processing In Dbms

DBMS tasks: Managing large quantity of structured data Efficient retrieval and modification: query processing and optimization Sharing data: multiple users use and manipulate data Controlling the access to data: maintaining the data integrity An example of a database (relational): Relations (tables) Attributes (columns) Tuples (rows) Example.

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. The trigger queries the parent table using SELECT FOR UPDATE, ensuring that parent row (if it exists) will remain in the database for the duration of the transaction inserting the child row. If the corresponding parent row does not exist, the trigger rejects the insert of the child row.

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

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.

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

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

Query Processing In Dbms Slideshare

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.

Query Processing In Dbms

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

Query Processing In Dbms

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.

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.

Query Processing In Dbms

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

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 Examples

Hope this helps. Regards Tim...