Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

how to tune this query ?

Avatar of deve_thomos
deve_thomosFlag for India asked on
Oracle Database
8 Comments1 Solution218 ViewsLast Modified:
Hello Expert,
below is my query. it is taking lots of time . can you please help me how can i  optimize this query ?
SELECT utype_fnc(p_uname ,p_visit) INTO l_utype FROM dual;
IF l_utype = 'OL' THEN
  --start of app id
  IF p_app_id = '3' THEN
  WITH setup_aud AS
    (SELECT
      (SELECT org_name
      FROM org_t
      WHERE oid = aud.tid
      )                                                 AS leader__company ,
      aud.leader                                       AS leader ,
      aud.vid                                      AS vid ,
      util.get_user_full_name(aud.leader) AS tpa_leader ,
      aud.is_lead
    FROM setup aud
    WHERE vid = p_visit
    ),
    aud1 AS
    (SELECT vid ,
      '{"'
      || leader__company
      || '":{"leaders":[' AS leader__company ,
      wm_concat(get_audasn_fnc(vid ,leader))
      || ']}}' asn_det
    FROM setup_aud
    GROUP BY leader__company ,
      vid
    )
  SELECT '"leader__company":['
    || wm_concat(leader__company
    || asn_det)
    || ']' ac_aud_ans
  INTO l_result
  FROM aud1
  GROUP BY vid; 

Regards
Thomos