Re: Anyway to speed up this pl/sql code?

From: Pablo Sanchez <pablo_at_dev.null>
Date: Thu, 18 Apr 2002 11:21:42 -0600
Message-ID: <IgDv8.502$dx3.53785@news.uswest.net>

Guang,

Seems that you're a pretty capable coder so I'll rough it out and you can debug it ... :)

The key is to use BULK COLLECT's to grab your data and DISTINT the data yourself - rather than letting the instance do it. I'll assume that 'id' column is indexed.

If you really want fast speeds with somewhat of a brittle solution, you can 'cover' your query by indexing the columns in the base table that comprise the query. I think Oracle is now smart enough (COB wasn't before) to walk the index tree to retrieve its data.

Here's what you want to try:

---

Assumptions: idlist is comma separated

---

Create the following TYPE in your instance:

CREATE OR REPLACE TYPE method_collection IS TABLE OF VARCHAR2(128);

---

l_sql_statement            VARCHAR2(1024);
l_method_tbl               method_collection;

TYPE l_type                IS TABLE OF VARCHAR2(128) INDEX BY
BINARY_INTEGER;
l_dedup                    l_type;
l_counter                  PLS_INTEGER;

...

BEGIN

  l_sql_statement := 'BEGIN SELECT method '||


                     'BULK COLLECT INTO :1 '||

                     'FROM xyz ' ||

                     'WHERE method IS NOT NULL '||

                     'AND   id IN ('|| idlist ||'); '||

                     'END;';


  EXECUTE IMMEDIATE l_sql_statement OUT l_method_tbl;

  -- Do we have data?  If so, let's DISTINCT here

  IF l_method_tbl.EXISTS(1) THEN
     FOR i in 1..l_method_tbl.COUNT LOOP
        l_dedup(l_method_tbl(i)) := l_method_tbl(i);
     END LOOP;

     l_counter := l_dedup.FIRST;
     WHILE l_counter <= l_dedup.LAST LOOP
        IF l_counter != l_cache.LAST THEN
           retstr := retstr || l_dedup(l_counter)||',';
           l_counter := l_dedup.NEXT(l_counter);
        ELSE
           retstr := retstr || l_dedup(l_counter);
           l_counter := l_counter + 1;
     END LOOP;
     RETURN retstr;
  ELSE
     RETURN NULL;

  END IF; ...

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
Available for short-term and long-term contracts

Received on Thu Apr 18 2002 - 12:21:42 CDT