Questions re subqueries
- From: "William Wagman" <wjwagman@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Jun 2007 16:32:08 -0700
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@bill_testx
Name Null? Type
----------------------------------------- --------
----------------------------
C1 VARCHAR2(2)
SQL> select count (*) from oracle.t1@bill_testx;
COUNT(*)
----------
4
SQL> select * from oracle.t1@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@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@bill_testx a where a.c1 = t1.c1);
C1
--
3
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...
1) How does TDE encrypt nulls and does this have any bearing on what I
am seeing?
2) If I remove the nulls from each table the query behaves correctly,
how does having nulls effect the results of my join and subquery?
3) 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@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@xxxxxxxxxxx
(530) 754-6208
--
//www.freelists.org/webpage/oracle-l
Other related posts: