Oracle:如何使用键列表有效地选择行 [英] Oracle: How to efficiently select rows using a key list
问题描述
我有一个Oracle表，其中包含一个主键(我们称其为key)和一个value字段.在我的PHP应用程序中，我有一个键列表，我想从数据库中提取所有相应的值.我可以使用类似以下PHP代码的方法做到这一点:
I have an Oracle table that contains a primary key (let's call it key) and a value field. In my PHP application I have a list of keys and I want to extract all the corresponding values from the database. I could do it using something like the following PHP code:
$keyList = array('apple', 'orange', 'banana');
$conn = oci_pconnect(USERNAME, PASSWORD, URI);
$stmt = oci_parse($conn, 'SELECT * FROM myTable WHERE value IN ('
.explode(',', $keylist)
.')');
oci_execute($stmt);
while($row = oci_fetch_array($stmt, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values
}
这应该可以，但是$keyList的长度最多可以达到200(甚至更多).这就提出了一个问题:
This should work, but the $keyList could be up to 200 items long (maybe even more). Which raises the question(s):
- 这是最有效/最可靠的方法吗?
- 还是最好用某种准备好的语句从数据库中选择一个值，然后对列表中的每个键执行一次呢?
推荐答案
将IN条件的值作为字符串串联传递不是一个好习惯.当然，第一件事是安全性和正确性，但接下来是性能.
每次调用语句时，数据库引擎都会对其进行解析，然后构建查询计划，然后执行SQL语句中指定的操作.
如果您每次都从头开始构建查询文本，则每次都执行所有三个阶段.
但是，如果始终使用绑定变量，则查询看起来相同，因此数据库使用缓存的查询计划可以加快查询的执行速度.甚至您只能调用oci_parse()一次，然后使用提供的不同参数集重复使用$stmt变量.
因此，为了获得最佳性能，您必须使用绑定变量，并使用 oci_bind_array_by_name .
It's not a good practice to pass values for IN condition as string concatenation. First things, of course, security and correctness, but next point is performance.
Each time you call the statement database engine parses it, builds a query plan and after that performs actions specified in SQL statement.
If you build up query text from scratch each time then all three stages executed each time.
But if you use bind variables all times query looks the same so database uses a cached query plan what speeds up query execution. Even you can call oci_parse() only once and reuse $stmt variable with different set of supplied parameters.
So, for the best performance you must use bind variable and fill it with array using oci_bind_array_by_name.
另一件事是使用 oci_fetch_all 的执行速度可能比逐行读取结果集要快，但这取决于处理结果的逻辑.
Additional thing is that retrieving results using oci_fetch_all may perform faster than reading result set row by row, but it depends on logic of processing results.
更新
似乎只有在您要执行PL/SQL块并且不能将其与SQL语句一起使用时，传递数组参数才有效.但是，另一种可能性是使用收藏来传递参数值.即使使用数组也可以满足问题的条件，但是这种方式不太优雅.
除了查询数据库的其他方法以外，还有系统设置之类的东西.对于PHP，在php.ini文件中有一些参数可控制与Oracle的交互.其中之一( oci8.statement_cache_size )与查询缓存和性能有关.
Seems that passing array parameters works only if you going to execute PL/SQL block and can't use it with SQL statements. But another possibility is to use collections to pass list of parameter values. It's possible to satisfy conditions of the question even with arrays, but this way is less elegant.
Besides of a different ways to query a database there are such thing as system settings. In case of PHP there are some parameters in php.ini file which controls interaction with Oracle. One of them (oci8.statement_cache_size) related to a query caching and performance.
示例
所有示例都使用Oracle中相同的数据设置.
要传递数据，我选择了预定义的SYS.ODCIVarchar2List类型，但是也可以定义具有相同特征的自定义类型(在数据设置示例中演示).
以下是演示数据方案设置的代码以及在DML中使用集合的原理.
All examples uses the same data setup in Oracle.
To pass data I choose predefined SYS.ODCIVarchar2List type, but it's also possible to define custom type with same characteristics (demonstrated in data setup example).
Below is code to demostate data scheme setup and principle of using collections in DML.
create table myTable(value varchar2(100), key varchar2(100))
/
insert into myTable(value, key)
select * from (
select 'apple', 'apple_one' from dual union all
select 'apple', 'apple_two' from dual union all
select 'banana', 'banana_one' from dual union all
select 'orange', 'orange_one' from dual union all
select 'orange', 'orange_two' from dual union all
select 'potato', 'potato_one' from dual
)
/
create or replace type TCustomList as table of varchar2(4000)
/
create or replace package TestPackage as
type TKeyList is table of varchar2(1000) index by binary_integer;
function test_select(pKeyList in out TKeyList) return sys_refcursor;
end;
/
create or replace package body TestPackage is
function test_select(pKeyList in out TKeyList) return sys_refcursor
is
vParam sys.ODCIVarchar2List := sys.ODCIVarchar2List();
vCur sys_refcursor;
vIdx binary_integer;
begin
vIdx := pKeyList.first;
while(vIdx is not null) loop
vParam.Extend;
vParam(vParam.last) := pKeyList(vIdx);
vIdx := pKeyList.next(vIdx);
end loop;
open vCur for
select * from myTable where value in (select column_value from table(vParam))
;
return vCur;
end;
end;
/
查询收藏集的查询
--select by value list
select * from myTable
where value in (
select column_value
from table(Sys.ODCIVarchar2List('banana','potato'))
)
/
--same with custom type
select * from myTable
where value in (
select column_value
from table(TCustomList('banana','potato'))
)
/
--same with demonstration of casting
select * from myTable
where value in (
select column_value
from table(cast(TCustomList('banana','potato') as Sys.ODCIVarchar2List))
)
/
示例1-使用集合从PHP调用
<?php
$keyList = array('apple', 'potato');
$conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME");
$stmt = oci_parse($conn, "SELECT * FROM myTable where value in (select column_value from table(:key_list))");
$coll = oci_new_collection($conn, 'ODCIVARCHAR2LIST','SYS');
for ($i=0; $i < count($keyList); $i++) {
$coll->append($keyList[$i]);
}
oci_bind_by_name($stmt, 'key_list', $coll, -1, OCI_B_NTY);
oci_execute($stmt);
while($row = oci_fetch_array($stmt, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values
}
echo "---\n";
$coll->free();
//-- Run statement another time with different parameters
//-- without reparsing.
$coll = oci_new_collection($conn, 'ODCIVARCHAR2LIST','SYS');
$coll->append('banana');
oci_bind_by_name($stmt, 'key_list', $coll, -1, OCI_B_NTY);
oci_execute($stmt);
while($row = oci_fetch_array($stmt, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values
}
echo "---\n";
$coll->free();
oci_free_statement($stmt);
oci_close($conn);
?>
示例2-使用数组和包从PHP调用
<?php
$keyList = array('apple', 'potato');
$conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME");
$stmt = oci_parse($conn, "begin :cur := TestPackage.test_select(:key_list); end;");
$curs = oci_new_cursor($conn);
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_bind_by_name($stmt, "cur", $curs, -1, OCI_B_CURSOR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values
}
echo "---\n";
//-- Run statement another time with different parameters
//-- without reparsing.
$keyList = array('banana');
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values
}
echo "---\n";
oci_free_statement($stmt);
oci_close($conn);
?>
示例3-使用数组和匿名块从PHP调用
<?php
$keyList = array('apple', 'potato');
$conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME");
$stmt = oci_parse($conn, "
declare
type TKeyList is table of varchar2(4000) index by binary_integer;
pKeyList TKeyList := :key_list;
vParam sys.ODCIVarchar2List := sys.ODCIVarchar2List();
vIdx binary_integer;
begin
-- Copy PL/SQL array to a type which allowed in SQL context
vIdx := pKeyList.first;
while(vIdx is not null) loop
vParam.Extend;
vParam(vParam.last) := pKeyList(vIdx);
vIdx := pKeyList.next(vIdx);
end loop;
open :cur for select * from myTable where value in (select column_value from table(vParam));
end;
");
$curs = oci_new_cursor($conn);
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_bind_by_name($stmt, "cur", $curs, -1, OCI_B_CURSOR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values
}
echo "---\n";
//-- Run statement another time with different parameters
//-- without reparsing.
$keyList = array('banana');
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}\n"; // Print the values
}
echo "---\n";
oci_free_statement($stmt);
oci_close($conn);
?>
这篇关于Oracle:如何使用键列表有效地选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
