<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Sets number of rows to be prefetched by queries</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.oci-set-module-name.html">oci_set_module_name</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.oci-statement-type.html">oci_statement_type</a></div>
<div class="up"><a href="ref.oci8.html">OCI8 Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="function.oci-set-prefetch" class="refentry">
<div class="refnamediv">
<h1 class="refname">oci_set_prefetch</h1>
<p class="verinfo">(PHP 5, PECL OCI8 >= 1.1.0)</p><p class="refpurpose"><span class="refname">oci_set_prefetch</span> — <span class="dc-title">Sets number of rows to be prefetched by queries</span></p>
</div>
<div class="refsect1 description" id="refsect1-function.oci-set-prefetch-description">
<h3 class="title">Description</h3>
<div class="methodsynopsis dc-description">
<span class="type">bool</span> <span class="methodname"><strong>oci_set_prefetch</strong></span>
( <span class="methodparam"><span class="type">resource</span> <code class="parameter">$statement</code></span>
, <span class="methodparam"><span class="type">int</span> <code class="parameter">$rows</code></span>
)</div>
<p class="para rdfs-comment">
Sets the number of rows to be buffered by the Oracle Client
libraries after a successful query call
to <span class="function"><a href="function.oci-execute.html" class="function">oci_execute()</a></span> and for each subsequent
internal fetch request to the database. For queries returning a
large number of rows, performance can be significantly improved by
increasing the prefetch count above the
default <a href="oci8.configuration.html#ini.oci8.default-prefetch" class="link">oci8.default_prefetch</a>
value.
</p>
<p class="para">
Prefetching is Oracle's efficient way of returning more than one
data row from the database in each network request. This can
result in better network and CPU utilization. The buffering of
rows is internal to OCI8 and the behavior of OCI8 fetching
functions is unchanged regardless of the prefetch count. For
example, <span class="function"><a href="function.oci-fetch-row.html" class="function">oci_fetch_row()</a></span> will always return one
row. The prefetch buffer is per-statement and is not used by
re-executed statements or by other connections.
</p>
<p class="para">
Call <span class="function"><strong>oci_set_prefetch()</strong></span> before
calling <span class="function"><a href="function.oci-execute.html" class="function">oci_execute()</a></span>.
</p>
<p class="para">
A tuning goal is to set the prefetch value to a reasonable size for
the network and database to handle. For queries returning a very
large number of rows, overall system efficiency might be better if
rows are retrieved from the database in several chunks (i.e set the
prefetch value smaller than the number of rows). This allows the
database to handle other users' statements while the PHP script is
processing the current set of rows.
</p>
<p class="para">
Query prefetching was introduced in Oracle 8<em class="emphasis">i</em>. REF CURSOR
prefetching was introduced in Oracle 11<em class="emphasis">g</em>R2 and occurs when PHP is
linked with Oracle 11<em class="emphasis">g</em>R2 (or later) Client libraries.
Nested cursor prefetching was
introduced in Oracle 11<em class="emphasis">g</em>R2 and requires both the Oracle Client
libraries and the database to be version 11<em class="emphasis">g</em>R2 or greater.
</p>
<p class="para">
Prefetching is not supported when queries contain LONG or LOB
columns. The prefetch value is ignored and single-row fetches will
be used in all the situations when prefetching is not supported.
</p>
<p class="para">
When using Oracle Database 12<em class="emphasis">c</em>, the prefetch
value set by PHP can be overridden by Oracle's
client <em>oraaccess.xml</em> configuration file. Refer
to Oracle documentation for more detail.
</p>
</div>
<div class="refsect1 parameters" id="refsect1-function.oci-set-prefetch-parameters">
<h3 class="title">Parameters</h3>
<p class="para">
<dl>
<dt>
<span class="term"><em><code class="parameter">statement</code></em></span>
<dd>
<p class="para">A valid OCI8 statement
identifier created by <span class="function"><a href="function.oci-parse.html" class="function">oci_parse()</a></span> and executed
by <span class="function"><a href="function.oci-execute.html" class="function">oci_execute()</a></span>, or a <em>REF
CURSOR</em> statement identifier.</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">rows</code></em></span>
<dd>
<p class="para">
The number of rows to be prefetched, >= 0
</p>
</dd>
</dt>
</dl>
</p>
</div>
<div class="refsect1 returnvalues" id="refsect1-function.oci-set-prefetch-returnvalues">
<h3 class="title">Return Values</h3>
<p class="para">
Returns <strong><code>TRUE</code></strong> on success or <strong><code>FALSE</code></strong> on failure.
</p>
</div>
<div class="refsect1 changelog" id="refsect1-function.oci-set-prefetch-changelog">
<h3 class="title">Changelog</h3>
<p class="para">
<table class="doctable informaltable">
<thead>
<tr>
<th>Version</th>
<th>Description</th>
</tr>
</thead>
<tbody class="tbody">
<tr>
<td>5.3.2 (PECL OCI8 1.4)</td>
<td>
Before this release, <em><code class="parameter">rows</code></em> must be >= 1.
</td>
</tr>
<tr>
<td>5.3.0 (PECL OCI8 1.3.4)</td>
<td>
Before this release, prefetching was limited to the lesser
of <em><code class="parameter">rows</code></em> rows and 1024
* <em><code class="parameter">rows</code></em> bytes. The byte size
restriction has now been removed.
</td>
</tr>
</tbody>
</table>
</p>
</div>
<div class="refsect1 examples" id="refsect1-function.oci-set-prefetch-examples">
<h3 class="title">Examples</h3>
<p class="para">
<div class="example" id="example-2027">
<p><strong>Example #1 Changing the default prefetch value for a query</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'SELECT * FROM myverybigtable'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_set_prefetch</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">300</span><span style="color: #007700">); </span><span style="color: #FF8000">// Set before calling oci_execute()<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br />echo </span><span style="color: #DD0000">"<table border='1'>\n"</span><span style="color: #007700">;<br />while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_ASSOC</span><span style="color: #007700">+</span><span style="color: #0000BB">OCI_RETURN_NULLS</span><span style="color: #007700">)) {<br /> echo </span><span style="color: #DD0000">"<tr>\n"</span><span style="color: #007700">;<br /> foreach (</span><span style="color: #0000BB">$row </span><span style="color: #007700">as </span><span style="color: #0000BB">$item</span><span style="color: #007700">) {<br /> echo </span><span style="color: #DD0000">" <td>"</span><span style="color: #007700">.(</span><span style="color: #0000BB">$item </span><span style="color: #007700">!== </span><span style="color: #0000BB">null </span><span style="color: #007700">? </span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$item</span><span style="color: #007700">, </span><span style="color: #0000BB">ENT_QUOTES</span><span style="color: #007700">) : </span><span style="color: #DD0000">"&nbsp;"</span><span style="color: #007700">).</span><span style="color: #DD0000">"</td>\n"</span><span style="color: #007700">;<br /> }<br /> echo </span><span style="color: #DD0000">"</tr>\n"</span><span style="color: #007700">;<br />}<br />echo </span><span style="color: #DD0000">"</table>\n"</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-2028">
<p><strong>Example #2 Changing the default prefetch for a REF CURSOR fetch</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #FF8000">/*<br /> Create the PL/SQL stored procedure as:<br /><br /> CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS<br /> BEGIN<br /> OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;<br /> END;<br />*/<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'BEGIN myproc(:rc); END;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$refcur </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_new_cursor</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':rc'</span><span style="color: #007700">, </span><span style="color: #0000BB">$refcur</span><span style="color: #007700">, -</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_B_CURSOR</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Change the prefetch before executing the cursor.<br />// REF CURSOR prefetching works when PHP is linked with Oracle 11gR2 or later Client libraries<br /></span><span style="color: #0000BB">oci_set_prefetch</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">, </span><span style="color: #0000BB">200</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">);<br /><br />echo </span><span style="color: #DD0000">"<table border='1'>\n"</span><span style="color: #007700">;<br />while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_ASSOC</span><span style="color: #007700">+</span><span style="color: #0000BB">OCI_RETURN_NULLS</span><span style="color: #007700">)) {<br /> echo </span><span style="color: #DD0000">"<tr>\n"</span><span style="color: #007700">;<br /> foreach (</span><span style="color: #0000BB">$row </span><span style="color: #007700">as </span><span style="color: #0000BB">$item</span><span style="color: #007700">) {<br /> echo </span><span style="color: #DD0000">" <td>"</span><span style="color: #007700">.(</span><span style="color: #0000BB">$item </span><span style="color: #007700">!== </span><span style="color: #0000BB">null </span><span style="color: #007700">? </span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$item</span><span style="color: #007700">, </span><span style="color: #0000BB">ENT_QUOTES</span><span style="color: #007700">) : </span><span style="color: #DD0000">"&nbsp;"</span><span style="color: #007700">).</span><span style="color: #DD0000">"</td>\n"</span><span style="color: #007700">;<br /> }<br /> echo </span><span style="color: #DD0000">"</tr>\n"</span><span style="color: #007700">;<br />}<br />echo </span><span style="color: #DD0000">"</table>\n"</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
If PHP OCI8 fetches from a REF CURSOR and then passes the REF
CURSOR back to a second PL/SQL procedure for further processing,
then set the REF CURSOR prefetch count to <strong><code>0</code></strong> to
avoid rows being "lost" from the result set. The prefetch value is
the number of extra rows fetched in each OCI8 internal request to
the database, so setting it to <strong><code>0</code></strong> means only
fetch one row at a time.
<div class="example" id="example-2029">
<p><strong>Example #3 Setting the prefetch value when passing a REF CURSOR back to Oracle</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/orcl'</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// get the REF CURSOR<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'BEGIN myproc(:rc_out); END;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$refcur </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_new_cursor</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':rc_out'</span><span style="color: #007700">, </span><span style="color: #0000BB">$refcur</span><span style="color: #007700">, -</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_B_CURSOR</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Display two rows, but don't prefetch any extra rows otherwise<br />// those extra rows would not be passed back to myproc_use_rc().<br />// A prefetch value of 0 is allowed in PHP 5.3.2 and PECL OCI8 1.4<br /></span><span style="color: #0000BB">oci_set_prefetch</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">, </span><span style="color: #0000BB">0</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$row</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$refcur</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$row</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// pass the REF CURSOR to myproc_use_rc() to do more data processing<br />// with the result set<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'begin myproc_use_rc(:rc_in); end;'</span><span style="color: #007700">); <br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':rc_in'</span><span style="color: #007700">, </span><span style="color: #0000BB">$refcur</span><span style="color: #007700">, -</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_B_CURSOR</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
</div>
<div class="refsect1 seealso" id="refsect1-function.oci-set-prefetch-seealso">
<h3 class="title">See Also</h3>
<p class="para">
<ul class="simplelist">
<li class="member">
<a href="oci8.configuration.html#ini.oci8.default-prefetch" class="link">oci8.default_prefetch</a>
ini option
</li>
</ul>
</p>
</div>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.oci-set-module-name.html">oci_set_module_name</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.oci-statement-type.html">oci_statement_type</a></div>
<div class="up"><a href="ref.oci8.html">OCI8 Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
