Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    41

    Unanswered: Joining DBA_SEGMENTS and DBA_OBJECTS - Cartesian RESULT?

    Am a bit tired and confused at the moment, can someone advise why am getting a cartesian product when running this SQL below?

    Code:
    select segment_name, s.bytes/1024/1024 "MBYTE", s.owner, segment_type, tablespace_name tablespace,
              o.created, o.last_ddl_time
    from   dba_segments s, dba_objects o
    where s.segment_name = o.object_name and s.segment_type = o.object_type and s.owner = o.owner
    I tried for a partitioned object for a segment_name = 'PART1' for example where I know there are only 20 records in DBA_SEGMENTS but am getting 400 records instead? SQL as below:

    Code:
    select segment_name, s.bytes/1024/1024 "MBYTE", s.owner, segment_type, tablespace_name tablespace,
              o.created, o.last_ddl_time
    from   dba_segments s, dba_objects o
    where s.segment_name = 'PART1' and o.object_name = 'PART1' 
             and s.segment_name = o.object_name and s.segment_type = o.object_type and s.owner = o.owner
    Only reason am using DBA_OBJECTS is cause of the LAST_DDL_TIME. Any advise will be very much appreciated, am running crazy now ... :-)

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    and partition_name ....

    take out the join and make it simple:
    Code:
    select owner, segment_name, partition_name, tablespace_name, sum(bytes) bytes
    from dba_segments
    where segment_name = 'PART1'
    group by owner, segment_name, partition_name, tablespace_name
     order by tablespace_name, segment_name, partition_name;
    Last edited by The_Duck; 03-11-10 at 13:11.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •