Re: Strage SQL query behaviour
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <yoursraju007@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 2 Dec 2006 19:21:31 +0100
MVR,
Query 1:
SELECT DISTINCT A , B , C ,D from
T1@L1
Query2:
insert into table1 SELECT DISTINCT A , B , C ,D from
T1@L1,
I observed (guess in 8i) the select on remote tables and the insert with
identical select led to distinctly different execution plans.
This could explain the difference is response time but not the difference in
cardinality of the result set.
Provide the total query including the "where" clause.
Post or check the execution plans as well.
Jaromir D.B. Nemec
----- Original Message -----
From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
To: <yoursraju007@xxxxxxxxx>; "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, December 01, 2006 8:51 PM
Subject: RE: Strage SQL query behaviour
MVR,
Logically, I don't believe this is a real query. Unless all of the
columns in your select are coming from the T1@L3 table, then this query
will fail. The same column names cannot be coming from T1@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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@L1,
T1@L1,
T1@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@L1,
T1@L1,
T1@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@L1,
T1@L1,
T1@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@L1,
T1@L1,
T1@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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
