------------------------------------------
-- Create CASEINFO test table 
------------------------------------------
DROP TABLE CASEINFO;
CREATE TABLE CASEINFO
(
       CASEID INTEGER,
       PERSON INTEGER,
       SEX INTEGER,
       FED INTEGER, REG INTEGER,
       PHONE1 VARCHAR2(24),
       PHONE2 VARCHAR2(24),
       PHONE3 VARCHAR2(24),
       PHONE4 VARCHAR2(24)
);
ALTER TABLE CASEINFO ADD PRIMARY KEY (CASEID) USING INDEX;
CREATE INDEX I_PERSON ON CASEINFO (PERSON);
CREATE INDEX I_SEX ON CASEINFO (SEX);
CREATE INDEX I_FED ON CASEINFO (FED);
CREATE INDEX I_REG ON CASEINFO (REG);
CREATE INDEX I_FED_REG ON CASEINFO (FED,REG);
CREATE INDEX I_PHONE1 ON CASEINFO (PHONE1);
CREATE INDEX I_PHONE2 ON CASEINFO (PHONE2);
CREATE INDEX I_PHONE3 ON CASEINFO (PHONE3);
CREATE INDEX I_PHONE4 ON CASEINFO (PHONE4);
CREATE INDEX I_PHONES_12 ON CASEINFO (PHONE1,PHONE2);
CREATE INDEX I_PHONES_13 ON CASEINFO (PHONE1,PHONE3);
CREATE INDEX I_PHONES_14 ON CASEINFO (PHONE1,PHONE4);
CREATE INDEX I_PHONES_23 ON CASEINFO (PHONE2,PHONE3);
CREATE INDEX I_PHONES_24 ON CASEINFO (PHONE2,PHONE4);
CREATE INDEX I_PHONES_34 ON CASEINFO (PHONE3,PHONE4);
/

------------------------------------------
-- fill CASEINFO with sample data
------------------------------------------
DECLARE
  I INTEGER;
  R1 INTEGER;
  R2 INTEGER;
  N INTEGER;
  M INTEGER;
  OBJECTS_PER_PERSON INTEGER;
  APERSON INTEGER;
  PERSON_COUNT INTEGER;
  CRIMINAL_PERCENT INTEGER;
  RNDSEED NUMBER(20);
  APHONE VARCHAR2(24);
BEGIN
  OBJECTS_PER_PERSON := 8;
  PERSON_COUNT := 250000;
  CRIMINAL_PERCENT := 3;
  N := OBJECTS_PER_PERSON * PERSON_COUNT;
  DBMS_OUTPUT.PUT_LINE('Record count '||TO_CHAR(N)||' ...');
  FOR I IN 0..N-1 LOOP
    APERSON := MOD(I, PERSON_COUNT);
    INSERT INTO CASEINFO 
      (
        CASEID,
        PERSON,
        SEX,
        FED,
        REG,
        PHONE1,
        PHONE2,
        PHONE3,
        PHONE4
      ) 
           VALUES
           (
             I+1,
             APERSON+1,
             MOD(APERSON,2),
             MOD(I,13)+1,
             MOD(I,97)+1,
             '+7727'||
               TRIM(TO_CHAR(APERSON+1,'0000009'))||'1',
             '+7727'||
               TRIM(TO_CHAR(APERSON+1,'0000009'))||'2',
             '+7727'||
               TRIM(TO_CHAR(APERSON+1,'0000009'))||'3',
             '+7727'||
               TRIM(TO_CHAR(APERSON+1,'0000009'))||'4'
           );
    IF MOD(I,1000)=0 THEN
      COMMIT;
      --DBMS_OUTPUT.PUT_LINE('Done '||TO_CHAR(I)||' ...');
    END IF;
  END LOOP;
  COMMIT;
  ------------------------------------------
  -- Lets make "CRIMINAL" CASEs among which we're searching for
  --on 11.2.0.1 next line isn't working
  --SELECT hsecs INTO RNDSEED FROM gv$timer;
  DBMS_RANDOM.INITIALIZE(131071);
  M := TRUNC(N * CRIMINAL_PERCENT / 100, 0);
  DBMS_OUTPUT.PUT_LINE('Criminal count '||TO_CHAR(M)||' ...');
  FOR I IN 0..M-1 LOOP
    --Phone1 => Phone2
    R1 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    R2 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    APHONE := '+7-CRIMINAL-'||
        TRIM(TO_CHAR(R1,'0000009'))||'-12';
    UPDATE CASEINFO SET PHONE1=APHONE WHERE (CASEID=R1);
    UPDATE CASEINFO SET PHONE2=APHONE WHERE (CASEID=R2);
    --Phone1 => Phone3
    R1 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    R2 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    APHONE := '+7-CRIMINAL-'||
        TRIM(TO_CHAR(R1,'0000009'))||'-13';
    UPDATE CASEINFO SET PHONE1=APHONE WHERE (CASEID=R1);
    UPDATE CASEINFO SET PHONE3=APHONE WHERE (CASEID=R2);
    --Phone1 => Phone4
    R1 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    R2 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    APHONE := '+7-CRIMINAL-'||
        TRIM(TO_CHAR(R1,'0000009'))||'-14';
    UPDATE CASEINFO SET PHONE1=APHONE WHERE (CASEID=R1);
    UPDATE CASEINFO SET PHONE4=APHONE WHERE (CASEID=R2);
    --Phone2 => Phone3
    R1 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    R2 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    APHONE := '+7-CRIMINAL-'||
        TRIM(TO_CHAR(R1,'0000009'))||'-23';
    UPDATE CASEINFO SET PHONE2=APHONE WHERE (CASEID=R1);
    UPDATE CASEINFO SET PHONE3=APHONE WHERE (CASEID=R2);
    --Phone2 => Phone4
    R1 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    R2 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    APHONE := '+7-CRIMINAL-'||
        TRIM(TO_CHAR(R1,'0000009'))||'-24';
    UPDATE CASEINFO SET PHONE2=APHONE WHERE (CASEID=R1);
    UPDATE CASEINFO SET PHONE4=APHONE WHERE (CASEID=R2);
    --Phone3 => Phone4
    R1 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    R2 := MOD(ABS(DBMS_RANDOM.RANDOM), N)+1;
    APHONE := '+7-CRIMINAL-'||
        TRIM(TO_CHAR(R1,'0000009'))||'-34';
    UPDATE CASEINFO SET PHONE3=APHONE WHERE (CASEID=R1);
    UPDATE CASEINFO SET PHONE4=APHONE WHERE (CASEID=R2);
    --
    COMMIT;
  END LOOP;
  COMMIT;
  DBMS_RANDOM.TERMINATE;
END;
/

------------------------------------------
-- Check test data counters
------------------------------------------
SELECT 'TOTAL',count(*) from CASEINFO
UNION ALL
SELECT 'LEGAL',count(*) from CASEINFO where 
  (PHONE1 = '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'1') AND 
  (PHONE2 = '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'2') AND 
  (PHONE3 = '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'3') AND
  (PHONE4 = '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'4')
UNION ALL
SELECT 'CRIMINAL',count(*) from CASEINFO where 
  (PHONE1 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'1') OR
  (PHONE2 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'2') OR
  (PHONE3 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'3') OR
  (PHONE4 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'4')
UNION ALL
SELECT 'PHONE1',count(*) from CASEINFO where 
  (PHONE1 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'1')
UNION ALL
SELECT 'PHONE2',count(*) from CASEINFO where 
  (PHONE2 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'2')
UNION ALL
SELECT 'PHONE3',count(*) from CASEINFO where 
  (PHONE3 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'3')
UNION ALL
SELECT 'PHONE4',count(*) from CASEINFO where 
  (PHONE4 <> '+7727'||
    TRIM(TO_CHAR(PERSON,'0000009'))||'4');

------------------------------------------
-- We need: CASEs for different PERSONs having same PHONEs
------------------------------------------

------------------------------------------
-- Problematic SQL SELECT - should return DISTINCT CASEs. Works very poorly.
------------------------------------------
select distinct b.caseid
from CASEINFO a, CASEINFO b 
where (a.person<>b.person) and (a.sex=b.sex) and 
(
      (a.phone1=b.phone1) or 
      (a.phone1=b.phone2) or 
      (a.phone1=b.phone3) or 
      (a.phone1=b.phone4) or 

      (a.phone2=b.phone1) or 
      (a.phone2=b.phone2) or 
      (a.phone2=b.phone3) or 
      (a.phone2=b.phone4) or

      (a.phone3=b.phone1) or 
      (a.phone3=b.phone2) or 
      (a.phone3=b.phone3) or
      (a.phone3=b.phone4) or

      (a.phone4=b.phone1) or 
      (a.phone4=b.phone2) or 
      (a.phone4=b.phone3) or 
      (a.phone4=b.phone4)
);