[Oracle] efficient PL/SQL programming (2)-Standard Quantum Query

Scalar Query

Oracle allows a single-row subquery to be included in the select clause. Using scalar queries can effectively improve performance. When using external connections or using aggregate functions, we can consider the possibility of scalar quantum queries.

1. Cancel the use of external connections

External Connection practices:

Select a. username, count (*) from all_users A, all_objects B
Where a. Username = B. Owner (+)
Group by A. Username;

Practices for converting to scalar quantum queries:

Select a. username, (select count (*) from all_objects B where B. Owner = A. username) CNT
From all_users;

PS: the results of the two methods may be slightly different, mainly when all_objects does not meet the conditions, the external connection count (*) = 1, while the count (*) of the scalar quantum Query (*) result = 0

Select a. username, count (*), AVG (object_id) from all_users A, all_objects B
Where a. Username = B. Owner (+)
Group by A. Username;

2. Tips for using multiple Aggregate functions

When count (*)/AVG () occurs at the same time, it is not suitable to call two subqueries in the select clause. The performance will be affected. You can use the following two methods instead.

(1). Splice and split again

Select username, to_number (substr (data, 1, 10) CNT, to_number (substr (data, 11) AVG from
(
Select a. username, (select to_char (count (*), 'fm0000000009') | AVG (object_id) from all_objects B where B. Owner = A. username) data
From all_users
)

(2). Create an object type

Create or replace type mytype as object
(CNT number, AVG number );

Select username, A. Data. CNT, A. Data. AVG from
(
Select username, (select mytype (count (*), AVG (object_id) from all_objects B where B. Owner = A. username) data
From all_users
);