RE: SQL question

From: Uzzell, Stephan <SUzzell_at_MICROS.COM>
Date: Wed, 24 Oct 2012 19:53:27 +0000
Message-ID: <DF78EADE484D37419A53F5C898629DB72E5CB680_at_USMAIL2K1001.us.micros.int>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ramadoss, Karthik Sent: Wednesday, 24 October, 2012 15:46
To: 'Peter Khmelnitsky'
Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org 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_at_qmassociates.com] Sent: Wednesday, October 24, 2012 3:33 PM To: Ramadoss, Karthik
Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org Subject: Re: SQL question

Do these tables have null values in key columns (*.id)

From:

"Ramadoss, Karthik" <Karthik.Ramadoss_at_accidentfund.com<mailto:Karthik.Ramadoss_at_accidentfund.com>>

To:

"oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>

Date:

10/24/2012 03:03 PM

Subject:

SQL question

Sent by:

oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>

---

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.

--

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 24 2012 - 21:53:27 CEST