Home » SQL & PL/SQL » SQL & PL/SQL » qery problem (oracle 9i)
qery problem [message #385024] Fri, 06 February 2009 03:48 Go to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
DEAR FREINDS
I AM USING BELOW FUNCTION TO CALCULATE
2ND HIGHST AMT BALANCE OF CUSTOMER.

AID (CUSTOMERID) WHICH IS IN PARAMETER OF FUNCTION
LED IS THE CUSTOMER AMT BALANCE TABLE
BAL IS CUSTOMER AMOUNT BALANCE WHICH IS COLUMN OF LED TABLE


FIRST METHOD 1)
CREATE OR REPLACE FUNCTION FN_GET_BAL(AID varchar2)
RETURN NUMBER
AUTHID CURRENT_USER AS
D1 NUMBER;
BEGIN


SELECT BAL INTO D1 FROM (
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=2
MINUS
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=1);

RETURN D1;
EXCEPTION
when others then
D1 :=null;
RETURN D1;
END;
/


IF I USE BELOW QUERY ON SESSION,
IT WILL GENERATE THE OUTPUT WITHIN FRACTION OF SECOND
BUT IF I USE THE SAME QUERY IN FUNCTION (WHICH I ALREADY USE IN ABOVE FINCTION),
ITS GOING IN LONG OPERATION
QUERY:
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=2
MINUS
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=1


SECOND METHOD 2) by using rnk

SELECT BAL FROM (
SELECT BAL,RANK() OVER (ORDER BY DATE_OF_TRANSACTION DESC) RNK FROM LED
WHERE CUSTOMERID='CA01') WHERE RNK=2

this is another method that is i am using to calculate second highest balance of customer.
here also i am facing same problem
if i run this on session , it will geberate the output within fraction of time
but if use this in function, it will go in long operation.

please help me to solve this
Re: qery problem [message #385025 is a reply to message #385024] Fri, 06 February 2009 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please help me to solve this

Use the query.

Don't post in UPPER case.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: qery problem [message #385031 is a reply to message #385024] Fri, 06 February 2009 04:41 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

It is better to use query ( The query using analytical function)

Regards,
Ashoka BL
Re: qery problem [message #385032 is a reply to message #385031] Fri, 06 February 2009 04:45 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Yes. In this case it is good to use Analytical Function Query.
Re: qery problem [message #385033 is a reply to message #385031] Fri, 06 February 2009 04:47 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
but i have to use function only but the problem is if u use function then it is going in long operation
Re: qery problem [message #385034 is a reply to message #385025] Fri, 06 February 2009 04:48 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
i have to use the function along with select statement,
but the problem is it is taking lot time
Re: qery problem [message #385035 is a reply to message #385033] Fri, 06 February 2009 04:48 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
But why you really need function. little more information for us can help you.
Re: qery problem [message #385036 is a reply to message #385033] Fri, 06 February 2009 04:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That's the way it is.

If you want to be faster you have to use the verison that is faster.

If you use the slower version it's slower.

Quote:

but i have to use function only



Which brings up the question: WHY?
Re: qery problem [message #385037 is a reply to message #385035] Fri, 06 February 2009 04:50 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
i want to generate some reports base on this function thats why
Re: qery problem [message #385039 is a reply to message #385033] Fri, 06 February 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vivek_rol wrote on Fri, 06 February 2009 11:47
but i have to use function only but the problem is if u use function then it is going in long operation

Use the query inside the function and get rid of the whole code.

Regards
Michel

Re: qery problem [message #385041 is a reply to message #385039] Fri, 06 February 2009 04:54 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
Dear michal sir,
i did that only, i use that query in function?
still that function is taking a lot time on session
i am still confuse why this is happening?
query runns very well on session but if u use the same query inside function, function goes into long operation
Re: qery problem [message #385069 is a reply to message #385024] Fri, 06 February 2009 05:56 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
whats the solution for this
Re: qery problem [message #385073 is a reply to message #385041] Fri, 06 February 2009 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 06 February 2009 10:51
Use SQL*Plus and copy and paste your session.

Regards
Michel

And don't forget to format it.

Regards
Michel

Re: qery problem [message #385079 is a reply to message #385073] Fri, 06 February 2009 06:22 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Are you using this function in some query where data is very large.If so have a look at this

SQL> COLUMN CUSTOMERID FORMAT A12
SQL> COLUMN BAL FORMAT A12
SQL> COLUMN DATE_OF_TRANSACTION FORMAT A20
SQL> CREATE TABLE TEST_FOR_FUNCTION
(
  CUSTOMERID           VARCHAR2(3 BYTE),
  BAL                  NUMBER(10),
  DATE_OF_TRANSACTION  DATE
)
Table created.
Elapsed: 00:00:00:23

SQL> INSERT INTO test_for_function
   SELECT     DBMS_RANDOM.STRING ('U', 3), ABS (DBMS_RANDOM.random),
              SYSDATE + LEVEL
         FROM DUAL
   CONNECT BY LEVEL <= 100000
100000 rows created.
Elapsed: 00:00:09:09

SQL> SELECT *
  FROM test_for_function
 WHERE customerid = 'ABC'

CUSTOMERID            BAL DATE_OF_TRANSACTION 
------------ ------------ --------------------
ABC             218208103 12-JAN-17           
ABC             623308774 05-DEC-47           
ABC            1435747492 11-DEC-75           
ABC             147434851 16-MAR-25           
ABC             464380799 02-MAR-41           

5 rows selected.
Elapsed: 00:00:01:85


SQL> -- Creating the function By you script
SQL> CREATE OR REPLACE FUNCTION fn_get_bal (aid VARCHAR2)
   RETURN NUMBER AUTHID CURRENT_USER
AS
   d1   NUMBER;
BEGIN
   SELECT bal
     INTO d1
     FROM (SELECT bal
             FROM (SELECT   bal
                       FROM test_for_function
                      WHERE customerid = aid
                   ORDER BY date_of_transaction DESC)
            WHERE ROWNUM <= 2
           MINUS
           SELECT bal                                             
             FROM (SELECT   bal
                       FROM test_for_function
                      WHERE customerid = aid
                   ORDER BY date_of_transaction DESC)
            WHERE ROWNUM <= 1);

   RETURN d1;
EXCEPTION
   WHEN OTHERS
   THEN
      d1 := NULL;
      RETURN d1;
END;
Function created.
Elapsed: 00:00:00:25


SQL> -- Runing By Function
SQL> SELECT fn_get_bal ('ABC')
  FROM DUAL

FN_GET_BAL('ABC')
-----------------
        147434851

1 row selected.
Elapsed: 00:00:01:50


SQL> -- Runing By Analytic Function Query
SQL> SELECT bal
  FROM (SELECT bal, RANK () OVER (ORDER BY date_of_transaction DESC) rnk
          FROM test_for_function
         WHERE customerid = 'ABC')
 WHERE rnk = 2

         BAL
------------
   147434851

1 row selected.
Elapsed: 00:00:00:45


This done by your Query. Now try with analytical function query.

SQL> CREATE OR REPLACE FUNCTION fn_get_bal (aid VARCHAR2)
   RETURN NUMBER AUTHID CURRENT_USER
AS
   d1   NUMBER;
BEGIN
   SELECT bal
     INTO d1
     FROM (SELECT bal
             FROM (SELECT bal,
                          RANK () OVER (ORDER BY date_of_transaction DESC)
                                                                          rnk
                     FROM test_for_function
                    WHERE customerid = aid)
            WHERE rnk = 2);

   RETURN d1;
EXCEPTION
   WHEN OTHERS
   THEN
      d1 := NULL;
      RETURN d1;
END;
Function created.
Elapsed: 00:00:01:28

SQL> SELECT fn_get_bal ('ABC')
  FROM DUAL

FN_GET_BAL('ABC')
-----------------
        147434851

1 row selected.
Elapsed: 00:00:00:45

SQL> SELECT bal
  FROM (SELECT bal, RANK () OVER (ORDER BY date_of_transaction DESC) rnk
          FROM test_for_function
         WHERE customerid = 'ABC')
 WHERE rnk = 2

       BAL
----------
 147434851

1 row selected.
Elapsed: 00:00:01:42


Now Checking this function in Loop for 10 different customers;

SQL> DECLARE
   temp   PLS_INTEGER;
BEGIN
   FOR i IN (SELECT     DBMS_RANDOM.STRING ('U', 3) AS parameter_value
                   FROM DUAL
             CONNECT BY LEVEL <= 10)
   LOOP
      temp := fn_get_bal (i.parameter_value);
      DBMS_OUTPUT.put_line (   'CUSTOMERID = '
                            || i.parameter_value
                            || ', Amount = '
                            || temp
                           );
      temp := NULL;
   END LOOP;
END;

CUSTOMERID = VPK, Amount = 1107167281
CUSTOMERID = ECR, Amount = 501286546
CUSTOMERID = WKF, Amount = 261721190
CUSTOMERID = WWG, Amount = 786540982
CUSTOMERID = TTL, Amount = 253135236
CUSTOMERID = NHN, Amount = 137470996
CUSTOMERID = WHP, Amount = 1081470174
CUSTOMERID = KOS, Amount = 1467415258
CUSTOMERID = PZX, Amount = 480841720
CUSTOMERID = ZNY, Amount = 1251977604

PL/SQL procedure successfully completed.
Elapsed: 00:00:03:64


When I have used this function in loop then time is increased. If you are using this function dont go it, try to implement the logic in your query with the use of analytic function.

Thanks
Trivendra

[Updated on: Fri, 06 February 2009 06:23]

Report message to a moderator

Re: qery problem [message #385218 is a reply to message #385024] Sat, 07 February 2009 20:24 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You might check out the example of a secondmax user-defined aggregate function in the 9i documentation in the link below. All you have to do is copy and paste the code to create the function, then you can use it to get the second maximum value just like you would use max to get the maximum value.

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1004821
Previous Topic: unable to create table
Next Topic: Dynaic Procedure
Goto Forum:
  


Current Time: Thu Dec 08 23:59:15 CST 2016

Total time taken to generate the page: 0.08487 seconds