Order Management - EBS (MOSC)

MOSC Banner

Poor performance of CONNECT BY PRIOR

edited Dec 24, 2014 10:03AM in Order Management - EBS (MOSC) 2 commentsAnswered

Hi All

I am facing performance issue of one view which is using CONNECT BY PRIOR.

The View is as below:

CREATE OR REPLACE VIEW  P_VIEW_NAME AS

SELECT xpn.quote_id,

  version_no

  /*, ROW_NUMBER() OVER(ORDER BY quote_id, version_no asc)*/

  ,

  discount

FROM TABLE_A xpn

UNION

SELECT DISTINCT connect_by_root (xpm.new_quote_id) AS quote_id,

  version_no

  /*, ROW_NUMBER() OVER(ORDER BY quote_id, version_no asc)*/

  ,

  discount

FROM TABLE_B xpm,

  TABLE_A xpn

WHERE xpn.quote_id                       = xpm.original_quote_id

AND xpm.status                           = 'C'

AND xpm.new_quote_id                    IS NOT NULL

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center