DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: strange query results against Data Dict.

  1. #1
    Join Date
    Nov 2000
    Posts
    344
    Hi Everyone,

    Can anyone tell me why this query :

    select *
    from dba_tablespaces t,
    v$temp_space_header s

    Produces NO rows? It should be a cartesian join, giving
    me more rows than tablespaces! If I query either one
    of these views I get results, but if I try to join them, I can't
    get anything!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    More questions than answers. If that doesn't work (didn't work on my system either), then why does:

    select * from dba_data_files d, (select * from v$temp_space_header ) t

    work?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    Ah-hah! A workaround!

    Cool. Actually I was trying to join to dba_tablespaces, not dba_data_files, but the workaround works for both.

    I opened a tar with Oracle on it, I'll post what they say.

    Their first reply was :

    Send me the results of 'select * from dba_tablespaces'
    and also the results of 'select * from v$temp_space_header'

    *sigh*

    -John

  4. #4
    Join Date
    Mar 2001
    Posts
    314
    Any of the table's (view's) have 0 rows in it?

    -amar

  5. #5
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    next they ask you to apply the latest patch.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by DBAtrix
    next they ask you to apply the latest patch.
    Yeah, I got a good one the other day. "This bug is fixed in 9.0.1. Upgrade your database and update the TAR with the results." As if it's a 10 minute fix.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jun 2001
    Posts
    29

    Exclamation Cartesian Product

    The query is correct, if there are no rows in the v$temp_space_header view. A cartesian product returns one row in a table for each row in another. If there are no rows in one of the tables, the condition of returning 1 row for 0 rows is never met, therefore no rows are returned.

    Mathematically speaking, the total rows returned can be thought of as row_countA * row_countB. As I recall from algebra (many many years ago), any number multiplied by 0 is 0. Therefore the total row count of the cartesian product is 0.

    I've never seen a cartesian product that returns 0, but Oracle never ceases to amaze me!
    Daniel W. Fink
    oracledba@ix.netcom.com

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Cartesian Product

    Originally posted by dwfink
    The query is correct, if there are no rows in the v$temp_space_header view.
    Do you work for Oracle Support?

    Code:
    SQL> select tablespace_name from dba_tablespaces;
    
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    RBS
    USERS
    TEMP
    TOOLS
    INDX
    TEST
    JHTEMP
    
    SQL> desc v$temp_space_header
     Name                                      Null?    Type
     ----------------------------------------- -------- --------------
     TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
     FILE_ID                                            NUMBER
     BYTES_USED                                         NUMBER
     BLOCKS_USED                                        NUMBER
     BYTES_FREE                                         NUMBER
     BLOCKS_FREE                                        NUMBER
     RELATIVE_FNO                                       NUMBER
    
    SQL> select tablespace_name from v$temp_space_header;
    
    TABLESPACE_NAME
    ------------------------------
    JHTEMP
    
    SQL> select * from dba_tablespaces t,
      2  v$temp_space_header s
      3  /
    
    no rows selected
    
    SQL> 
    SQL>  select * from dba_tablespaces t, (select * from v$temp_space_header) s
      2  /
    
    TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
    ------------------------------ -------------- ----------- -----------
    MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   EXTENT_MAN
    ----------- ------------ ---------- --------- --------- --------- ----------
    ALLOCATIO PLU TABLESPACE_NAME                   FILE_ID BYTES_USED BLOCKS_USE
    ...stuff deleted...
    8 rows selected.
    
    SQL>
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Nov 2000
    Posts
    344
    Hi Amar,

    Neither of the views have zero rows. I know that will make the join give zero rows, but that is not the problem here.

    Forget the fact that I am doing a cartesian join. it won't
    work for a regular join or an outer join either. I was just
    trying to make my example as simple as possible. ;-)

    Yes, they have already asked me to apply the patch. I asked them 'is it documented that the patch will fix it' and they said 'well, no, but....'

    -John


    [Edited by jdorlon on 06-27-2001 at 03:56 PM]

  10. #10
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    good, i'm glad it's not just me...

    they always sound so disappointed when i tell them, yes, i have the latest patch applied.

    d.

Posting Permissions

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


Click Here to Expand Forum to Full Width