Improve Performance of pl/sql,want to perform Q2 on result of Q1

Question

CREATE or REPLACE PROCEDURE TEST(
activationStartDate IN DATE,
activationEndDate IN DATE,
deActivationStartDate IN DATE,
deActivationEndDate IN DATE
)
AS
FirstNameListTable LIST_TABLE;

{--COMMENT :LIST_TABLE is nested table :create or replace TYPE "LIST_TABLE" as table     of varchar2(20);-----Nested Table Declaration
/
}

totalSameFirstName NUMBER;
j NUMBER := 1;
BEGIN



SELECT first_name BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
MINUS 
SELECT first_name FROM Employee where start_date between deActivationStartDate AND deActivationEndDate


FOR i IN FirstNameListTable.FIRST .. FirstNameListTable.LAST LOOP
     SELECT count(*) INTO totalSameFirstName FROM Employee where start_date between activationStartDate AND activationEndDate AND first_name=FirstNameListTable(i)

       IF totalSameFirstName > 2 THEN
            ---business logic
       END IF;
END LOOP;

Actually there are two queries as mensioned above

Letsay this query as Q1:

SELECT first_name BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
MINUS 
SELECT first_name FROM Employee where start_date between deActivationStartDate AND deActivationEndDate

and this query as Q2 :

SELECT count(*) INTO totalSameFirstName FROM Employee where start_date between activationStartDate AND activationEndDate AND first_name=FirstNameListTable(i)

In both the qyery i am scanning complete table,which i think there is no need. I am iterating result of Q1 and then again scanning the table to count similar first_name. If a particular firstName occur more than two times i wrote business logic.

Can i combine both the queries,Means i want to store result of Q1 in some PL/SQL dataStructure and want to perform Q2 on result of Q1.

I want to modify Q1 as

SELECT * BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
 MINUS 
 SELECT * FROM Employee where start_date between deActivationStartDate AND deActivationEndDate

But how to store 'select *' result in pl/sql dataStructure and How to pass these records to second query..Can u tell me how my code will look like? try to be more clear, i'm in lack of ideas in this PL/SQL, even it sounds like a classic :I have spend hours trying to play around with this but have got nowhere

@Ollie I changed the code as suggested by u but getting some error and not able to solve them

CREATE or REPLACE PROCEDURE TEST(
 activationStartDate IN DATE, 
 activationEndDate IN DATE, 
 deActivationStartDate IN DATE, 
  deActivationEndDate IN DATE, 
 Out_Entity OUT TEST1.RefCsr
 )
  AS
  FirstNameListTable CRITERIA_LIST_TABLE;
  out NUMBER;
  j NUMBER := 1;

  CURSOR main_cur
  IS
    WITH include_rec
      AS (SELECT first_name,COUNT(1) OVER (PARTITION BY first_name) name_count  FROM employee where start_date between activationStartDate AND activationEndDate 
     MINUS
        SELECT first_name FROM employee where start_date between deActivationStartDate AND deActivationEndDate)
       SELECT first_name FROM include_rec WHERE name_count > 2;             

    BEGIN
     OPEN main_cur;
     FETCH main_cur BULK COLLECT INTO FirstNameListTable;
     CLOSE main_cur;

      OPEN Out_Entity FOR SELECT * FROM TABLE(
                                               CAST (
                                                    FirstNameListTable AS LIST_TABLE
                                                 )
                                    ) Nos;

      END;
       /

Error 1: PL/SQL: SQL Statement ignored {Indicating : "WITH include_rec" Line} Error 2 : PL/SQL: ORA-01789: query block has incorrect number of result columns {Indicating line : "AS (SELECT first_name,COUNT(1) OVER (PARTITION BY first_name)"} thanks in advance waiting for ur reply

Answer 1

I'd like to caveat this by saying that I haven't had a lot of time to work on this so there may be a few errors but it should give you the gist of what i'm trying to tell you:

CREATE or REPLACE 
PROCEDURE TEST( 
   activationStartDate IN DATE, 
   activationEndDate IN DATE, 
   deActivationStartDate IN DATE, 
   deActivationEndDate IN DATE ) 
AS 
   CURSOR main_cur
   IS
      WITH include_rec 
        AS (SELECT first_name, 
                   start_date,
                   COUNT(1) OVER (PARTITION BY first_name) name_count
              FROM Employee 
             WHERE start_date BETWEEN activationStartDate 
                                  AND activationEndDate)
      SELECT DISTINCT 
             first_name
        FROM include_rec
       WHERE start_date NOT BETWEEN deActivationStartDate 
                                AND deActivationEndDate
         AND name_count > 2; 
   --
   FirstNameListTable dbms_sql.varchar2_table;    
BEGIN    
    OPEN main_cur;
    FETCH main_cur BULK COLLECT INTO FirstNameListTable;
    CLOSE main_cur;

    FOR i IN FirstNameListTable.FIRST .. FirstNameListTable.LAST 
    LOOP                 
           ---business logic         
    END LOOP; 

    etc...

I'd also say that if you are expecting a large resultset then put the BULK COLLECT into a loop too to reduce your memory requirements.

In an ideal world, you'd pass the variables activationStartDate, activationEndDate, deActivationStartDate and deActivationEndDate into the cursor as cursor parameters to keep the cursor modular but that's up to you. ;-)

Hope this helps... Ollie.

EDIT: In response to your question about using records to select * from the table, you can declare an associative array based on the cursor columns, for the example above if you wanted to select more than one column from EMPLOYEE then after the WITH clause you would select the named columns from EMPLOYEE you want and instead of:

FirstNameListTable dbms_sql.varchar2_table;

declare the associative array type and variable as:

TYPE main_cur_tabtype IS TABLE OF main_cur%ROWTYPE
     INDEX BY PLS_INTEGER;
main_cur_tab main_cur_tabtype;

This gives you a flexible array that will automatically hold the columns selected in your cursor (main_cur).

You would collect the records into this array with the BULK COLLECT:

OPEN main_cur;
FETCH main_cur BULK COLLECT INTO main_cur_tab;
CLOSE main_cur;

and loop through them with:

FOR i IN main_cur_tab.FIRST .. main_cur_tab.LAST
LOOP
   etc.

as for returning your resultset to Java, well I'm no java expert and you'll need to ask that in another forum or tag this question with the Java tag and hope a Java person picks it up and answers it for you.

Answer 2

Can you do this in SQL? E.g. maybe using a WITH like this:

WITH q1 AS (SELECT ....)
SELECT /* q2 */ ... FROM q1
WHERE ..
GROUP BY etc

If you can do it in SQL you can always embed it in PL/SQL afterwards if you need to.