Hi
I have strange behavior with this query:
select SubType, OwnerID, DataID, VersionNum, Name, DECODE(SubType,270,1,281,2,272,3,257,3,4) orderingCol from DTree where OwnerID != -5 and SubType in ( 257,259,260,271,272,273,277,280,281,282,285,286,291,293,294,368,369 ) and ParentID != -1 order by 6
When I run this query(in a single line in any editor), it takes unlimited time to give me results; but when I remove any space in this query or run the same query in multiple line(i.e. editing the query in any mean) it gives me results in 2-3 seconds. I could not able to find the Root cause.
NOTE: Execution Plan for such queries are exactly same
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 488 | 3 |
| 1 | SORT ORDER BY | | 8 | 488 | 3 |
| 2 | INLIST ITERATOR | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DTREE | 8 | 488 | 2 |
| 4 | INDEX RANGE SCAN | DTREE_SUBTYPE | 8 | | 2 |
-------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Oracle Version: 10.2.0.4
Oracle Client Used: SQL Developer / SQL PLUS