That worked!
Thanks Jonathan.
On Friday, October 23, 2015 1:29 PM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx> wrote:
#yiv6855912149 P {margin-top:0;margin-bottom:0;}
The effect may be vary with version - and it probably won't work in 10g - but
you could add the following hints to the main query:
/*+
leading(sl de)
no_merge(sl)
no_merge(de)
push_pred(de)
*/
This should make the optimizer do a nested loop into dba_extents for each row
in segment_list passing in the relevant values each time.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Deepak Sharma [dmarc-noreply@xxxxxxxxxxxxx]
Sent: 23 October 2015 18:46
To: Oracle-L Group
Subject: Querying DBA_EXTENTS
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
