Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
--
-- 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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment