This was tested on Oracle 11.2. You can use ALL before a subquery as follows:
SQL> l
1 select count(*)
2 from dba_segments
3 where segment_type = 'TABLE'
4 and bytes > all
5 (select bytes from dba_segments
6* where segment_type = 'INDEX')
SQL> /
COUNT(*)
----------
2
SQL>
This
is fairly intuitive. It counts the number of tables which are bigger
than all indexes in the database. The SQL below does the same thing:
SQL> l
1 select count(*)
2 from dba_segments
3 where segment_type = 'TABLE'
4 and bytes >
5 (select max(bytes) from dba_segments
6* where segment_type = 'INDEX')
SQL> /
COUNT(*)
----------
2
SQL>
You can also use ANY before a subquery like this:
SQL> l
1 select count(*)
2 from dba_segments
3 where segment_type = 'TABLE'
4 and bytes > any
5 (select bytes from dba_segments
6* where segment_type = 'INDEX')
SQL> /
COUNT(*)
----------
754
SQL>
The
effect of this is not quite so obvious. It counts the number of tables
which are bigger than at least one of the indexes in the database. The
following SQL is equivalent:
SQL> l
1 select count(*)
2 from dba_segments
3 where segment_type = 'TABLE'
4 and bytes >
5 (select min(bytes) from dba_segments
6* where segment_type = 'INDEX')
SQL> /
COUNT(*)
----------
754
SQL>
No comments:
Post a Comment