Querying DBA_EXTENTS

From: Deepak Sharma <"Deepak>
Date: Fri, 23 Oct 2015 17:46:34 +0000 (UTC)
Message-ID: <44625056.2184530.1445622394071.JavaMail.yahoo_at_mail.yahoo.com>

If I run the below query using literals (red), it comes back immediately: SELECT DE.TABLESPACE_NAME, DE.OWNER,DE.SEGMENT_NAME,        MAX(DE.BYTES) LARGEST_EXTENT_BYTES FROM dba_extents DE
WHERE 1=1

  AND DE.OWNER           = <owner>
  AND DE.SEGMENT_NAME    = <segment_name>
  AND DE.segment_type    = <segment_type>
  AND DE.tablespace_name = <tablespace_name>
  AND DE.partition_name  = <max_partition_name>

GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME However, if I want to iterate through using a driving WITH_AS list, it's a drag even if I'm selecting just 1 row in the driving section (rownum < 2) I've tried a few hints, LEADING(SL DE), USE_NL(SL DE), but the dba_extents being a dictionary view, these hints don't seem to have any effect. Thoughts?

WITH SEGMENT_LIST AS(

  select * from (
   SELECT /*+ materialize */
           owner, segment_name, segment_type, tablespace_name, 
           MAX(partition_name) MAX_PARTITION_NAME   FROM <my_custom_table>
   GROUP BY owner, segment_name, segment_type, tablespace_name
  ) where rownum < 2

)
SELECT DE.TABLESPACE_NAME, DE.OWNER,DE.SEGMENT_NAME,        MAX(DE.BYTES) LARGEST_EXTENT_BYTES FROM SEGMENT_LIST SL, dba_extents DE
WHERE 1=1

  AND DE.OWNER           = SL.OWNER
  AND DE.SEGMENT_NAME    = SL.SEGMENT_NAME
  AND DE.segment_type    = SL.segment_type
  AND DE.tablespace_name = SL.tablespace_name
  AND DE.partition_name  = SL.max_partition_name

GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 23 2015 - 19:46:34 CEST