php - Oracle: How to efficiently select rows using a key list

Question

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']}
"; // Print the values
}

This should work, but the $keyList could be up to 200 items long (maybe even more). Which raises the question(s):

• Is this the most efficient/robust way to do this?
• Or would it be better to do it with some kind of prepared statement that selects a single value from the database, and execute it once for each key in the list?

Answer 1

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.

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.

Update

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.

Examples

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.

SQLFiddle

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;
/

Queries to demonstrate collections:

--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))
      )
/

Example 1 - call from PHP using collections

<?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']}
"; // Print the values
  }
  echo "---
";

  $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']}
"; // Print the values
  }
  echo "---
";

  $coll->free();

  oci_free_statement($stmt);
  oci_close($conn);
?>

Example 2 - Call from PHP using array and package

<?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']}
"; // Print the values
  }
  echo "---
";


  //-- 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']}
"; // Print the values
  }
  echo "---
";

  oci_free_statement($stmt);
  oci_close($conn);
?>

Example 3 - call from PHP using array and anonymous block

<?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']}
"; // Print the values
  }
  echo "---
";


  //-- 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']}
"; // Print the values
  }
  echo "---
";

  oci_free_statement($stmt);
  oci_close($conn);
?>