Snippet Name: Having Clause
Description: Some example of using the 'HAVING' clause with 'GROUP BY'.
Also see:
» BETWEEN Condition
» LIKE Condition
» Combining the AND and OR Conditions
» OR Condition
» AND Condition
» Distinct
» WHERE Clause: Conditions
» EXISTS 2
» EXISTS
» WITH with CONNECT BY
» WITH Clause: Single alias
» WITH Clause: Double alias
Comment: (none)
Language:
Highlight Mode: PLSQL
Last Modified: March 01st, 2009
|
column owner format a10
column table_name format a32
column index_name format a32
column maxext format 99999
column numext format 99999
prompt CHECK Tables
SELECT
t.owner,
t.table_name,
MAX(t.max_extents) maxext,
COUNT(extent_id) numext
FROM
sys.dba_tables t,
sys.dba_extents e
WHERE
t.table_name = e.segment_name AND
t.owner = e.owner AND
e.segment_type = 'TABLE'
GROUP BY
t.owner,
t.table_name
HAVING COUNT(extent_id)/ MAX(t.max_extents) > .8
/
prompt CHECK Indexes
SELECT
t.owner,
t.index_name,
MAX(t.max_extents) maxext,
COUNT(extent_id) numext
FROM
sys.dba_indexes t,
sys.dba_extents e
WHERE
t.index_name = e.segment_name AND
t.owner = e.owner AND
e.segment_type = 'INDEX'
GROUP BY
t.owner,
t.index_name
HAVING COUNT(extent_id)/ MAX(t.max_extents) > .8
/
|
