Oracle 在类似条件下连接表格非常慢

嗨，Oracle大师!

我有两张表:
1) 大: 帐户，200万行，包含这样的帐号:
123467896546
698746516874
685497964116
748794251631
...

2) 小: 模式，2000行，包含这样的帐户模式:
12 __ 465%
7894%
4646 _ 82%

任务:

根据条件将帐户加入模式
WHERE accounts.accounts like patterns.pattern

结果是帐户表的70% (140 mln行)

问题: 非常慢
计划: 2个FTS嵌套循环

问: 如何优化？

提前谢谢!

---------- 更新:

以下是LiveSQL示例:

-创建测试数据-帐户
创建表帐户为
选择
替换 (时间戳，'-'，')，':'，') | | 替换 (时间戳，'-'，')，':'，') a
从所有对象中
;

-由于LiveSQL限制空间，我们必须通过交叉连接来模拟大表:
创建查看大账户为
选择t.a 从账户t, accounts t2
其中rownum <= 200000000
;

-创建测试数据模式
创建表格模式为
选择 distinct substr(a,1,13) || '%' as p
从账户


-- 有问题的查询嵌套循环，非常慢:
选择* from big_accounts acc
内部连接模式pat
在acc上。像帕特一样。p

问题: 如何优化，如果可能的话？

首先，一些观察结果:

-您正在选择200M + 行表的70% (~ 140M行)

那是一大块数据。无论你做什么，都需要一段时间来处理这个问题。选择如此大的表格百分比意味着对帐户进行全面扫描可能是最好的方法。

-将该数据量传输到客户端需要时间

如果我使用返回75k行的较小示例，则查询在SQL * Plus中需要〜2分钟，等等:

create table accounts as   
select  
replace(replace(timestamp,'-',''),':','') || replace(replace(timestamp,'-',''),':','') a  
from all_objects  
;

create table patterns as  
select  distinct substr(a,1,13) || '%'   as p  
from accounts  
;

exec dbms_stats.gather_table_stats(user, 'accounts');
exec dbms_stats.gather_table_stats(user, 'patterns');

set term off
alter session set tracefile_identifier = chris;
exec dbms_monitor.session_trace_enable(waits => true);
select * from accounts acc 
inner join patterns pat 
on acc.a like pat.p;
复制

但是，正如格式化的tracefile所显示的那样，大部分时间都是与客户端来回进行的 (请参阅最后的 “SQL * Net来自客户端的消息” 值上的114):

select * from accounts acc
inner join patterns pat
on acc.a like pat.p

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      753      3.83       3.88          0     152089          0       75116
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      755      3.84       3.88          0     152089          0       75116

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     75116      75116      75116  NESTED LOOPS  (cr=152089 pr=0 pw=0 time=4030450 us cost=24262 size=6610208 card=150232)
     75116      75116      75116   TABLE ACCESS FULL ACCOUNTS (cr=1105 pr=0 pw=0 time=117288 us cost=108 size=2178364 card=75116)
     75116      75116      75116   TABLE ACCESS FULL PATTERNS (cr=150984 pr=0 pw=0 time=3432685 us cost=0 size=30 card=2)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     753        0.00          0.00
  SQL*Net message from client                   753        0.32        114.49
复制

而如果我在PL/SQL中循环查询，整个事情在 ~ 2s内完成!

begin
  for rws in (
    select * from accounts acc
    inner join patterns pat
    on acc.a like pat.p
  ) loop
    null;
  end loop;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.02          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.14          0.14

SELECT *
FROM
 ACCOUNTS ACC INNER JOIN PATTERNS PAT ON ACC.A LIKE PAT.P


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      752      1.79       1.80         92     152087          0       75116
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      754      1.80       1.80         92     152087          0       75116

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     75116      75116      75116  NESTED LOOPS  (cr=152087 pr=92 pw=0 time=1904147 us cost=24262 size=6610208 card=150232)
     75116      75116      75116   TABLE ACCESS FULL ACCOUNTS (cr=1104 pr=92 pw=0 time=49706 us cost=108 size=2178364 card=75116)
     75116      75116      75116   TABLE ACCESS FULL PATTERNS (cr=150983 pr=0 pw=0 time=1603095 us cost=0 size=30 card=2)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        15        0.00          0.00
  db file scattered read                         30        0.00          0.00
复制

这是因为所有这些网络往返都消失了。

你在用这些数据做什么？你真的需要全部选择吗？您可以在PL/SQL中处理它以减少网络影响吗？

-此查询中没有其他谓词

因此，索引策略的帮助空间不大。

考虑到所有这些，您可以研究以下一些内容:

Parallel

如果您的服务器上有容量，则并行运行查询可能有助于减少运行时

Materialized View

预先计算联接将减少查询所做的一些工作。不过，你仍然需要获取大量数据，所以不要期待奇迹。

Manually Splitting It Up (DIY Parallelism)

一次取少量，然后手动将结果重新缝合在一起。