title: How do I optimize hierarchical query
body: <p>I've been trying to run my query but it always seem to be running like forever.
  Would like to know how can I Improve on performance for this
  Had to add in the group by Clause as the left join seem to be creating duplication when viewing the results.</p>
  <p>Hierarchical Query Code</p>
  <pre><code>with NextValue (equip_id, link_id, node_fr_id, node_to_id ) as
  (
      SELECT lm.equipment_id, l.link_id, l.node_fr_id_num, l.node_to_id_num
      FROM road_details l
      LEFT JOIN Equipment_details e ON l.link_id = e.link_id
      Where 1=1
      START WITH l.link_id = 2
      CONNECT By NOCYCLE Prior l.node_to_id_num = l.node_fr_id_num  
  )
  Select * from NextValue 
  
  -- Select Particular Equipment starting with 'DET' --
  Where 1=1 and equip_id like 'DET%'
  
  -- Remove Duplicate cause by Join clause --
  Group by  equip_id, link_id, node_fr_id, node_to_id;
  </code></pre>
  <p>Expected output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">equip_id</th>
  <th style="text-align: center;">link_id</th>
  <th style="text-align: center;">node_fr_id</th>
  <th style="text-align: center;">node_to_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">DET_276627</td>
  <td style="text-align: center;">990</td>
  <td style="text-align: center;">1138</td>
  <td style="text-align: center;">1141</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_546012</td>
  <td style="text-align: center;">1881</td>
  <td style="text-align: center;">4856</td>
  <td style="text-align: center;">2322</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_546010</td>
  <td style="text-align: center;">1980</td>
  <td style="text-align: center;">2397</td>
  <td style="text-align: center;">2437</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526526</td>
  <td style="text-align: center;">13176</td>
  <td style="text-align: center;">14259</td>
  <td style="text-align: center;">14260</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526014</td>
  <td style="text-align: center;">13724</td>
  <td style="text-align: center;">14762</td>
  <td style="text-align: center;">14763</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_536513</td>
  <td style="text-align: center;">114721</td>
  <td style="text-align: center;">114445</td>
  <td style="text-align: center;">114446</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526524</td>
  <td style="text-align: center;">106130</td>
  <td style="text-align: center;">105001</td>
  <td style="text-align: center;">106402</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_516043</td>
  <td style="text-align: center;">18803</td>
  <td style="text-align: center;">19196</td>
  <td style="text-align: center;">19198</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526024</td>
  <td style="text-align: center;">106319</td>
  <td style="text-align: center;">106753</td>
  <td style="text-align: center;">105919</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526515</td>
  <td style="text-align: center;">105796</td>
  <td style="text-align: center;">105762</td>
  <td style="text-align: center;">103751</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526037</td>
  <td style="text-align: center;">99221</td>
  <td style="text-align: center;">96871</td>
  <td style="text-align: center;">94382</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526510</td>
  <td style="text-align: center;">99185</td>
  <td style="text-align: center;">97133</td>
  <td style="text-align: center;">97881</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Current output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">equip_id</th>
  <th style="text-align: center;">link_id</th>
  <th style="text-align: center;">node_fr_id</th>
  <th style="text-align: center;">node_to_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">DET_276627</td>
  <td style="text-align: center;">990</td>
  <td style="text-align: center;">1138</td>
  <td style="text-align: center;">1141</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_546012</td>
  <td style="text-align: center;">1881</td>
  <td style="text-align: center;">4856</td>
  <td style="text-align: center;">2322</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_546010</td>
  <td style="text-align: center;">1980</td>
  <td style="text-align: center;">2397</td>
  <td style="text-align: center;">2437</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526526</td>
  <td style="text-align: center;">13176</td>
  <td style="text-align: center;">14259</td>
  <td style="text-align: center;">14260</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526014</td>
  <td style="text-align: center;">13724</td>
  <td style="text-align: center;">14762</td>
  <td style="text-align: center;">14763</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_536513</td>
  <td style="text-align: center;">114721</td>
  <td style="text-align: center;">114445</td>
  <td style="text-align: center;">114446</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526524</td>
  <td style="text-align: center;">106130</td>
  <td style="text-align: center;">105001</td>
  <td style="text-align: center;">106402</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_516043</td>
  <td style="text-align: center;">18803</td>
  <td style="text-align: center;">19196</td>
  <td style="text-align: center;">19198</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526024</td>
  <td style="text-align: center;">106319</td>
  <td style="text-align: center;">106753</td>
  <td style="text-align: center;">105919</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526515</td>
  <td style="text-align: center;">105796</td>
  <td style="text-align: center;">105762</td>
  <td style="text-align: center;">103751</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526518</td>
  <td style="text-align: center;">1061319</td>
  <td style="text-align: center;">106514</td>
  <td style="text-align: center;">104601</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526037</td>
  <td style="text-align: center;">99221</td>
  <td style="text-align: center;">96871</td>
  <td style="text-align: center;">94382</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526510</td>
  <td style="text-align: center;">99185</td>
  <td style="text-align: center;">97133</td>
  <td style="text-align: center;">97881</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526510</td>
  <td style="text-align: center;">99185</td>
  <td style="text-align: center;">97133</td>
  <td style="text-align: center;">97881</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526510</td>
  <td style="text-align: center;">99185</td>
  <td style="text-align: center;">97133</td>
  <td style="text-align: center;">97881</td>
  </tr>
  <tr>
  <td style="text-align: center;">DET_526510</td>
  <td style="text-align: center;">99185</td>
  <td style="text-align: center;">97133</td>
  <td style="text-align: center;">97881</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>for odd reason it creating duplicates and hence had to add in the Group clause to counter this issue.. but with the group clause added in i've notice the longer querying duration occuring.</p>
  <p><strong>Updates for commenters (not enough space to explain):</strong>
  Hi @HimanshuKandpal, let me just explain the query and what it is suppose to do. There are 2 tables, one is road_details and the other is an equipment_details. PK for the road detailsis Link_id, where equipment_id is the PK in the equipment_details and link_id is FK.</p>
  <p>Brief explanation road details for context of what this hieratical query is aiming to do. Picture a map of inter connecting roads, e.g. a single road is made up of multiple links (Blocks) each block has a fr_id (head) and to_id (tail) <em><strong>[link_id 1 -&gt; link_id 2 -&gt; link_id 3]</strong></em> where <code>link_id1.to_id = link_id2.fr_id</code> as they are linked that way. The query is supposed to find out what is the next connecting link based on the criteria.</p>
  <p>2nd criteria, with each link can have multiple equipment on it or non at all. If a user were to select a certain equipment type, as shown in my where clause are they able to know what is the next or previous link ID of a particular equipment with support from hieratical query.</p>