Index a type (table of varchar2 in Oracle)

I have create a type in Oracle (11gR2):
CREATE OR REPLACE TYPE MYARRAY AS TABLE OF VARCHAR2(4000);

I used this type in a Oracle function (str2tab). comma-separated string to collection. The function works but takes too long to execute in complex query.

Is there any way to index this Type "MYARRAY" to make the function faster?

Thanks.

toookiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
no you can't index the varray,  but you might be getting multiple invocations or a poor implementation.

What does your function code look like, what does the query that uses it look like?
0
toookiAuthor Commented:
Thanks.

I have attached the function and the query that uses the function for this. It is working functionality-wise. But it is taking 15-minutes or more to execute. The table MyTab1 and MyTab2 are a bit big though.

Thank you.
INSERT INTO MYTAB3
(SELECT DISTINCT TRIM(y.COLUMN_VALUE) x_val
                FROM (SELECT TO_CHAR(SUBSTR(TRIM(LOWER(t1.t1_val1)),
                                            1,
                                            4000)) val2,
                             t2.t2_val1 t2_val1
                        FROM MyTab1        t1,
                             MyTab2        t2
                       WHERE t2.id = t1.id) x,
                     TABLE(STR_TAB(x.val2, ' ')) y);

CREATE OR REPLACE FUNCTION STR_TAB(p1    IN VARCHAR2,
                                   p2 IN VARCHAR2 := ',')
  RETURN MYARRAY
  PIPELINED

 AS
  len NUMBER := LENGTH(p1);
  str  NUMBER := 1;
  ind  NUMBER;
BEGIN
  WHILE (str <= len) LOOP
    ind := INSTR(p1, p2, str);

    IF ind = 0 THEN
      PIPE ROW(SUBSTR(p1, str));
      str := len + 1;
    ELSE
      PIPE ROW(SUBSTR(p1, str, ind - str));
      str := ind + 1;
    END IF;
  END LOOP;

  RETURN;
END;

Open in new window

0
sdstuberCommented:
unfortunately, that's sort of the worst case scenario.


based on this...

SUBSTR(TRIM(LOWER(t1.t1_val1)),
                                            1,
                                            4000)



I assume your t1_val1  is a CLOB,  so that's extra work just to get your 4000 character substring extracted.
I suggest putting the LOWER outside the other nested functions though,  no need to lower case the whole string if you only need 4000 characters of it.


LOWER(TO_CHAR(SUBSTR(TRIM(t1.t1_val1), 1, 4000)))

It's not going to make a ton of difference but it should help a little.

Another option is to write a dedicated function that extracts the strings and parses out the strings and pipes them.  

and the query would just be....

select * from table(new_function)





0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
toookiAuthor Commented:
Thank you.
I tried this way:
LOWER(TO_CHAR(SUBSTR(TRIM(t1.t1_val1), 1, 4000)))

I tried the above multiple times and it ran faster than the one I was using
TO_CHAR(SUBSTR(TRIM(LOWER(t1.t1_val1))

It reduced the time (15minutes to 11minutes). So it improved...thanks.

And t1.t1_val1 is the CLOB field.

Could you please explain the ... dedicated function that extracts the strings and parses out the strings and pipes them... Could not well understand...  Shall I change STR_TAB or will create another function on top of it...?
Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.