qery problem [message #385024] |
Fri, 06 February 2009 03:48  |
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 #385036 is a reply to message #385033] |
Fri, 06 February 2009 04:49   |
ThomasG
Messages: 3212 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 #385041 is a reply to message #385039] |
Fri, 06 February 2009 04:54   |
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 #385079 is a reply to message #385073] |
Fri, 06 February 2009 06:22   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
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
|
|
|
|