Need distinct values from PL/SQL Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Need distinct values from PL/SQL Table

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Does anyone have a quick little routine to populate one PL/SQL table from another. The trick is that I only want the distinct values copied.

    Thanks in advance,

    - Chris

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I don't know of any straightforward method, but this comes to my mind:

    1.) The source PL/SQL table has one column only of type INTEGER (or INITIGER compatible type):
    You could use the column itself as the index of the destination index-by table (meaning that column value will be the same as its corresponding index value). As a result each distinct value will have only one entry in the destination index-by table.

    2.) The source PL/SQL table has one column of a type that is not INTEGER:
    Use a good hash function to calculate a hashed integer number from the column value and use this hashed value as a index in the destination table.

    3.) The source PL/SQL table is a table of records (meaning more than one field in the records, and uniquenes is defined accros all fields):
    In this case (and if number of records is not trivially small) I would populate a normal database table from the source PL/SQL table and load distinct records into destination PL/SQL table from there.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Option 1 would be the applicable one.

    Here's the scenario: I have some before row triggers that populate an index-by table with IDs from an FK column for all the rows that were modified. Multiple rows can be affected for each FK value, so it can show up multiple times inthe index-by table.

    Now, I use this index-by table in 1 of my after-statement triggers as-is. In another, I want to act only on the distinct set of keys as it would be much more efficient.

    So, using the values from the one table as indexes in the other sound intriguing, but how do I then roll through the values in the second table? I don't want to roll through tens of thousands of entries to find the ones that are populated - that would be too slow and definitely not scalable.

    So how would this work?

    - Chris

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Index-by tables are typicaly sparce, so you are right it isn't very efficient to scan each and every possible value in the index to find existing rows. But index-by tables now offer quite some useful "methods" (aka functions) like FIRST, LAST, NEXT etc, that you can use to efficiently scan the table:

    DECLARE
    --indx PLS_INTEGER := pl_table.FIRST;
    BEGIN
    --LOOP
    ----EXIT WHEN indx IS NULL;
    ----DBMS_OUTPOT.PUT_LINE(pl_table(idx).value);
    ----idx := pl_table.NEXT;
    --END LOOP;
    END;

    So if you have three records in a table with index values 0, 100 and 200, the above block will "touch" only those three indexed rows in three iterations, instead of doing 201 iterations to check each possible value between 0 and 200.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi Jmodic , 24th May 2001 15:09 hrs chennai

    Good Solution.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Very cool!

    Here is the final code:

    DECLARE
    ___-- ---------------
    ___-- Local Variables
    ___-- ---------------
    ___l_Employee_ID1______EmployeeSalaryHistory_P.Employee_ID_TYPE___;
    ___l_Employee_ID2______EmployeeSalaryHistory_P.Employee_ID_TYPE___;
    ___l_idx1____________PLS_INTEGER;
    ___l_idx2____________PLS_INTEGER;

    BEGIN

    ______-- First, get the unique entries
    ___FOR x IN 1..EmployeeSalaryHistory_P.g_Old_Employee_ID.COUNT
    ___LOOP
    ______l_Employee_ID1(EmployeeSalaryHistory_P.g_Old_Employee_ID(x)) := EmployeeSalaryHistory_P.g_Old_Employee_ID(x);
    ___END LOOP;

    ___l_idx1___:=___l_Employee_ID1.FIRST;
    ___l_idx2___:=___0;
    ___WHILE l_idx1 IS NOT NULL
    ___LOOP
    ______l_idx2__________________:=___l_idx2 + 1;
    ______l_Employee_ID2(l_idx2)___:=___l_Employee_ID1(l_idx1);
    ______l_idx1__________________:=___l_Employee_ID1.NEXT(l_idx1);
    ___END LOOP;

    ______-- Loop through each deleted entry and handle the changes
    ___FORALL x IN l_Employee_ID2.FIRST..l_Employee_ID2.LAST

    After putting the distinct values into l_Employee_ID1, I needed to re-create a contiguous array with l_Employee_ID2 so the FORALL would work - doesn't work on sparse tables.

    I think I'm going to make a generic proc for this - it is very cool (I'm such a geek )

    Thanks a ton!

    - Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width