Hierarchical query return rows in wrong order on Oracle 9i
The query has a hint to use an ordered index such that the data returned is in the right order (the column POSITION should be in increasing order in the result set within a "level"), we have 9.2.0.4 database with optimizer_feature_enable = 8.1.7. but the return set with the wrong order for the position column, I assume the explain plan and the return records should be the same for both releases but it is not.
The following are the query and explain plan for 8.1.7 and 9.2.0.4
8.1.7.:
select /*+ INDEX(CTR_HIERARCHY CTR_HIERARCHY_PID_POS) */ ELEMENT_ID, LEVEL as THE_LEVEL, VERSION_INFO_ID, POSITION
from CTR_HIERARCHY
start WITH VERSION_ID = ctr_gen.findVersionID('CTR.63.1')
and PARENT_HIERARCHY_ID = 'root'
connect by prior HIERARCHY_ID = PARENT_HIERARCHY_ID and PRIOR VERSION_ID=VERSION_ID and position > 0
SELECT STATEMENT Hint=FIRST_ROWS 1 2
CONNECT BY
INDEX RANGE SCAN CTR_HIERARCHY_PID_POS 1 17 1
TABLE ACCESS BY USER ROWID CTR_HIERARCHY
TABLE ACCESS BY INDEX ROWID CTR_HIERARCHY 1 46 2
INDEX RANGE SCAN CTR_HIERARCHY_PID_POS 1 1
9.2.0.4:
SQL> explain plan for select /*+ INDEX(CTR_HIERARCHY CTR_HIERARCHY_PID_POS) */ ELEMENT_ID, LEVEL as
THE_LEVEL, VERSION_INFO_ID, POSITION
2 from CTR_HIERARCHY
3 start WITH VERSION_ID = ctr_gen.findVersionID('CTR.5471.1')
4 and PARENT_HIERARCHY_ID = 'root'
5 connect by prior HIERARCHY_ID = PARENT_HIERARCHY_ID and PRIOR VERSION_ID=VERSION_ID ;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Cost = 4
CONNECT BY WITH FILTERING
NESTED LOOPS
INDEX RANGE SCAN CTR_HIERARCHY_PID_POS
TABLE ACCESS BY USER ROWID CTR_HIERARCHY
NESTED LOOPS
BUFFER SORT
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID CTR_HIERARCHY
INDEX RANGE SCAN CTR_HIERARCHY_PID_POS
