RE: Strage SQL query behaviour

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 1 Dec 2006 14:51:01 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE088FB@EXCNYSM0A1AJ.nysemail.nyenet>

MVR, Logically, I don't believe this is a real query. Unless all of the columns in your select are coming from the T1_at_L3 table, then this query will fail. The same column names cannot be coming from T1_at_L1 - Oracle would not know which column to use.

How about giving us the real query and we'll see if we can help. Provide the total query including the "where" clause.

Tom

---

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MVR Sent: Friday, December 01, 2006 11:57 AM To: oracle-l
Subject: Strage SQL query behaviour

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
--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 01 2006 - 13:51:01 CST