| PL/SQL Cursor [message #639428] |
Tue, 07 July 2015 02:01  |
nqtrung
Messages: 25
Registered: April 2007
|
Junior Member |
|
|
Hi all, I have 2 below queries:
1.
DECLARE
CURSOR c1 RETURN emp%ROWTYPE IS
SELECT * FROM EMP WHERE EMPNO = 1174540;
emp_rec emp%ROWTYPE;
BEGIN
OPEN c1;
FETCH c1 INTO emp_rec;
DBMS_OUTPUT.PUT_LINE('empno: ' || emp_rec.empno);
DBMS_OUTPUT.PUT_LINE('ename: ' || emp_rec.ename);
DBMS_OUTPUT.PUT_LINE('sal: ' || emp_rec.sal);
DBMS_OUTPUT.PUT_LINE('deptno: ' || emp_rec.deptno);
CLOSE c1;
END;
/
2.
DECLARE
CURSOR c1(empno NUMBER) RETURN emp%ROWTYPE IS
SELECT * FROM EMP WHERE EMPNO = empno;
emp_rec emp%ROWTYPE;
BEGIN
OPEN c1(1174540);
FETCH c1 INTO emp_rec;
DBMS_OUTPUT.PUT_LINE('empno: ' || emp_rec.empno);
DBMS_OUTPUT.PUT_LINE('ename: ' || emp_rec.ename);
DBMS_OUTPUT.PUT_LINE('sal: ' || emp_rec.sal);
DBMS_OUTPUT.PUT_LINE('deptno: ' || emp_rec.deptno);
CLOSE c1;
END;
/
EMP has about 10 million records. Pls explain the reason why query 2 is faster than query 1
Thanks so much
Lalit : Added code tags. Please do it yourself in future.
[Updated on: Tue, 07 July 2015 03:04] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: PL/SQL Cursor [message #639430 is a reply to message #639428] |
Tue, 07 July 2015 02:16  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
I do not know the answer to your question and how you determined it (have you run it in a loop at least million times? treating DBMS_OUTPUT would be the slowest part in that case).
The only thing I can see is that those anonymous blocks are not the same.
The first one fetches one row with EMPNO = 1174540.
The second one fetches one row with non-null EMPNO (as the condition EMPNO = EMPNO uses only EMPNO table column - why should the same name be treated differently? maybe you should rename the parameter).
|
|
|
|
| Re: PL/SQL Cursor [message #639431 is a reply to message #639429] |
Tue, 07 July 2015 02:17 |
nqtrung
Messages: 25
Registered: April 2007
|
Junior Member |
|
|
I don't think caching is reason because I ran them repeatly many times and:
Response time of query 1 is always: 0.03s
Response time of query 2 is always: 0.01s
|
|
|
|
|
|
| Re: PL/SQL Cursor [message #639435 is a reply to message #639432] |
Tue, 07 July 2015 02:38 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
0.01 vs 0.03...
Two things:
1) The client is probably rounding here and you're down to vagaries of SANs/cpu cycles and so forth
2) Is there actually a problem?
2a) If there is, stop doing it in PL/SQL for a start
Also, your variable names are hella confusing...
|
|
|
|
| Re: PL/SQL Cursor [message #639436 is a reply to message #639432] |
Tue, 07 July 2015 02:41 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
For some concrete parameter values, it might be the same.
Generally, it is not - see the explanation in my reply above.
What happens when you enter number different than 1174540 into both anonymous blocks?
By the way, it behaves diffrently for SCOTT.EMP table - the first query returns nothing (there is no employee with that EMPNO there), the second one returns employee KING (empno 7839) - just the firstly fetched row.
|
|
|
|
|
|
| Re: PL/SQL Cursor [message #639440 is a reply to message #639438] |
Tue, 07 July 2015 03:15 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
To be VERY clear. The two queries are not equivalent.
Your issue lies here:
It is not doing what you think it is doing. Because you have given your parameter the same name as your column, the code is referencing your column name, NOT your parameter. If you do not believe us, adjust your code to use an id that does not exist in your table. Post what happens then.
To emphasise, the two cursors are NOT equivalent.
Quickest easiest solution, rename your cursor parameter as has already been suggested.
[Updated on: Tue, 07 July 2015 03:15] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
