Oracle中的最長前綴搜尋

我有一個為大量區域定義的電話號碼前綴列表（在由 gvcode 和 cgi 定義的查詢中）。我需要有效地找到與給定號碼 PHONE_NR 匹配的最長前綴。

我在欄位數字上使用倒置的 LIKE 子句（其中包含 +48%、+49%、+1%、+1232% 等形式的前綴）。

因此我不能在該欄位上使用普通索引。

通過在 gvcode 和 cgi 欄位（它們是主鍵的一部分（前兩個列））上使用 IOT，我設法獲得了實質性的改進。我還查看了一些 oracle 文本索引，但在表中找不到與較長輸入和較短前綴匹配的索引。

是否有任何其他方法可以比這種方法更快地執行此類搜尋。

這是給出所有匹配前綴列表的查詢（我隨後按數字長度對其進行排序）。

 select  t.gvcode,  t.digits
               from NUMBERS t 
                   where 
                       t.gvcode=ZONE_SET_CODE 
                       and t.cgi=cgi_f
                      and ( PHONE_NR like t.digits)
                        order by length(digits) desc 

這在電信領域（我工作的地方）經常出現。您可以使用多種方法來獲得合理的性能，同時仍完全保留在數據庫中。

在所有情況下，您都需要從 DIGITS 列中刪除“%”，這個額外的字元根本對您沒有幫助。

**選項 1.**迭代函式。

DECLARE
   p VARCHAR(30);                
BEGIN
   p := :full_number;

   WHILE (LENGTH (p) > 0)
   LOOP
       BEGIN
           SELECT [whatever]
           INTO :whatever
           FROM numbers t
           WHERE digits = p;

           RETURN;
       EXCEPTION
           WHEN NO_DATA_FOUND THEN
               p := SUBSTR (p, 0, LENGTH (p) - 1);
       END;                    
   END LOOP;
END;

假設您在 DIGITS 上有一個不錯的索引，這種函式的性能相當不錯。

**選項 1.**開始/結束範圍鍵。創建兩個輔助列（BOTH INDEXED）。

DIGITS_RANGE_START
DIGITS_RANGE_END

這些是您通過插入/更新觸發器設置的派生值。

DIGITS_RANGE_START should be automatically set to DIGITS || CHAR(0)
DIGITS_RANGE_END should be automatically set to DIGITS || CHAR(255)

現在您的查詢可以是

SELECT ... FROM numbers t 
WHERE (number_to_match || CHR(1) > digits_range_start) 
 AND (number_to_match || CHR(1) < digits_range_end) AND ... [other conditions]
ORDER BY LENGTH (digits) DESC;

這將以一個很好的比較匹配來擊中索引，並且 Oracle 應該能夠做得很好。它可能會返回多個匹配項，您需要獲取第一個匹配項。

更多選擇

如果你想變得真正聰明和真正快速，另請參閱這個著名的執行緒，了解索引組織表的花哨選項。

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4246230700346756268

引用自：https://dba.stackexchange.com/questions/42997