Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Red face Unanswered: Equivalent of Hierarchy SQL in Oracle

    Hi All,

    We have some performance issues with 'CONNECT BY PRIOR' SQL statement and trying to find an alternate SQL which gives the same output.

    Example:

    ENum Mgr
    Julie
    Andrew Julie
    Mark Andrew
    Matt Andrew
    Wyatt Julie
    Jenny Wyatt

    SELECT enum, mgr FROM <table> START WITH enum IS NULL
    CONNECT BY PRIOR enum = mgr;

    Output:
    -------
    Julie
    ---Andrew
    ------Mark
    ------Matt
    ---Wyatt
    ------Jenny
    ---Joel

    How do I get the same output without using CONNECT BY PRIOR command. I dont mind creating a new table that will keep all the possible combinations between enum and mgr columns.

    Please provide your solution. I really appreciate it.

    Thanks in advance,
    Rao

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I dont mind creating a new table that will keep all the possible combinations between enum and mgr columns.
    So what is stopping you from do so?
    Please post your results back here so we can benefit from your efforts.
    What leads you to conclude that new table will perform any faster?
    As a learning exercise for all concerned, please post the EXPLAIN PLAN for your CONNECT BY statement.
    HAND!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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