Home » SQL & PL/SQL » SQL & PL/SQL » why does SELECT DISTINCT return different rows when used with ORDER BY? (11g)
why does SELECT DISTINCT return different rows when used with ORDER BY? [message #573929] Fri, 04 January 2013 05:54 Go to next message
horicel
Messages: 8
Registered: September 2009
Junior Member

Hi, nex lines showing SELECT DISTINCT return different rows when used with ORDER BY.
Please help me understand why.


CREATE TABLE M1( ID_ NUMBER, A NUMBER, B NUMBER);
CREATE TABLE V1 ( A NUMBER, B2 NUMBER);


DELETE FROM M1;
DELETE FROM V1;

INSERT INTO M1 (ID_, A,B) VALUES (107, 5,1);
INSERT INTO M1 (ID_, A,B) VALUES (108, 11,1);
INSERT INTO M1 (ID_, A,B) VALUES (109, 17,1);
INSERT INTO M1 (ID_, A,B) VALUES (110, 34,1);

INSERT INTO V1 (A,B2) VALUES (5,7);
INSERT INTO V1 (A,B2) VALUES (5,7);
INSERT INTO V1 (A,B2) VALUES (5,7);
INSERT INTO V1 (A,B2) VALUES (5,4);

INSERT INTO V1 (A,B2) VALUES (11,4);
INSERT INTO V1 (A,B2) VALUES (11,4);
INSERT INTO V1 (A,B2) VALUES (11,4);
INSERT INTO V1 (A,B2) VALUES (11,7);


INSERT INTO V1 (A,B2) VALUES (17,7);
INSERT INTO V1 (A,B2) VALUES (17,7);

INSERT INTO V1 (A,B2) VALUES (42,4);


SELECT * FROM M1;
SELECT * FROM V1;

SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;


A B2
-----
5 2
11 2
17 2
42 1 *


SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;


A B2
-----
5 2
11 2
17 2
42 2 *
Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #573932 is a reply to message #573929] Fri, 04 January 2013 06:25 Go to previous messageGo to next message
cookiemonster
Messages: 10593
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like a bug. I get:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

SQL> SELECT DISTINCT
 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
 FROM V1
 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;  2    3    4

         A         B2
---------- ----------
         5          2
        11          2
        17          2
        42          1

SQL> SELECT DISTINCT
 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
 FROM V1
 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;  2    3    4

         A         B2
---------- ----------
        42          1
         5          2
        11          2
        17          2

SQL>



Same results.

Post your exact oracle version and explain plan for both queries.
Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #573934 is a reply to message #573929] Fri, 04 January 2013 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem in 10.2.0.4:
SQL> SELECT DISTINCT
  2  V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
  3  FROM V1
  4  WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
         A         B2
---------- ----------
         5          2
        11          2
        17          2
        42          1

4 rows selected.

SQL> SELECT DISTINCT
  2  V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
  3  FROM V1
  4  WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
         A         B2
---------- ----------
         5          2
        11          2
        42          1
        17          2

4 rows selected.

SQL> @v

Version Oracle : 10.2.0.4.0

Regards
Michel

[Updated on: Fri, 04 January 2013 06:55]

Report message to a moderator

Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #573935 is a reply to message #573934] Fri, 04 January 2013 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem in 11.2.0.1:
SQL> SELECT DISTINCT
  2  V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
  3  FROM V1
  4  WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
         A         B2
---------- ----------
         5          2
        11          2
        17          2
        42          1

4 rows selected.

SQL> SELECT DISTINCT
  2  V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
  3  FROM V1
  4  WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
         A         B2
---------- ----------
         5          2
        11          2
        42          1
        17          2

4 rows selected.

SQL> @v

Version Oracle : 11.2.0.1.0

Regards
Michel
Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574022 is a reply to message #573935] Sat, 05 January 2013 09:56 Go to previous messageGo to next message
horicel
Messages: 8
Registered: September 2009
Junior Member
hi, version is Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.

plan for first command:

SELECT  DISTINCT
       V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
    FROM V1
    WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1

                                                  COST CARDINALITY BYTES 
SELECT STATEMENT, GOAL = ALL_ROWS			                   9	2	24
        SORT UNIQUE                             			   9	2	24
               MERGE JOIN ANTI NA			                   8	2	24
                   SORT JOIN			                       4	11	66
                       TABLE ACCESS FULL	DEV2.V1	                   3	11	66
                   SORT UNIQUE			                           4	4	24
                       TABLE ACCESS FULL  DEV2.M1	                   3	4	24


plan for SECOND command:
SELECT  DISTINCT
       V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
    FROM V1
    WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) 
                                                  COST CARDINALITY BYTES 
SELECT STATEMENT, GOAL = ALL_ROWS			                   9	2	24
        SORT UNIQUE NOSORT			                         9	2	24
                MERGE JOIN ANTI NA			                 8	2	24
                   SORT JOIN			                       4	11	66
                       TABLE ACCESS FULL	DEV2.V1	       3	11	66
                   SORT UNIQUE			                     4	4	24
                       TABLE ACCESS FULL	DEV2	M1	     3	4	24


Thanks for refering to this behaviour as a "bug", I thought my mind was playing tricks on me.

[Edit MC: add code tags]

[Updated on: Sat, 05 January 2013 10:00] by Moderator

Report message to a moderator

Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574023 is a reply to message #574022] Sat, 05 January 2013 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

These are plans gotten via explain plan.
Activate a sql trace and tkprof the trace to get the actual execution plan.

Regards
Michel

[Updated on: Sat, 05 January 2013 10:02]

Report message to a moderator

Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574024 is a reply to message #574022] Sat, 05 January 2013 10:07 Go to previous messageGo to next message
horicel
Messages: 8
Registered: September 2009
Junior Member
Hi, I do acknowledge that , after dropping and recreating both tables, both queries return the (42,1) row as expected. The execution plans were generated before that, when the erroneous behaviour was present. at that time I have run over and over the sequence posted here and I witness the second query kept returning (42,2).
Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574026 is a reply to message #574024] Sat, 05 January 2013 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Possible memory corruption, so now impossible to reproduce.

Regards
Michel
Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574043 is a reply to message #574026] Sun, 06 January 2013 01:44 Go to previous messageGo to next message
horicel
Messages: 8
Registered: September 2009
Junior Member
Hi Michel,
I was able to repro it by playing around with rows (i.e. deleting them, re-inserting them one by one - in a way that I know that should not make a difference, since the tables ended up having the same rows, as shown by SELECT * FROM M1; SELECT * FROM V1;). The latter returns
A B2
----------
5 7
5 7
5 4
11 4
11 4
11 4
11 7
17 7
17 7
42 4
5 7
as expected, and the SELECT DISTINCT queries 1 and 2 return different results, as initially posted.
I am sorry it will take me a little until I can provide with the tkprof-ed trace, as it's my first time using tkprof and apparently I have some issues identifying the right trc file.

Please note that the example provided by me with M1 and V1 is a simple one created in order to point the "bug", I have seen it somewhere else as well. Will get back.

Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574045 is a reply to message #574043] Sun, 06 January 2013 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the test case that allows you to reproduce the bug so we can test it in different versions on our side.

Regards
Michel
Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574266 is a reply to message #574045] Tue, 08 January 2013 05:59 Go to previous messageGo to next message
horicel
Messages: 8
Registered: September 2009
Junior Member
Hi Michel,
I do not have a repro sequence, starting with CREATE TABLE etc. that leads to the erroneous behaviour.
What I do have is the possibility of running the queries over and over ( on the tables that I have already populated ) in order to see the bug and produce trace files.
Below you can find 2 traces , the first showing no bug (it was produced in another schema of my server using same lines of code for setting up M1 and V1 ) and the second showing the bug (somehow the contents of the tables result in different results for query 1 and query 2).
If I replace (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) with (CASE WHEN V1.B2 = 7 THEN 3 ELSE 1 END) I keep seeing the bug.
If I replace (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) with (CASE WHEN V1.B2 = 7 THEN '2' ELSE '1' END) I cannot see the bug.
Thanks for your support.
Horia





-- First session (trace trace28022.txt)

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;

A B2
---------- ----------
5 2
11 2
17 2
42 1

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;

A B2
---------- ----------
5 2
11 2
17 2
42 1

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;

A B2
---------- ----------
5 2
11 2
42 1
17 2

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;

A B2
---------- ----------
5 2
11 2
42 1
17 2

SQL> select a.spid from v$process a, v$session b where a.addr = b.paddr and b.audsid = userenv('sessionid');

SPID
------------------------
28022


2 -- trace trace28022.txt -- for first session




TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 8 13:29:13 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Trace file: DB11G_ora_28022.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 2p7j1vq4k466w
Plan Hash: 0
ALTER SESSION SET SQL_TRACE = TRUE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99
********************************************************************************

SQL ID: 9m7787camwh4m
Plan Hash: 0
begin :id := sys.dbms_transaction.local_transaction_id; end;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 12 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 12
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.00 0.00 0 0 0 12

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99
********************************************************************************

SQL ID: cf06fwacdmgfk
Plan Hash: 1388734953
select 'x'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 0 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 0 0 6

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99

Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 82 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 82 0 8

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99

Rows Row Source Operation
------- ---------------------------------------------------
4 SORT UNIQUE (cr=41 pr=0 pw=0 time=6 us cost=22 size=312 card=12)
8 FILTER (cr=41 pr=0 pw=0 time=56 us)
12 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=33 us cost=3 size=312 card=12)
2 TABLE ACCESS FULL M1 (cr=34 pr=0 pw=0 time=0 us cost=3 size=104 card=4)

********************************************************************************

SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 82 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 82 0 8

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99

Rows Row Source Operation
------- ---------------------------------------------------
4 HASH UNIQUE (cr=41 pr=0 pw=0 time=18 us cost=22 size=104 card=4)
8 FILTER (cr=41 pr=0 pw=0 time=63 us)
12 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=44 us cost=3 size=312 card=12)
2 TABLE ACCESS FULL M1 (cr=34 pr=0 pw=0 time=0 us cost=3 size=104 card=4)

********************************************************************************

SQL ID: 0cc34t6tu3twv
Plan Hash: 1456042965
select a.spid
from
v$process a, v$session b where a.addr = b.paddr and b.audsid =
userenv('sessionid')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 0 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=156 card=1)
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=143 card=1)
1120 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=13055 us cost=0 size=360 card=5)
35 FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=136 us cost=0 size=46 card=1)
1120 BUFFER SORT (cr=0 pr=0 pw=0 time=1705 us cost=0 size=2600 card=100)
32 FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=310 us cost=0 size=2600 card=100)
1 FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=71 card=1)
1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)

********************************************************************************

SQL ID: f711myt0q6cma
Plan Hash: 0
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)
values
(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,
:12, cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,
:18, :19,:20,:21,:22)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 23 0.00 0.00 0 0 0 0
Execute 24 0.00 0.00 0 0 0 12
Fetch 11 0.01 0.01 0 164 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 58 0.02 0.01 0 164 0 35

Misses in library cache during parse: 0
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1

Misses in library cache during parse: 0

24 user SQL statements in session.
1 internal SQL statements in session.
25 SQL statements in session.
********************************************************************************
Trace file: DB11G_ora_28022.trc
Trace file compatibility: 11.1.0.7
Sort options: default

1 session in tracefile.
24 user SQL statements in trace file.
1 internal SQL statements in trace file.
25 SQL statements in trace file.
7 unique SQL statements in trace file.
258 lines in trace file.
60 elapsed seconds in trace file.






















3 -- Second session (trace trace_bug.txt)

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;

A B2
---------- ----------
5 2
11 2
17 2
42 1

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;

A B2
---------- ----------
5 2
11 2
17 2
42 1

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;

A B2
---------- ----------
5 2
11 2
17 2
42 2

SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;

A B2
---------- ----------
5 2
11 2
17 2
42 2

SQL> select a.spid from v$process a, v$session b where a.addr = b.paddr and b.audsid = userenv('sessionid');

SPID
------------------------
28861

4 -- trace trace_bug.txt -- for second session




TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 8 13:32:18 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Trace file: DB11G_ora_28861.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 2p7j1vq4k466w
Plan Hash: 0
ALTER SESSION SET SQL_TRACE = TRUE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
********************************************************************************

SQL ID: 9m7787camwh4m
Plan Hash: 0
begin :id := sys.dbms_transaction.local_transaction_id; end;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 12 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 12
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.00 0.00 0 0 0 12

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
********************************************************************************

SQL ID: cf06fwacdmgfk
Plan Hash: 1388734953
select 'x'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 0 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 0 0 6

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90

Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 28 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 28 0 8

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90

Rows Row Source Operation
------- ---------------------------------------------------
4 SORT UNIQUE (cr=14 pr=0 pw=0 time=6 us cost=9 size=24 card=2)
7 MERGE JOIN ANTI NA (cr=14 pr=0 pw=0 time=48 us cost=8 size=24 card=2)
11 SORT JOIN (cr=0 pr=0 pw=0 time=20 us cost=4 size=66 card=11)
11 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=20 us cost=3 size=66 card=11)
4 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=4 size=24 card=4)
4 TABLE ACCESS FULL M1 (cr=7 pr=0 pw=0 time=6 us cost=3 size=24 card=4)

********************************************************************************

SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 28 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 28 0 8

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90

Rows Row Source Operation
------- ---------------------------------------------------
4 SORT UNIQUE NOSORT (cr=14 pr=0 pw=0 time=54 us cost=9 size=24 card=2)
7 MERGE JOIN ANTI NA (cr=14 pr=0 pw=0 time=54 us cost=8 size=24 card=2)
11 SORT JOIN (cr=0 pr=0 pw=0 time=20 us cost=4 size=66 card=11)
11 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=20 us cost=3 size=66 card=11)
4 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=4 size=24 card=4)
4 TABLE ACCESS FULL M1 (cr=7 pr=0 pw=0 time=6 us cost=3 size=24 card=4)

********************************************************************************

SQL ID: 0cc34t6tu3twv
Plan Hash: 1456042965
select a.spid
from
v$process a, v$session b where a.addr = b.paddr and b.audsid =
userenv('sessionid')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 0 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=156 card=1)
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=143 card=1)
1330 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=6040 us cost=0 size=360 card=5)
38 FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=185 us cost=0 size=46 card=1)
1330 BUFFER SORT (cr=0 pr=0 pw=0 time=1776 us cost=0 size=2600 card=100)
35 FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=408 us cost=0 size=2600 card=100)
1 FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=71 card=1)
1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)

********************************************************************************

SQL ID: f711myt0q6cma
Plan Hash: 0
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)
values
(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,
:12, cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,
:18, :19,:20,:21,:22)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 23 0.00 0.00 0 0 0 0
Execute 24 0.00 0.00 0 0 0 12
Fetch 11 0.01 0.01 0 56 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 58 0.02 0.01 0 56 0 35

Misses in library cache during parse: 0
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1

Misses in library cache during parse: 0

24 user SQL statements in session.
1 internal SQL statements in session.
25 SQL statements in session.
********************************************************************************
Trace file: DB11G_ora_28861.trc
Trace file compatibility: 11.1.0.7
Sort options: default

1 session in tracefile.
24 user SQL statements in trace file.
1 internal SQL statements in trace file.
25 SQL statements in trace file.
7 unique SQL statements in trace file.
260 lines in trace file.
28 elapsed seconds in trace file.


Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574270 is a reply to message #574023] Tue, 08 January 2013 07:18 Go to previous message
cookiemonster
Messages: 10593
Registered: September 2008
Location: Rainy Manchester
Senior Member
You missed doing this:
Michel Cadot wrote on Sat, 05 January 2013 16:01
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Code, and trace file extracts, are a lot easier on eyes when in code tags.

Regardless - you need to take this up with oracle support not us. It's obviously a bug in your version.
With a complete test case we could try it in other versions and see if it just affected your specific version, but without one we can't even do that.
Previous Topic: How to run query between 2 schemas
Next Topic: Max Length for a column
Goto Forum:
  


Current Time: Thu Apr 24 19:10:46 CDT 2014

Total time taken to generate the page: 0.10137 seconds