Oracle：通過不同的鍵物理重組大表的行
我有一個巨大的表
RECORD——幾十億行——其中包含大量連續載入的數據。我可以從序列中分配一個主鍵RECORD_ID，或者使用自然複合鍵BATCH_NUM + RECORD_NUM，這給了我一個很好的、完整的表，其中包含數千條記錄的批處理只跨越幾個物理塊，這對於下一個批處理感知步驟很有用。但後來我需要根據另一個欄位值 some 將任意批次的行組合在一起
RECORD_KEY。有數億個不同RECORD_KEY的 s 分佈在表的行中，因此如果我想選擇 10 行相同的 s，RECORD_KEY幾乎肯定需要檢索 10 個物理塊。由於表的大小，這也不容易被記憶體，因此它是 10 次物理讀取（加上索引遍歷的成本）。這顯然非常緩慢。例子：
表記錄
RECORD_ID是序列中的主鍵BATCH_NUM並且RECORD_NUM是唯一約束（並且可能是主鍵）RECORD_KEY是一個索引列樣本數據：
| RECORD_ID | BATCH_NUM | RECORD_NUM | RECORD_KEY | 1 | 1 | 1 | 987654321 | 2 | 1 | 2 | 876543219 | 3 | 1 | 3 | 765432198 | 4 | 2 | 1 | 654321987 | 5 | 2 | 2 | 543219876 ... | 100000006 | 3000003 | 2 | 432198765 | 100000007 | 3000003 | 3 | 876543219 | 100000008 | 3000003 | 4 | 321987654 ... | 200000009 | 6000004 | 3 | 219876543 | 200000010 | 6000004 | 4 | 876543219 | 200000011 | 6000004 | 5 | 198765432 ...這個 SQL 命令會很快，因為它只需要檢索一個物理塊：
select RECORD_ID from RECORD where BATCH_NUM = 1這個 SQL 命令會很慢，因為它必須檢索三個物理塊——每個檢索的行一個：
select RECORD_ID from RECORD where RECORD_KEY = 876543219編輯：
以上只是一個例子。通常我會：
- 每 BATCH_NUM 數千行
- 每個 RECORD_KEY 數十行
- 每次查找數千行，按 RECORD_KEY 的 BATCH_NUM
SQL 看起來像這樣，都檢索大約 1000 行：
select RECORD_ID from RECORD where BATCH_NUM = 123456 select RECORD_ID from RECORD where RECORD_KEY in ( select COLUMN_VALUE from TABLE(batch_num_tbl) -- 100 values )執行計劃看起來既合理又相似——使用各自的索引。然而，雖然 BATCH_NUM 查找需要不到 1 秒的時間來執行，但 RECORD_KEY 查找需要大約 20 秒。
我考慮過的選項：
- 分區：我可以在
RECORD_KEY. 它將使記錄彼此靠近一點，增加它們在同一個塊中的機會，並啟用一些分區連接。可能會有所幫助，但不會為我解決問題。- 索引組織表：因為它需要主鍵，所以我必須製作主鍵
RECORD_KEY + RECORD_ID。此外，查找BATCH_NUM也會變得緩慢。- 重新插入行：一個相當醜陋的解決方案，我會定期選擇按 排序的行
RECORD_KEY，刪除它們並插入附加它們，有效地將它們彼此相鄰。請注意，我不是任何有經驗的 DBA，只是一個被這個不平凡的 DB 問題困擾的開發人員。
在 上創建索引
(record_key, record_id)。這樣，oracle 很可能只使用索引來回答您執行緩慢的查詢。您可以向該索引添加更多列，以便您還可以獲得所需的內容。希望您不需要所有列。請注意，此索引對您的負載性能有什麼影響。
如果你發現一個簡單的覆蓋索引（它會立即解決你所有的查詢性能問題）對 INSERT 性能有不可接受的影響，你可以在表中引入一個人為的 ‘clustering’ 列：
create table foo( record_id integer not null constraint u_foo unique , record_key integer not null , record_id_grp integer not null -- artificial clustering column , bar varchar(100) default lpad('A',10,'A') , constraint pk_foo primary key(record_id_grp,record_key,record_id) , check(record_id_grp=trunc(record_id/10000)) ) organization index;create trigger trg_foo before insert on foo for each row begin :new.record_id_grp := trunc(:new.record_id/10000); end; /insert into foo(record_id, record_key) select level, floor(dbms_random.value(1,1000)) from dual connect by level<=100000;select /*+ gather_plan_statistics index_ss(foo pk_foo) */ count(*) from foo where record_key=10;| 計數(*) | | -------: | | 106 |select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));| 計劃表輸出 | | :------------------------------------------------------------------------------------ | | SQL_ID b1za5v8u2ccxj，子編號 0 | | ------------------------------------- | | select /*+ gather_plan_statistics index_ss(foo pk_foo) */ count(*) from | | foo 其中record_key=10 | | | | 計劃雜湊值：2217311945 | | | | ------------------------------------------------------------------------------------- | | | 身份證 | 操作 | 姓名 | 開始 | 電子行 | A-行 | 時間 | 緩衝器 | | | ------------------------------------------------------------------------------------- | | | 0 | 選擇聲明 | | 1 | | 1 |00:00:00.01 | 21 | | | | 1 | 排序聚合 | | 1 | 1 | 1 |00:00:00.01 | 21 | | | |* 2 | 索引跳過掃描| PK_FOO | 1 | 100 | 106 |00:00:00.01 | 21 | | | ------------------------------------------------------------------------------------- | | | | 謂詞資訊（由操作 id 標識）：| | --------------------------------------------------- | | | | 2 - 訪問（“RECORD_KEY”=10）| | 過濾器（“RECORD_KEY”=10）| | |dbfiddle在這裡
請注意，即使返回了大約 100 行，也只觸及了 21 個塊。由
trunc(record_id/X)_ 您可能很幸運，發現您可以簡單地BATCH_NUM用於集群。請注意，您不需要使用 IOT — 您可以使用以
RECORD_ID_GRP,RECORD_KEY(或BATCH_NUM,RECORD_KEY) 開頭的覆蓋索引。對於數十億行，我假設儲存大小翻倍會帶來成本——實現集群的單個 IOT 將是佔用空間最少的解決方案。