Oracle efficient design-benchmark quantum query Performance Discussion

I just read the article about oracle efficient design. When it comes to Standard Quantum queries in oracle, the performance depends on associated queries, however, I know in SQL server that the performance of associated queries is better than that of scalar queries.

Let's test the performance by executing the statement:

Set autotrace on
Select a. username, count (*) from all_users a, all_objects B
Where a. username = B. owner (+)
Group by a. username;

Run the following command in SQL developer:

5356 recursive cballs
0 db block gets
82152 consistent gets

 

Execute the following statements:

Set autotrace on
Select a. username, (select count (*) from all_objects B where B. owner = a. username) cnt
From all_users;

The execution result is as follows:

5371 recursive cballs
0 db block gets
98645 consistent gets

In this case, it is found that the performance of association queries is better than that of scalar queries. It is estimated that the "Oracle efficient design" version is relatively low, and the 10gR2 version is used now, which has greatly changed.

The execution statement is as follows:

Set autotrace on
Select a. username, count (*), avg (object_id) from all_users a, all_objects B
Where a. username = B. owner (+) group by a. username;

Execution result:

5371 recursive cballs
0 db block gets
82157 consistent gets

Execute the statement:

Set autotrace on
Select username, to_number (substr (data, 1, 10) cnt, to_number (substr (data, 11) avg from
(
Select. username, (select to_char (count (*), 'fm0000000009') | avg (object_id) from all_objects B where B. owner =. username) data from all_users)

Execution result:

5356 recursive cballs
0 db block gets
98556 consistent gets

Execute the statement:

Set autotrace on
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 a);

Execution result:

5390 recursive cballs
0 db block gets
98662 consistent gets

 

Summary:

Through the test above, we found that the performance of the scalar quantum query mentioned in TOM's Oracle efficient design was due to the association query,It may be possible in the old version, but not in 10gR2..