0

I need to optimize a PL/SQL function that is currently like that:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_input number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c IS SELECT n_number FROM t_table WHERE n_prop_1=n_input OR n_prop_2=n_input OR n_prop_3=n_input;
BEGIN
    ret :=  t_table_of_number();

    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

I want to be able to give an array as argument, however, I don't know how to build my cursor to take to array. I think I could use the IN statement, but could you help me settle this down please ?

EDIT:

According to solution provided by Justin Cave, it would become:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_inputs t_table_of_number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c IS SELECT n_number FROM t_table WHERE n_prop_1 IN (SELECT column_value FROM TABLE(n_inputs))
                                             OR n_prop_2 IN (SELECT column_value FROM TABLE(n_inputs))
                                             OR n_prop_3 IN (SELECT column_value FROM TABLE(n_inputs));
BEGIN
    ret :=  t_table_of_number();

    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

However, the multiple SELECT column_value FROM TABLE(n_inputs) slow the entire function. How can I improve that ?

Improve this question
7
  • Do you want to pass in a collection of n_input values? Or are you looking for something else? Commented Aug 7, 2012 at 15:44
  • Yes, I want to pass a collection of n_input Commented Aug 7, 2012 at 16:18
  • What is the query plan for the existing query? What is the query plan for the new query? What does "slow the entire function" mean exactly? That is, if the original function took, say 1 second to execute for a single input, does the revised function take 2*N seconds to execute when the input is N elements? How many rows are there in t_table? What indexes are available? Do you know in advance (roughly) how many elements are going to be in the array that is passed in? Commented Aug 7, 2012 at 16:27
  • it means that it takes n seconds for the original function with one argument and 50*n seconds for the new function call with one element in the array. All the accessed column are indexed Commented Aug 7, 2012 at 16:37
  • The query plan gives me that the original query access the index, but the new doesn't, it uses a COLLECTION ITERATOR... Commented Aug 7, 2012 at 16:44

3 Answers 3

Reset to default
2

If you want to pass in a collection of n_input values and return the same t_table_of_number collection (i.e. you don't need to know which element of the output array was associated with which element of the input array)

CREATE OR REPLACE FUNCTION tkt_get_underlying(p_inputs t_table_of_number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c 
        IS SELECT n_number 
             FROM t_table 
            WHERE n_prop IN (SELECT column_value 
                               FROM TABLE( p_inputs ) );
BEGIN
    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

This assumes that the number of elements that is going to potentially be inserted into the ret collection is still reasonable to hold in PGA memory simultaneously. Depending on the situation, you may want to transform this into a pipelined table function in order to limit the amount of PGA memory required.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hello Justin, your solution would work, but I was not enough precise. Could you give me some expertise on the edited question ?
1

Oracle is getting the cardinality wrong using the nested table, since it will have no idea how many rows are actually there. Try making your function look like:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_inputs t_table_of_number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c IS SELECT n_number FROM t_table WHERE n_prop_1 IN (SELECT /*+ cardinality(ni 1) */ column_value FROM TABLE(n_inputs) ni)
                                             OR n_prop_2 IN (SELECT /*+ cardinality(ni 1) */ column_value FROM TABLE(n_inputs) ni)
                                             OR n_prop_3 IN (SELECT /*+ cardinality(ni 1) */ column_value FROM TABLE(n_inputs) ni);
BEGIN
    ret :=  t_table_of_number();

    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

Note, if you know how many rows you expect in the nested table, make your cardinality hint accurate. Also, if you put too many rows in the nested table, Oracle could perform sub-optimally because you are making it think there are less rows in the nested table than what it really has.

Improve this answer

1 Comment

Hi thanks for your input about cardinality, I don't know if it really helps, but it doesn't hurt. I found a solution that uses a JOIN
0

Thank you for all your help, I finally find THE optimization that fit my needs. Now the query is like:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_inputs t_table_of_number) 
RETURN t_table_of_number 
IS
  ret t_table_of_number;
  CURSOR c IS SELECT t.n_number FROM t_table t, (SELECT column_value /*+cardinality(t_inputs 100) */ c FROM TABLE(n_inputs)) t_inputs
                WHERE t_inputs.c = t.n_prop_1
                OR t_inputs.c = t.n_prop_2
                OR t_inputs.c = t.n_prop_3; 
BEGIN
  ret :=  t_table_of_number();
  OPEN c;
    FETCH c BULK COLLECT INTO ret;
  CLOSE c;
  RETURN ret;
END;

It does a JOIN that is better than a IN

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.