Query performance - Alternate for instr

can you provide some sample data for this please?

I suspect TRANSLATE is also part of the performance issue by the way.

samples are really needed I think.

You may want to also look at your Execution Plan.

Anytime you use functions of the left in the predicate it usually invalidates use of any indexes on the query which may lead to some ugly full table scans (not so bad if they are small tables, horrendous if they are very large tables).

forgive me but aren't you adding a comma into the concatenated string?
then using instr to locate a comma
(i.e. won't it always be true?)

also, could you review the table joins?

If I attempt to use ANSI syntax, it seems that tmp_entry d isn't actually joined and may be causing a cartesian product - I could be wrong but I'd check it anyway.

-- apply ANSI JOINS ? 
SELECT *
FROM clnt_oc_disabled a
INNER JOIN ordcatalog b   ON a.ocid = b.ocid
INNER JOIN ordcomponent c ON b.ocid = c.ocid
INNER JOIN tmp_entry d
INNER JOIN tmp_entry e    ON e.tmpid = d.tmpid AND e.nodeid = d.parentid
WHERE 
--     AND 
     AND d.tmpid = 15
--     AND 
--     AND 
    
     AND instr(',' || TRANSLATE(d.optionalitems, ' ', ',') 
                   || ',', ',' 
                   || TO_CHAR(c.attvalmeid) 
                   || ','
             ) > 0

Select allOpen in new window

Basically i am trying to find c.attvalmeid from optionalitems, Optional item will be having value as "20636 28218" and attvalmeid  will be having 28128 , so i am replacing empty space with comma in optionalitems and locate the string using instr .

I really would like you to check those joins still.

But perhaps just a LIKE would help? Below is a just a tiny sample - please ignore the use of cross join I'm not proposing you would use that. see this working at: http://sqlfiddle.com/#!4/e5413/3 

SELECT
        d.*
      , c.*
      , '%' || c.ATTVALMEID || '%'

FROM ORDCOMPONENT c
CROSS JOIN TMP_ENTRY d -- nb: cross join just used for a tiny sample

WHERE d.OPTIONALITEMS LIKE ('%' || to_char(c.ATTVALMEID) || '%')
	

--DDL used (tiny sample as I said)
CREATE TABLE ORDCOMPONENT
	(ATTVALMEID int)
;

INSERT ALL 
	INTO ORDCOMPONENT (ATTVALMEID)
		 VALUES (28128)
SELECT * FROM dual
;

CREATE TABLE TMP_ENTRY
	(OPTIONALITEMS varchar2(100))
;

INSERT ALL 
	INTO TMP_ENTRY (OPTIONALITEMS)
		 VALUES ('20636 28128')
SELECT * FROM dual
;

Select allOpen in new window

Thanks Paul, Again this is also taking much time in execution, it takes 37 seconds for me with like joins in the last if i remove that it executes just in seconds

SELECT a.ocid orderid,
  (e.node_label || ' - ' || b.ordertext) as ordertext,
  a.reasonid,
  c.attvalmeid menentryid,
  c.parentmeid,
  d.nodeid,
  d.parentid,
  c.MedC,
  d.node_label,
  c.attname,
  d.node_name
FROM clnt_oc_disabled a,
  ordcatalog b,
  ordcomponent c,
  tmp_entry d,
  tmp_entry e
WHERE a.ocid = b.ocid
AND NOT EXISTS
  (SELECT *
  FROM ordcomponent
  WHERE ocid     = c.ocid
  AND parentmeid = c.parentmeid
  AND NOT EXISTS
    (SELECT *
    FROM tmp_entry
    WHERE tmpid    = e.tmpid
    AND parentid   = e.nodeid
    AND menentryid = attvalmeid
    )
  )
AND 0              =
  (SELECT COUNT(*) FROM clnt_oc_disabled WHERE b.parent = clnt_oc_disabled.ocid
  )
AND b.ocid         = c.ocid
AND d.tmpid        = 15
AND e.tmpid        = d.tmpid
AND e.nodeid       = d.parentid
AND c.attvalmeid  IS NOT NULL
AND ((d.menentryid = c.attvalmeid)
--removing below 2 lines will execute fastly
or d.menentstr LIKE ('%' || to_char(c.ATTVALMEID) || '%')  
OR d.OPTIONALITEMS LIKE ('%' || to_char(c.ATTVALMEID) || '%'))

while 37 seconds isn't stellar, is it faster than the instr approach at all?

& no matter what we do, the last part of this query will add some time most probably

are you totally convinced you joins are fine?
(please indicate yes/no and I can then stop hassling you on that point)

The joins are fine , thats y i showed you the full query now ,

but for both instr and like are taking same 37 seconds, since this query is running in loop for each tmpid i need to reduce as much time as possible,

can we achieve this using oracle xmltype ?

yes, I see now, joins do exist. Observations:

This is how I see the current query

SELECT
       a.ocid orderid
     , (e.node_label || ' - ' || b.ordertext) AS ordertext
     , a.reasonid
     , c.attvalmeid menentryid
     , c.parentmeid
     , d.nodeid
     , d.parentid
     , c.MedC
     , d.node_label
     , c.attname
     , d.node_name
FROM clnt_oc_disabled a
INNER JOIN  ordcatalog b   ON a.ocid = b.ocid
INNER JOIN  ordcomponent c ON b.ocid = c.ocid AND c.attvalmeid IS NOT NULL -- the 'is not null' is probably redundant (see line 15)
INNER JOIN  tmp_entry d    ON c.attvalmeid = d.menentryid
                              OR d.menentstr LIKE ('%' || to_char(c.ATTVALMEID) || '%')
                              OR d.OPTIONALITEMS LIKE ('%' || to_char(c.ATTVALMEID) || '%')
INNER JOIN  tmp_entry e    ON e.tmpid = d.tmpid AND e.nodeid = d.parentid
WHERE d.tmpid = 15
     AND NOT EXISTS (
          SELECT *
          FROM ordcomponent -- why can't you use table alias c instead?
          WHERE ocid = c.ocid
               AND parentmeid = c.parentmeid
               AND NOT EXISTS (
                    SELECT *
                    FROM tmp_entry
                    WHERE tmpid = e.tmpid
                         AND parentid = e.nodeid
                         AND menentryid = attvalmeid
                    )
          )
     AND 0 = (
          SELECT COUNT(*)
          FROM clnt_oc_disabled
          WHERE b.parent = clnt_oc_disabled.ocid 
          )  -- what happens if this subquery is NULL?

Select allOpen in new window

the following is how I got there

SELECT
       a.ocid orderid
     , (e.node_label || ' - ' || b.ordertext) AS ordertext
     , a.reasonid
     , c.attvalmeid menentryid
     , c.parentmeid
     , d.nodeid
     , d.parentid
     , c.MedC
     , d.node_label
     , c.attname
     , d.node_name
FROM clnt_oc_disabled a
INNER JOIN  ordcatalog b   ON a.ocid = b.ocid
INNER JOIN  ordcomponent c ON b.ocid = c.ocid AND c.attvalmeid IS NOT NULL -- the 'is not null' is probably redundant (see line 15)
INNER JOIN  tmp_entry d    ON c.attvalmeid = d.menentryid
                              OR d.menentstr LIKE ('%' || to_char(c.ATTVALMEID) || '%')
                              OR d.OPTIONALITEMS LIKE ('%' || to_char(c.ATTVALMEID) || '%')
INNER JOIN  tmp_entry e    ON e.tmpid = d.tmpid AND e.nodeid = d.parentid
WHERE 1=1 -- see line 13
     AND NOT EXISTS (
          SELECT *
          FROM ordcomponent -- why can't you use table alias c instead?
          WHERE ocid = c.ocid
               AND parentmeid = c.parentmeid
               AND NOT EXISTS (
                    SELECT *
                    FROM tmp_entry
                    WHERE tmpid = e.tmpid
                         AND parentid = e.nodeid
                         AND menentryid = attvalmeid
                    )
          )
     AND 0 = (
          SELECT COUNT(*)
          FROM clnt_oc_disabled
          WHERE b.parent = clnt_oc_disabled.ocid 
          )                                       -- what happens if this subquery is NULL?
--     AND-- line 14
     AND d.tmpid = 15
--     AND line 19
--     AND line 19
--     AND line 14
--     AND ( -- lines 16,17,18
          -- () line 16
          --removing below 2 lines will execute fastly
          --OR d.menentstr LIKE ('%' || to_char(c.ATTVALMEID) || '%') -- line 17
          --OR d.OPTIONALITEMS LIKE ('%' || to_char(c.ATTVALMEID) || '%') -- line 18
--          ) -- lines 16,17,18

Select allOpen in new window

So I will stop hassling you about the cartesian product as the join certainly exists. But that join certainly is not simple.

However without knowing a good deal more I don't think I can come up with a magic bullet.

Is that table TMP_ENTRY literally a temp table that you have constructed?

are these possibilities?

Thanks Markgeer,
            i can try function based index and see since redesigning will not be approved at this point .

Paul,
      can you provide me some example for breaking into multipart string . or any example to split up into 2 different rows and join . i need to some how get away from string functions .