Questions re subqueries

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Tue, 19 Jun 2007 16:32:08 -0700
Message-ID: <FE043305B38A0F448F3924429D650C2A0315938F@VEXBE2.ex.ad3.ucdavis.edu>

Greetings,

I am struggling to get my head wrapped around an issue which is confusing me greatly. I hope I can articulate this properly but given my confusion I may muddy the waters here. I have two 10.2.0.3.0 databases, the local one on Linux and a remote one on Solaris. I don't think platform is involved but one never knows. On each database I have a simple table with one column, varchar2 encrypted using TDE on the local node, unencrypted (TDE not implemented) on the remote node. Each table has one row with null values.

Local...
SQL> desc t1

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 C1                                                 VARCHAR2(2) ENCRYPT

SQL> select count (*) from t1;
COUNT(*)

4
SQL> select * from t1;
C1
--

1
2
3

Remote...
SQL> desc oracle.t1_at_bill_testx

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 C1                                                 VARCHAR2(2)

SQL> select count (*) from oracle.t1_at_bill_testx; COUNT(*)

4
SQL> select * from oracle.t1_at_bill_testx; C1
--

1
2
5

Now I execute the following selects, both from the local database...

SQL> select * from t1 where exists
2 (select 'x' from oracle.t1_at_bill_testx a where a.c1 = t1.c1);

C1
--

1
2

2 rows selected.

SQL> select * from t1 where not exists
2 (select 'x' from oracle.t1_at_bill_testx a where a.c1 = t1.c1);

C1
--

2 rows selected.

The second one is not really what I would expect but this is where I start to get confused and am hoping someone can shed some light on the situation for me. The issues are...

How does TDE encrypt nulls and does this have any bearing on what I am seeing?
If I remove the nulls from each table the query behaves correctly, how does having nulls effect the results of my join and subquery?
I have been playing with setting _unnest_subquery=FALSE at Oracle's suggestion (I have an SR open on this) and the results get even stranger... SQL> alter session set "_unnest_subquery"=false; Session altered. SQL> select * from t1 where not exists 2 (select 'x' from oracle.t1_at_bill_testx a where a.c1 = t1.c1);

C1
--

1
2
3

4 rows selected.

What I don't understand is the relation between the variables, TDE, the nulls and the _unnest_subquery parameter. I am quite confused. If anyone can shed some light on this and explain to me what is going on I would greatly appreciate it.

Thank you.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 19 2007 - 18:32:08 CDT