Scalar Subqueries

"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