"Since Oracle8i, you have been able to use a subquery that returns a single row and column anywhere you could use a character string literal before"*:
select a.username
,(select count(*)
from all_objects b
where b.owner = a.username
) cnt
from all_users a;
This is more efficient than this:
select a.username, count(*)
from all_users a, all_objects b
where a.username = b.owner (+)
group by a.username;
However, what will you do if you need two columns?
1. Use Two Scalar Subqueries:
select a.username
,(select count(*)
from all_objects b
where b.owner = a.username
) cnt
,(select avg(object_id)
from all_objects b
where b.owner = a.username
) avg
from all_users a;
However, this will double the time needed for the subquery.
2. Use a Single Scalar Subquery:
Simply put the results in one field and write a select around the whole query in order to separate the results:
select username
, to_number( substr( data, 1, 10 ) ) as cnt
, to_number( substr( data, 11 ) ) as avg
from (
select a.username
,(select to_char( count(*), 'fm0000000009' ) || avg(object_id)
from all_objects b
where b.owner = a.username
) as data
from all_users a
);
This is fast. Be carefull, you will get problems if you apply to_char to a null value.
3. Use an Object Type:
"Lastly, we can use an object type to return a 'scalar' value that is really a complex type"*:
create or replace type myScalarType as object
( cnt number, average number )
/
Then, we can use this query:
select username
, a.data.cnt
, a.data.avg
from (
select username
,(select myScalarType( count(*), avg(object_id) )
from all_objects b
where b.owner = a.username
) as data
from all_users a
);
This is also fast. We don't need to_char or worry about nulls. But we have to create the type.
*) This and all other informations on this page are from chapter 8 of the book: "Effective Oracle by Design" by Thomas Kyte,
McGraw Hill 2003. Still the best book about building performant
databases: Very good readable. And since 2003 no other book has been
written covering this subject completely.
No comments:
Post a Comment