How Can I Efficiently Match Large String Lists Against an Oracle Database Table?

Efficient matching of large string lists in Oracle database

In Oracle SQL Developer, matching with database tables using large lists of strings can be challenging, especially if permissions are restricted. To overcome this problem, consider the following techniques:

Collections

Leverage collections to efficiently store and manipulate large numbers of values ​​in memory. By creating a SYS.ODCIVARCHAR2LIST collection you can load your list of IDs and perform join operations using nested queries.

VARIABLE cursor REFCURSOR;

DECLARE
  your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  your_collection.EXTEND( 10000 );

  FOR i IN 1 .. 10000 LOOP
    -- 填充集合。
    your_collection(i) := DBMS_RANDOM.STRING( 'x', 20 );
  END LOOP;

  OPEN :cursor FOR
  SELECT t.*
  FROM   your_table t
         INNER JOIN
         TABLE( your_collection ) c
         ON t.id = c.COLUMN_VALUE;
END;
/

Java method

For greater flexibility, you can use Java to handle collections and query execution. This approach involves creating an ARRAY object and passing it to the prepared statement.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestDatabase2 {
    public static void main(String args[]){
        try{
            Class.forName("oracle.jdbc.OracleDriver");

            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

            String[] ids = { "1", "2", "3" };

            ArrayDescriptor des = ArrayDescriptor.createDescriptor("SYS.ODCIVARCHAR2LIST", con);

            PreparedStatement st = con.prepareStatement("SELECT t.* FROM your_table t INNER JOIN TABLE( ? ) c ON t.id = c.COLUMN_VALUE");

            // 将数组传递给过程 - 
            ((OraclePreparedStatement) st).setARRAYAtName( "your_collection", new ARRAY( des, con, ids ) );
            ResultSet cursor = st.executeQuery();

            while ( cursor.next() )
            {
                int id = cursor.getInt(1);
                double column1 = cursor.getDouble(2);
                double column2 = cursor.getDouble(3);

                System.out.println( String.format( "Id: %5d", id ) );
                System.out.println( String.format( "  Column1: %s", column1 ) );
                System.out.println( String.format( "  Column2: %s", column2 ) );
            }
        } catch(ClassNotFoundException | SQLException e) {
            System.out.println(e);
        }
    }
}

Even with read-only permissions, these methods provide efficient solutions for matching large string lists using Oracle databases.

Please note that the setARRAYAtName method in the Java example uses a placeholder ? instead of the hardcoded collection name "your_collection", which is more in line with best practices. Additionally, the actual database connection string, username, and password need to be replaced with your actual values.

The above is the detailed content of How Can I Efficiently Match Large String Lists Against an Oracle Database Table?. For more information, please follow other related articles on the PHP Chinese website!