oci_set_module_name
oci_statement_type
OCI8 関数
PHP Manual

# oci_set_prefetch

(PHP 5, PECL OCI8 >= 1.1.0)

oci_set_prefetch — クエリがプリフェッチする行数を設定する

### 説明

bool oci_set_prefetch ( resource `$statement` , int `$rows` )

oci_execute() のコール、およびそれに伴う内部的なデータベースへのリクエストがすべて成功した後に Oracle Client ライブラリがバッファに取り込む行数を設定します。 For queries returning a large number of rows, performance can be significantly improved by increasing the prefetch count above the default oci8.default_prefetch value.

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, oci_fetch_row() will always return one row. The prefetch buffer is per-statement and is not used by re-executed statements or by other connections.

Call oci_set_prefetch() before calling oci_execute().

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.

Query prefetching was introduced in Oracle 8i. REF CURSOR prefetching was introduced in Oracle 11gR2 and occurs when PHP is linked with Oracle 11gR2 Client libraries and connected to 11gR2 or previous versions of the database. Nested cursor prefetching was introduced in Oracle 11gR2 and requires both the Oracle Client libraries and the database to be version 11gR2.

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.

### パラメータ

`statement`

  oci_parse() で作成して oci_execute() で実行した有効な OCI8 ステートメント ID、 あるいは REF CURSOR ステートメント ID。

`rows`

  プリフェッチする行数。>= 0

### 返り値

成功した場合に `TRUE` を、失敗した場合に `FALSE` を返します。

### 変更履歴

バージョン | 説明
--- | ---
PHP 5.3.2 (PECL OCI8 1.4) | Before this release, `rows` must be >= 1.
PHP 5.3 (PECL OCI8 1.3.4) | Before this release, prefetching was limited to the lesser of `rows` rows and 1024 * `rows` bytes. The byte size restriction has now been removed.

### 例

例1 Changing the default prefetch value for a query

`<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
$stid = oci_parse($conn, 'SELECT * FROM myverybigtable');
oci_set_prefetch($stid, 300); // Set before calling oci_execute()
oci_execute($stid);
echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;")."</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
oci_free_statement($stid);
oci_close($conn);
?>`

例2 Changing the default prefetch for a REF CURSOR fetch

`<?php
/*
Create the PL/SQL stored procedure as:
CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
END;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
$stid = oci_parse($conn, 'BEGIN myproc(:rc); END;');
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Change the prefetch before executing the cursor.
// REF CURSOR prefetching works when PHP is linked with Oracle 11gR2 Client libraries
oci_set_prefetch($refcur, 200);
oci_execute($refcur);
echo "<table border='1'>\n";
while ($row = oci_fetch_array($refcur, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;")."</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
oci_free_statement($refcur);
oci_free_statement($stid);
oci_close($conn);
?>`

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 `0` 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 `0` means only fetch one row at a time.

例3 Setting the prefetch value when passing a REF CURSOR back to Oracle

`<?php
$conn = oci_connect('hr', 'welcome', 'localhost/orcl');
// get the REF CURSOR
$stid = oci_parse($conn, 'BEGIN myproc(:rc_out); END;');
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stid, ':rc_out', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Display two rows, but don't prefetch any extra rows otherwise
// those extra rows would not be passed back to myproc_use_rc().
// A prefetch value of 0 is allowed in PHP 5.3.2 and PECL OCI8 1.4
oci_set_prefetch($refcur, 0);
oci_execute($refcur);
$row = oci_fetch_array($refcur);
var_dump($row);
$row = oci_fetch_array($refcur);
var_dump($row);
// pass the REF CURSOR to myproc_use_rc() to do more data processing
// with the result set
$stid = oci_parse($conn, 'begin myproc_use_rc(:rc_in); end;');
oci_bind_by_name($stid, ':rc_in', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
?>`

### 注意

> 注意: PHP 5.0.0 より前のバージョンでは、かわりに ocisetprefetch() を使用します。現在のバージョンでは、古い関数名もまだ使えます。 しかし、これは廃止予定であり非推奨です。

### 参考

- oci8.default_prefetch ini オプション

oci_set_module_name
oci_statement_type
OCI8 関数
PHP Manual