strange query results against Data Dict.

[jdorlon]

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!

[marist89]

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?

[jdorlon]

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

[amar]

Any of the table's (view's) have 0 rows in it?

-amar

[DBAtrix]

next they ask you to apply the latest patch.

[marist89]

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.

[dwfink]

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!

[marist89]

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?

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>

[jdorlon]

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]

[DBAtrix]

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.