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
