如何有效率地尋找每個 Oracle 表中所有列的特定值？

在每個 Oracle 表的每一列中尋找特定值

本指南說明如何在大型 Oracle 資料庫中每個資料表的每個欄位中搜尋特定值（「1/22/2008P09RR8」）。 挑戰在於未知的列名稱和龐大的資料量。

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

1. 細化列名搜尋：

   透過以下方式改善列名稱搜尋：

   SELECT owner, table_name, column_name 
   FROM all_tab_columns 
   WHERE column_name LIKE '%DTN%';

   這重點在於包含「DTN」的列，可能會標識目標列。

2. 全面的表格和欄位搜尋（PL/SQL 方法）：

   這個強大的方法使用 PL/SQL 區塊和動態 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;
   /

   注意：新增 WHERE owner NOT IN ('SYS', 'SYSTEM') 子句是為了提高效率，不包括系統表。

3. 最佳化搜尋：

   效率至關重要。 將搜尋限制為類似 CHAR 的資料類型（因為目標值是字串）可顯著減少處理時間。

4. 替代方案：每個表格的合併查詢：

   不要單獨的列查詢，而是為每個表建立一個查詢：

   SELECT * FROM table1
   WHERE column1 = '1/22/2008P09RR8'
      OR column2 = '1/22/2008P09RR8'
      OR column3 = '1/22/2008P09RR8'
      ...;

   這種方法需要事先了解表格結構。

這些方法提供了有效的解決方案，用於在 Oracle 資料庫中的所有表和列中查找特定值，即使列名最初未知。 請記住調整 LIKE 查詢中的 all_tab_columns 子句以反映有關列名稱的任何其他資訊。

以上是如何有效率地尋找每個 Oracle 表中所有列的特定值？的詳細內容。更多資訊請關注PHP中文網其他相關文章！