We are using version 12.1.0.2 of oracle. We have a query which is giving different results when getting executed from toad vs sqlplus.Below is the query and its plan and few sample rows which shows the difference. The plan is similar in both the client(Toad and sqlplus). The total number of rows are also coming same. But few of the rows(mainly the value of column wname) were giving wrong results when queried using sqlplus.In toad i am seeing correct results. Also when we added "order by" clause to the query, it started giving correct result on sqlplus too. This behaviour is reproducible , so i am sure that no data modification(INSERT/UPDATE/DELETE) affecting the results. I am not able to dig down the cause of this behaviour.
We are getting ~5000 records as output. I have mentioned three sample values/rows of how the corrct and wrong records looks like. There should not be rows exists in the resultset for combination, of column sname and wname having values, USER1 and EVENT2 respectively. Wondering at what situation the value "EVENT2" can appear against USER1? what is possible reason for this or if this is a Oracle bug?
SELECT s.sname , w.wname
FROM app_user.osl l,
app_user.os s,
app_user.owr w,
app_user.oot o
WHERE w.sid = s.sid1
AND l.wtp NOT IN (1, 2)
AND l.wid = w.wid
AND l.wrid = w.wrid
AND o.otid = l.wtp
AND w.stime BETWEEN TO_DATE ('20180610', 'YYYYMMDD') - 7 AND TO_DATE ('20180610', 'YYYYMMDD') - 1 / 86400;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1675677484
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 364 | 81900 | 1193 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 364 | 81900 | 1193 (0)| 00:00:01 |
|* 2 | TABLE ACCESS STORAGE FULL | oot | 93 | 1767 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 364 | 74984 | 1190 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 364 | 74984 | 1190 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 364 | 23296 | 177 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| os | 56 | 896 | 4 (0)| 00:00:01 |
|* 7 | TABLE ACCESS STORAGE FULL| owr | 364 | 17472 | 173 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | osl_idx | 1 | | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| osl | 1 | 142 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."otid"="L"."wtp")
2 - storage("O"."otid"<>1 AND "O"."otid"<>2)
filter("O"."otid"<>1 AND "O"."otid"<>2)
5 - access("W"."sid"="S"."sid1")
7 - storage("W"."stime"<=TO_DATE(' 2018-06-09 23:59:59', 'syyyy-mm-dd hh24:mi:ss')
AND "W"."stime">=TO_DATE(' 2018-06-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("W"."stime"<=TO_DATE(' 2018-06-09 23:59:59', 'syyyy-mm-dd hh24:mi:ss')
AND "W"."stime">=TO_DATE(' 2018-06-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("L"."wid"="W"."wid" AND
"L"."wrid"="W"."wrid")
9 - filter("L"."wtp"<>2 AND "L"."wtp"<>1)
SQL> SELECT s.sname , w.wname
FROM app_user.osl l,
app_user.os s,
app_user.owr w,
app_user.oot o
WHERE w.sid = s.sid1
AND l.wtp NOT IN (1, 2)
AND l.wid = w.wid
AND l.wrid = w.wrid
AND o.otid = l.wtp
AND w.stime BETWEEN TO_DATE ('20180610', 'YYYYMMDD') - 7 AND TO_DATE ('20180610', 'YYYYMMDD') - 1 / 86400
ORDER by sname, wname,wrid;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1140539435
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 364 | 81900 | 1194 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 364 | 81900 | 1194 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 364 | 81900 | 1193 (0)| 00:00:01 |
|* 3 | TABLE ACCESS STORAGE FULL | oot | 93 | 1767 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 364 | 74984 | 1190 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 364 | 74984 | 1190 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 364 | 23296 | 177 (0)| 00:00:01 |
| 7 | TABLE ACCESS STORAGE FULL| os | 56 | 896 | 4 (0)| 00:00:01 |
|* 8 | TABLE ACCESS STORAGE FULL| owr | 364 | 17472 | 173 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | osl_idx | 1 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| osl | 1 | 142 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."otid"="L"."wtp")
3 - storage("O"."otid"<>1 AND "O"."otid"<>2)
filter("O"."otid"<>1 AND "O"."otid"<>2)
1 - access("W"."sid"="S"."sid1")
8 - storage("W"."stime"<=TO_DATE(' 2018-06-09 23:59:59', 'syyyy-mm-dd hh24:mi:ss')
AND "W"."stime">=TO_DATE(' 2018-06-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("W"."stime"<=TO_DATE(' 2018-06-09 23:59:59', 'syyyy-mm-dd hh24:mi:ss')
AND "W"."stime">=TO_DATE(' 2018-06-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - access("L"."wid"="W"."wid" AND
"L"."wrid"="W"."wrid")
10 - filter("L"."wtp"<>2 AND "L"."wtp"<>1)