1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Handling multi values by using cte in query is reducing query performance

Discussion in 'SQL PL/SQL' started by prudhvi kotagiri, Aug 11, 2017.

  1. prudhvi kotagiri

    prudhvi kotagiri Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    hyderabad
    Hi,

    WITH PlantParameter AS(
    SELECT regexp_substr('a,b','[^,]+', 1, LEVEL) AS ParameterValue FROM dual
    CONNECT BY regexp_substr('a,b','[^,]+', 1, LEVEL) IS NOT NULL
    )

    SELECT * FROM Table1 WHERE td.PLANTID IN (SELECT ParameterValue FROM PlantParameter)) /*Paramater*/

    in the above example i am passing multi values to the query and handling them by using CTE which split's the entire string to values and these values are used in the below query.this functionality is reducing query performance and it is taking more time to fetch results is there any ways to improve query execution time .My main goal is to pass multi values and i am doing it by using CTE but this is reducing performance of query is there any other way that can be suggested.

    thanks,
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    702
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.

    Most likely, you have for each row from Table1 performs a hierarchical query.

    modify your query:

    Code (Text):

    WITH PlantParameter AS(
    SELECT regexp_substr('a,b','[^,]+', 1, LEVEL) AS ParameterValue FROM dual
    CONNECT BY regexp_substr('a,b','[^,]+', 1, LEVEL) IS NOT NULL
    )

    SELECT /*+ leading(t) no_merge(z)*/
    * FROM Table1 t,PlantParameter z
    WHERE t.PLANTID = z.ParameterValue;
     

    Additional examples:

    Code (Text):

    WITH
     params AS (
      SELECT
        extract('/b/text()').getStringVal()  paramid
        FROM
      table(
        xmlsequence(
          xmltype('<a><b>' || replace('a,b,c', ',', '</b><b>') || '</b></a>' ).extract('/*/*')
                   )
           )

     )
    SELECT  --+ leading(t)
    t.*
    FROM table1 t, params p
    where t.PLANTID = p.paramid;

     
    Code (Text):

    SELECT  --+ ordered
    t.*
    FROM table t,
    XMLTABLE
      (
        'x/z'
        passing xmltype('<x><z>'||replace('a,b,c', ',', '</z><z>')||'</z></x>' )
        columns el_str VARCHAR2(20) path '.'
      ) x
    where t.PLANTID  = x.el_str;

     
     
    Last edited: Aug 11, 2017
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,496
    Likes Received:
    356
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You post no execution plans for this query so it's difficult to determine IF this needs help, much less HOW that help can be suggested. Since you're using subquery factoring (the WITH clause) one possible way to POSSIBLY improve performance is to use the /*+ materialize */ hint in the WITH query:

    SELECT /*+ materialize */ regexp_substr('a,b','[^,]+', 1, LEVEL)

    which creates a temporary table that Oracle can dynamically sample to provide some sort of statistics to feed the optimizer. Sergey has also supplied several re-writes of your query but not having the entire code block to work from means such re-writes may not be usable.

    You really need to supply more information than you have.