如何高效地查找每个 Oracle 表中所有列的特定值？

在每个 Oracle 表的每一列中查找特定值

本指南解释了如何在大型 Oracle 数据库中每个表的每个字段中搜索特定值（“1/22/2008P09RR8”）。 挑战在于未知的列名称和庞大的数据量。

使用SELECT * from dba_objects WHERE object_name like '%DTN%'的初步尝试被证明是不成功的。 以下是有效的策略：

1. 细化列名搜索：

   通过以下方式改进列名称搜索：

   <code class="language-sql">SELECT owner, table_name, column_name 
   FROM all_tab_columns 
   WHERE column_name LIKE '%DTN%';</code>

   这重点关注包含“DTN”的列，可能会标识目标列。

2. 全面的表和列搜索（PL/SQL 方法）：

   这种强大的方法使用 PL/SQL 块和动态 SQL 来搜索每一列：

   <code class="language-sql">SET SERVEROUTPUT ON SIZE 100000
   
   DECLARE
     match_count INTEGER;
   BEGIN
     FOR t IN (SELECT owner, table_name, column_name
                 FROM all_tab_columns
                 WHERE owner NOT IN ('SYS', 'SYSTEM') AND data_type LIKE '%CHAR%') LOOP
   
       EXECUTE IMMEDIATE
         'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
         ' WHERE '||t.column_name||' = :1'
         INTO match_count
         USING '1/22/2008P09RR8';
   
       IF match_count > 0 THEN
         dbms_output.put_line( t.owner || '.' || t.table_name || '.' || t.column_name || ': ' || match_count );
       END IF;
   
     END LOOP;
   
   END;
   /</code>

   注意：添加 WHERE owner NOT IN ('SYS', 'SYSTEM') 子句是为了提高效率，不包括系统表。

3. 优化搜索：

   效率至关重要。 将搜索限制为类似 CHAR 的数据类型（因为目标值是字符串）可显着减少处理时间。

4. 替代方案：每个表的合并查询：

   不要单独的列查询，而是为每个表创建一个查询：

   <code class="language-sql">SELECT * FROM table1
   WHERE column1 = '1/22/2008P09RR8'
      OR column2 = '1/22/2008P09RR8'
      OR column3 = '1/22/2008P09RR8'
      ...;</code>

   这种方法需要事先了解表结构。

这些方法提供了有效的解决方案，用于在 Oracle 数据库中的所有表和列中查找特定值，即使列名最初未知。 请记住调整 LIKE 查询中的 all_tab_columns 子句以反映有关列名称的任何其他信息。

以上是如何高效地查找每个 Oracle 表中所有列的特定值？的详细内容。更多信息请关注PHP中文网其他相关文章！