Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

View gregrahn's full-sized avatar

Greg Rahn gregrahn

156 followers · 14 following
View GitHub Profile
@gregrahn
gregrahn / triangle_counting.sql
Created October 15, 2011 07:01
--
-- 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;
@gregrahn
gregrahn / ASH_plots.R
Created December 20, 2011 18:15
Example on how to read ASH data from Oracle into R and do scatter plots
library(RJDBC)
#
# set up the JDBC connection
# configure this for your env
#
drv <-JDBC("oracle.jdbc.driver.OracleDriver","/Users/grahn/code/jdbc/ojdbc6.jar")
conn<-dbConnect(drv,"jdbc:oracle:thin:@zulu.us.oracle.com:1521:orcl","grahn","grahn")
#
# import the data into a data.frame
@gregrahn
gregrahn / rowgen.sql
Created February 10, 2012 22:55
row generator using recursive subquery factoring (recursive with / recursive CTE) in Oracle. More: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#BABCDJDB
WITH RecursiveCTE (rnum) AS
(
SELECT 1 AS rnum
FROM dual
UNION ALL
SELECT rnum + 1
FROM RecursiveCTE
WHERE rnum < 24
)
SELECT rnum
@gregrahn
gregrahn / emp2.sql
Created February 27, 2012 15:35
create table emp2 as select * from emp;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
commit;
alter table emp2 parallel 2;
@gregrahn
gregrahn / scott_emp2_cursors.sql
Created February 27, 2012 15:58
col CURSOR_TYPE for a15
col PROGRAM for a36
select
s.program,
o.sql_id,
o.sql_exec_id,
o.cursor_type,
o.sql_text
from
@gregrahn
gregrahn / scott_emp2_cursors.txt
Created February 27, 2012 16:00
--
-- open EMP2 tab & browse DATA tab, notice there are 2 open cursors, noting the SQL_EXEC_ID numbers
--
PROGRAM SQL_ID SQL_EXEC_ID CURSOR_TYPE SQL_TEXT
------------------------------------ ------------- ----------- --------------- ------------------------------------------------------------
SQL Developer apdcq2s3sxn9y 16777216 OPEN SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
oracle@localhost.localdomain (P000) apdcq2s3sxn9y 16777216 OPEN-RECURSIVE SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
oracle@localhost.localdomain (P001) apdcq2s3sxn9y 16777216 OPEN-RECURSIVE SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
SQL Developer apdcq2s3sxn9y 16777217 OPEN SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
@gregrahn
gregrahn / query2csv.R
Created April 14, 2012 06:28
example of using R to save query results to csv file
library(RJDBC)
drv <-JDBC("oracle.jdbc.driver.OracleDriver","/Users/grahn/code/jdbc/ojdbc6.jar")
conn<-dbConnect(drv,"jdbc:oracle:thin:@grahn-dev.us.oracle.com:1521:orcl","scott","tiger")
data <-dbGetQuery(conn, "select * from emp")
write.table(data, file="emp.csv", sep = ",", row.names=FALSE)
@gregrahn
gregrahn / demo_rdbms.mk
Created August 20, 2012 21:46
demo_rdbms.mk from Oracle 11.2
#
# Example for building demo OCI programs:
#
# 1. All OCI demos (including extdemo2, extdemo4 and extdemo5):
#
# make -f demo_rdbms.mk demos
#
# 2. A single OCI demo:
#
# make -f demo_rdbms.mk build EXE=demo OBJS="demo.o ..."
@gregrahn
gregrahn / gist:3877498
Created October 12, 2012 05:34
ethtool eth0
$ sudo ethtool eth0
Settings for eth0:
Supported ports: [ TP ]
Supported link modes: 10baseT/Half 10baseT/Full
100baseT/Half 100baseT/Full
1000baseT/Full
Supports auto-negotiation: Yes
Advertised link modes: 10baseT/Half 10baseT/Full
100baseT/Half 100baseT/Full
1000baseT/Full
@gregrahn
gregrahn / create_tables.sql
Created December 2, 2012 06:05
Experiment to see if it is possible to rewrite the SQL query to not use a join and compute the count(distinct) in a single pass of the data.
drop table t2;
create table t2
(
day_id int not null,
time_id date not null,
begin_time date not null,
end_time date not null
);
begin