Select statements inside from statements

From: John Harris <jharri_at_sapient.com>
Date: Thu, 10 Sep 1998 14:42:50 -0400
Message-ID: <0685A427A719D11197BB00A024D3994508357B9A@delphi.sapient.com>

John Harris
--
1998-09-10
--
Summary
-------
This sql demonstrates oracles ability to handle "select" statements
inside "from" statements. This feature can be thought of as an
"anonymous view"
--
You must have select privileges on all_ind_columns to run this sql
--
Aim
---
To summarize all indexes owned by all users ordering by the
number of columns in the index and calculating the number of indexes
with that number of columns.
--
Method
------
To get the required result set we need to do a "group by"
of the results of a "group by". Now, you could build a view of
the first group by or you could create a temporary table to hold it's
results. But suppose you are on a production machine and don't have
privileges to create any objects!
--
select table_owner, column_count, count(1) number_of_indexes from ( select table_owner, index_name, count(1) column_count from all_ind_columns group by table_owner, index_name ) group by table_owner, column_count order by table_owner, column_count desc, count(1);
--
The trick to understanding this sql is to think what Oracle does
when it processes a select statement based on a view. The above
statement is logically equivalent to
--
select table_owner, column_count, count(1) instances
from index_summary_view
group by table_owner, column_count
order by table_owner, column_count desc, count(1)
--
where index_summary_view is defined as follows
--
create view index_summary view as
( select table_owner,
from all_ind_columns
group by table_owner, index_name )
--
The use of "select" statements in a "from" statement
can be thought of as an anonymous view.

Received on Thu Sep 10 1998 - 13:42:50 CDT