Should i expect to execute of 160billion iteration from oracle 11g?

Question

I have a table that contains approximately 390 000 records and up to now I found 3 different algorithm(solution) for my problem. Each algorithm require cartesian product of table with itself. I am wondering only that; should i expect from oracle 11g to execute a query requires 160 Billion iteration or what? Because for one week I try a lot of things but whatever I do it is not end even after 8 hour execution.

If this is normal for this much process I maybe start thinking of a new algorithm because I must ended up in approximately 30 minutes.

My last and better among three algorithm is;

declare
loopCount number(10);
remainingRows number(10);
dup number(10);
crosses number(10);
res varchar2(5);--TRUE or FALSE
BEGIN
loopCount :=0;
remainingRows :=0;
crosses :=0;
dup := 0;
res :='FALSE';
SELECT COUNT(*) INTO remainingRows FROM ORAHAN;--376497 records
WHILE(remainingRows ^= 0) LOOP
 FOR aRow IN (SELECT mip,startmi,mi_prinx,geoloc,rownum FROM ORAHAN where rownum=1)  LOOP
  FOR bRow IN (SELECT mip,startmi,mi_prinx,geoloc FROM ORAHAN WHERE Mi_Prinx ^= aRow.Mi_Prinx) LOOP
    BEGIN
      --loopCount := loopCount+1;
      select SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) into res from dual;
      IF (res='TRUE') THEN
        Insert INTO ORAHANCROSSES values (aRow.Mip,aRow.Startmi,bRow.Mip,bRow.Startmi);
        DELETE FROM ORAHAN WHERE MI_PRINX=bRow.MI_PRINX;
        COMMIT;
        crosses := crosses+1;
        END IF;
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          dup := dup+1;
    END;
    remainingRows := remainingRows-1;
  END LOOP;
DELETE FROM ORAHAN WHERE MI_PRINX=aRow.MI_PRINX;
END LOOP;
SELECT COUNT(*) INTO remainingRows FROM ORAHAN;
END LOOP;
dbms_output.put_line('crosses: ' || crosses);
dbms_output.put_line('duplicate: ' || dup);
END;

I execute this and in 3 hour it is delete 604 record from source table and insert 99 row to target table. There is 376342 remaining rows.

Answer 1

We use SDO_RELATE function which is very fast against SDO_GEOM_RELATE. But SDO_REALTE has no parameter as buffer. We use SDO_GEOM.SDO_BUFFER for giving 2cm buffer to the target object. Now it takes one hour for intersection all records with each other. Here is working code;

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;