public
Created

  • Download Gist
triangle_counting.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
--
-- 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
;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.