RE: SQL question
- From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- To: "'Karthik.Ramadoss@xxxxxxxxxxxxxxxx'" <Karthik.Ramadoss@xxxxxxxxxxxxxxxx>, 'Peter Khmelnitsky' <peter.khmelnitsky@xxxxxxxxxxxxxxxx>
- Date: Wed, 24 Oct 2012 19:53:27 +0000
I would compare the access plans... is one via an index?
I'm thinking something like broken index... maybe analyze table TABLE1 validate
structure cascade; and repeat for the other two tables.
Stephan Uzzell
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ramadoss, Karthik
Sent: Wednesday, 24 October, 2012 15:46
To: 'Peter Khmelnitsky'
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
Subject: RE: SQL question
They do not. The id columns are all not null columns.
The source database this one was refreshed from does not exhibit this behavior.
Everything is identical between them - init.ora params, database options, etc.
From: Peter Khmelnitsky [mailto:peter.khmelnitsky@xxxxxxxxxxxxxxxx]
Sent: Wednesday, October 24, 2012 3:33 PM
To: Ramadoss, Karthik
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: SQL question
Do these tables have null values in key columns (*.id)
From:
"Ramadoss, Karthik"
<Karthik.Ramadoss@xxxxxxxxxxxxxxxx<mailto:Karthik.Ramadoss@xxxxxxxxxxxxxxxx>>
To:
"oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>"
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Date:
10/24/2012 03:03 PM
Subject:
SQL question
Sent by:
oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
________________________________
This is probably a simple one but definitely something new for me.
Database: 11.2.0.3
OS: Oracle Linux 5.6
A SQL like
SELECT A.COL1,
A.COL2,
B.COL3
FROM TABLE1 A,
TABLE2 B,
TABLE3 C
WHERE A.ID = B.ID AND B.ID = C.ID
returns 1,192,940 rows.
And
SELECT COUNT(*) from
(SELECT A.COL1,
A.COL2,
B.COL3
FROM TABLE1 A,
TABLE2 B,
TABLE3 C
WHERE A.ID = B.ID AND B.ID = C.ID)
Returns 1,192,978 rows.
Anyone know what is going on here? I would expect both to return the same
number of rows.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
