它是正常的合并或循环表，有400 K记录花了8小时以上？

Question

我有一张桌子，里面有大约39万张记录。在这个表中，有一个列类型是oracle的sdo_geometry包，在这个列中有一个点具有X和Y坐标。因此，在我的工作中，有一个任务交给我，就是用SDO_RELATE函数将这些点相交，并找到id's，在所有这些记录中只有三个以上的交叉点。因此，如果我们有point1 point2 point3 ...pointN，那么point1将检查所有剩余的...pointN。必须检查point2本身之后的所有剩余部分。我找到了两种解决办法，但这两种方法都持续了8个多小时，我必须在30分钟内完成。有没有类似的方法来做这个或更快的方式，而不是被要求的卡通产品或什么？因为大约400 K的记录在笛卡尔积之后变成了1600亿

注意:如果point1与point2和point3相交，那么就没有必要检查point2和point3之间的交叉。交叉口可能是累积的。为此，我还添加了一个存储状态的列，因此如果状态为1，则不要迭代该行，但是状态更新过程也会消耗大量时间，在For循环开始时，查询只运行一次，看不到状态更新。

我的备选代码如下：

Merge Into ORAHANCROSSES C
Using (With D as (select T.*, ROWNUM RN from (select MI_PRINX, GEOLOC from ORAHAN order by MI_PRINX) T)
   Select aROW.MI_PRINX  aROW_MI_PRIX, 
          aROW.GEOLOC    aROW_GEOLOC,
          bROW.MI_PRINX  bROW_MI_PRIX, 
          bROW.GEOLOC    bROW_GEOLOC,
          SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) RES
   From   D aROW,
          D bROW
   Where  aROW.RN < bROW.RN
   ) Q
 On    (C.ID_POINT1 = Q.aROW_MI_PRIX and C.ID_POINT2 = Q.bROW_MI_PRIX)
 When Not Matched Then
 Insert Values (Q.aROW_MI_PRIX, Q.bROW_MI_PRIX)
 Where Q.RES = 'TRUE';

第二项是：

declare
counter number(10);
control number(1);
dup number(10);
res varchar2(5);--TRUE or FALSE
BEGIN
counter :=0;
dup :=0;
control :=0;
FOR aRow IN (SELECT mip,startmi,mi_prinx,geoloc,state,rownum FROM ORAHAN where state=0)
 LOOP
  FOR bRow IN (SELECT mip,startmi,mi_prinx,geoloc,state,rownum FROM ORAHAN WHERE state=0 and mi_prinx!=aRow.mi_prinx)
   LOOP
    counter :=counter+1;
    BEGIN
     select SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) into res from dual;
     if (res='TRUE')
      THEN 
        Insert INTO ORAHANCROSSES values (aRow.MI_PRINX,bRow.MI_PRINX);
        UPDATE ORAHAN SET STATE=1 where MI_PRINX=bRow.MI_PRINX; 
        control :=1;
        --dbms_output.put_line(' added');
     END IF;
     EXCEPTION
      WHEN DUP_VAL_ON_INDEX
        THEN
          dup := dup+1;
          --dbms_output.put_line('duplicate');
          --continue;
    END;
  END LOOP;
  IF(control =1)
    THEN
      UPDATE ORAHAN SET STATE=1 WHERE MI_PRINX=aRow.MI_PRINX;
  END IF;
  control :=0;
END LOOP;
dbms_output.put_line('duplicate: '||dup);
dbms_output.put_line('counter: '||counter);
--commit;
END ;

我两个都执行，但时间太长了(在八个小时内，执行仍在进行中)。

我有oracle-11g实时服务器和pl/ server。(数据库不在我的本地pc中。)

Answer 1

我意识到SDO_GEOM_RELATE是旧函数，SDO_RELATE是新函数。SDO_RELATE非常快，但是在SDO_RELATE中没有缓冲区参数。为了给对象缓冲区提供一个参数，我们执行了如下操作；

declare
BEGIN
for curs in (select * from ORAHAN t) loop
 for curs2 in (select *
               from ORAHAN t2
              where SDO_RELATE(t2.geoloc,SDO_GEOM.SDO_BUFFER(curs.geoloc,0.2,0.02,'UNIT=M') , 
              'mask=ANYINTERACT') = 'TRUE'
                and t2.mi_prinx <> curs.mi_prinx) loop                    
  Insert INTO ORAHANCROSSES
   values
   (curs.Mip, curs.Startmi, curs2.Mip, curs2.Startmi);
  commit;
 end loop;
end loop;
END;

现在，整个迭代在一小时内完成。

Answer 2

下面的算法怎么样?我刚才考虑过这个算法，并在纸上涂鸦( 10点有两个部分，但总结速度比O(N)=n平方要快得多)：

1. 开始迭代400 K点，找出最小/最大X和Y坐标
2. 再次迭代你的点，在每个Y坐标下创建一条从Xmin到Xmax的线(在内存中)
3. 在第三次迭代中，在每个X上创建(在内存中)从yMin到yMax的行
4. 对于每条水平线，选择那些上面有3+点的
5. 对每条垂直线做同样的操作
6. 现在，您有两个，可能要小得多的列表，如果每条水平线上有相同的3个(或更多)点，则可以检查每条水平线和垂直线。