Hierarchical query return rows in wrong order on Oracle 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Hierarchical query return rows in wrong order on Oracle 9i

  1. #1
    Join Date
    Apr 2002
    Posts
    73

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Remove the HINT and add order by clause.
    See the result set.

    Remember, in every oracle release the optimizer gets changed.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width