Strage SQL query behaviour

From: MVR <yoursraju007_at_gmail.com>
Date: Fri, 1 Dec 2006 11:57:27 -0500
Message-ID: <7f411f4e0612010857s7ce42958i730fc34a547077f9@mail.gmail.com>

Environment: 9.2.0.4, Linux x86.

Query 1:
SELECT DISTINCT A , B , C ,D from

T1_at_L1,
T1_at_L1,
T1_at_L3

where
blah..blah..blah...

This query returns say around 500 rows(less than 1 second time). Please note that all tables are REMOTE over DB Link.

The following insert statement inserted 14 million rows(around 2 hours), select is same as above:

Query2:
insert into table1 SELECT DISTINCT A , B , C ,D from

T1_at_L1,
T1_at_L1,
T1_at_L3

where
blah..blah..blah...

The following table is also created with 14 million rows:

Query3:
create table table2 as SELECT DISTINCT A , B , C ,D from

T1_at_L1,
T1_at_L1,
T1_at_L3

where
blah..blah..blah...

The following query count show 14 millon rows:

Query3:
select count(1) from (SELECT DISTINCT A , B , C ,D from

T1_at_L1,
T1_at_L1,
T1_at_L3

where
blah..blah..blah...)

SELECT query is the same in all above queries.. Im not able to understand whats going wrong here, when its used as a sub query.

Has anyone seen these kind of wierd things?

Thanks,
MVR

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 01 2006 - 10:57:27 CST