Question: I need to have my query "pause" the
SQL, stopping execution after the first screen is displayed. I
don't want to fetch more rows than I need to map out each page, to
reduce the overhead and time for the initial screen to display. How
can I stop the fetching of SQL rows after the first screen is
displayed?
Answer: Oracle provides the
first_rows_n (e.g. first_rows_10, first_rows_100)
to force the optimizer to optimizer your SQL to start returning rows
as quickly as possible, and you can pause SQL after first page map
out in PL/SQL.
The pseudocode might look something like this, where you declare
a cursor and fetch the rows, pausing the fetch when you exit the
loop to map out the first screen. Please note that you can also use
the PL/SQL
BULK COLLECT and
FORALL operators for even faster performance:
declare cursor c1 as select /*+ first_rows_100 */
stuff from mytable;
Open cursor c1
Count:=0
LOOP
until EOF
count := count+1
fetch c1 into
:datatruct varray(20)
If count = 20 then DO
BEGIN
Map out the screen
DONE
END
LOOP
Display first page
For more PL/SQL advanced tips, see Dr. Hall's book,
Oracle PL/SQL Tuning.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
