-
Can anyone please clarify the quesitons below
Question 1:
I have created an index on a couple of columns.I then issue a query.Now I want to check what all indexes has been used by the optimizer? Can anyone let me know.
Question 2:
To select one row from the table should I use
a) A SELECT statement (or)
b) Using a Cursor
Which one will yield good performance and why?
-
1. Use SET AUTOTRACE ON in SQL*PLUS
2. Do you mean by (b) an explicit cursor and by (a) an implicit cursor? Even for 1 row, I use an expicit cursor. I wouldn't say *good* perforamnce but fetching data with explicit cursor has its other advantages.
-
Hi,
To see if Oracle optimizer uses an index you just created, you can use EXPLAIN PLAN command in SQL*PLus for you query - in my opinion it is more convenient.
As for question 2:
To select one row from the table should I use
a) A SELECT statement (or)
b) Using a Cursor
Which one will yield good performance and why?
If you work with Oracle 7.xx - using an explicit cursor would give you better performance, because it does exactly one fetch for one row. If you use implicit cursor (select statement), even if the table contained only one row for the query, Oracle would do the second fetch to make sure that it was the last row. So, implicit cursor would do 2 fetches if you try to get one row.
If you work with Oracle 8.xx....9 - it does not make any difference which type of cursors you use - it was improved by Oracle.
Regards,
-
If you work with Oracle 8.xx....9 - it does not make any difference which type of cursors you use - it was improved by Oracle.
Thanks a lot for the info. I didn't know that Oracle has done something about that. I have addopted using explicit cursors in Oracle 7, and I do the same in Oracle 8/8i. Could you please provide an URL concerning your last claim: about Oracle's improvement, I would be curios on what they've done.
Still, I think that people should use explicit cursors: more readable applications for all it counts.
-
"Using an explicit cursor would give you better performance over implicit cursor..."
Just another of those myths that have nothing to do with reality! The truth is the other way around: implicit cursors generally outperform explicit cursors all the time, even in 7.xx! See what a real expert has to say about that:
http://asktom.oracle.com/pls/ask/f?p...:1544606261686
and
http://asktom.oracle.com/pls/ask/f?p...:1205168148688
[Edited by jmodic on 09-02-2001 at 03:28 PM]
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
cut & paste from http://www.oracledba.co.uk/tips/explicit_implicit.htm
Pick any tuning manual, and you'll often see a section about coding with explicit cursors as opposed to implicit ones. The argument seems sound, typically going along the lines of:
"With an explicit cursor, you can open, fetch and close, whereas with an implicit cursor must issue open, fetch, second fetch, close - the second fetch being needed to check for a too many rows exception.
However, since 7.3, those cunning people at Oracle have worked around this problem and the second fetch is no longer required. Because PL/SQL is interpreted (watch out for the new native compile option in 9i), implicit cursors will actually run FASTER than explicit since typically you are using less code to achieve the same task.
You can try the following test for yourself:
REM
REM First a temporary table
REM
create table blah ( x number primary key ) ;
insert into blah
select rownum x
from sys.source$
where rownum < 1000;
REM
REM Make sure its got the full set of statistics
REM
analyze table blah compute statistics;
REM
REM A procedure to do approx 50,000 implicit reads
REM
create or replace procedure imp_test is
y number;
begin
for i in 1 .. 50 loop
for j in 1 .. 999 loop
select x
into y
from blah
where x = j;
end loop;
end loop;
end;
/
REM
REM A procedure to do approx 50,000 explicit reads
REM
create or replace procedure exp_test is
cursor c(p number) is
select x
from blah
where x = p;
y number;
begin
for i in 1 .. 50 loop
for j in 1 .. 999 loop
open c(j);
fetch c into y;
close c;
end loop;
end loop;
end;
/
REM
REM We give each one a run to prime the buffers
REM
exec imp_test
exec exp_test
REM
REM And now we see who is king of the roost...
REM
set timing on
exec imp_test
exec exp_test
so implicit is faster
-
Hi guys,
I read the links and tried examples. You both are absolutely right - it seems that an implicit cursor rules forever. I was wrong. Thank you for the info.
Sorry, Julian.
Best regards,
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|