Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Question Unanswered: SQL function optimization

    Here is a function that selects a dropid (parent) for a specified childid(child). If the parent and child are not equal and the parent is not null, then the parent value is stored in a view. Can this SQL be modified or optimized any more so that it runs more efficiently?

    CREATE OR REPLACE FUNCTION get_dropid (i_value IN VARCHAR2)
    RETURN dropiddetail.dropid%TYPE
    IS
    o_value dropiddetail.dropid%TYPE;
    t_value dropiddetail.dropid%TYPE;
    BEGIN
    SELECT a.dropid
    INTO t_value
    FROM dropiddetail a
    WHERE a.childid = i_value;

    IF t_value <> i_value
    THEN
    IF t_value IS NOT NULL
    THEN
    o_value := get_dropid (t_value);
    END IF;
    END IF;

    RETURN (o_value);
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    o_value := i_value;
    RETURN (o_value);
    WHEN TOO_MANY_ROWS
    THEN
    o_value := 'TMR-' || i_value;
    RETURN (o_value);
    WHEN OTHERS
    THEN
    o_value := 'ERROR-' || i_value;
    RETURN (o_value);
    END get_dropid;
    /

  2. #2
    Join Date
    Nov 2003
    Posts
    76

    Re: SQL function optimization

    Also, does any one know of online tools which can help me analyse different SQLs. I just have the cheapest version of toad with none of the cool features. Thanks

Posting Permissions

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