triangle_counting.sql

--
-- triangle_counting.sql
--
-- get flat file from http://www.vertica.com/benchmark/TriangleCounting/edges.txt.gz
-- gunzip & place in /tmp
--

create or replace directory GRAPH_DATA as '/tmp';

DROP TABLE ET_EDGES;

CREATE TABLE ET_EDGES
(
         "SOURCE"                         NUMBER
        ,"DEST"                           NUMBER
)
ORGANIZATION EXTERNAL
(
        TYPE oracle_loader
        DEFAULT DIRECTORY GRAPH_DATA
        ACCESS PARAMETERS
        (
                RECORDS DELIMITED BY NEWLINE
                NOLOGFILE
                NOBADFILE
                FIELDS TERMINATED BY ' '
                MISSING FIELD VALUES ARE NULL
                REJECT ROWS WITH ALL NULL FIELDS
                (
                         "SOURCE"
                        ,"DEST"
                )
        )
        LOCATION ('edges.txt')
)
REJECT LIMIT 0;

exec dbms_stats.lock_table_stats(user,'ET_EDGES');

drop table edges;

create table edges (source int not null, dest int not null)
storage(initial 8M)
compress for query
parallel
nologging
;

insert /*+ append */ into edges select * from et_edges;
commit;

exec dbms_stats.gather_table_stats(user,'EDGES');

-- 86,220,856 rows should be loaded
select count(*) from edges;

--
-- find the # of blocks for compression analysis
-- (assumes default of 8k blocks)
--  
select (8192 * blocks)/1024/1024 as size_in_mbytes from user_tables where table_name='EDGES';

--
-- original query
--
select count(*)
from edges e1
join edges e2 on e1.dest = e2.source and e1.source < e2.source
join edges e3 on e2.dest = e3.source and e3.dest = e1.source and e2.source < e3.source
;

--
-- modified query
--
with
  e1 as (select * from edges where source < dest),
  e2 as (select * from edges where source < dest),
  e3 as (select * from edges where source > dest)
select count(*)
from e1
join e2 on (e1.dest = e2.source)
join e3 on (e2.dest = e3.source)
where e3.dest = e1.source
;