Golang Oracle Performance Extensions

Purpose: Demo Go Oracle database driver OCI Performance Extensions.

We will do a benchmark and show you the improvement before and after enabling Prefetching rows.
Also use SQL session trace file to prove that Update Batching works, than is insert many rows at one time.

Info:

Update Batching

You can reduce the number of round-trips to the database, thereby improving application performance, by grouping multiple UPDATE, DELETE, or INSERT statements into a single batch and having the whole batch sent to the database and processed in one trip. This is referred to as 'update batching'.

Prefetching rows

This reduces round-trips to the database by fetching multiple rows of data each time data is fetched. The extra data is stored in client-side buffers for later access by the client. The number of rows to prefetch can be set as desired.
...

Code:

// Copyright 2015, Author: Charlie Database Craftsman. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
/*
Package main demo 2 Oracle OCI Performance Extensions: array interface and Prefetching rows.
- Update Batching
It calls StmtCfg.SetPrefetchRowCount to set Prefetching size for SELECT query.
- Prefetching rows
It is binding slice type data, to pass array to database in single SQL to do Bulk DML, in one context switch.
Notes:
stmtQry.Cfg().SetPrefetchRowCount(uint32(200))  works,
ses.Cfg().StmtCfg.SetPrefetchRowCount(2000) works,
EnvCfg -> SrvCfg are both not work, because code issue, NewStmtCfg() overwrite Env/Srv on session level.
Env.StmtCfg.SetPrefetchRowCount not working.

| Modification History:
|  Date        Who          What
| 11-Jan-2016: Charlie(Yi): Abstract checkErr function,
| 18-Dec-2015: Charlie(Yi): create the package,
*/
package main

import (
 "fmt"
 "gopkg.in/rana/ora.v3"
)

func main() {
 // example usage of the ora package driver
 // connect to a server and open a session
 env, err := ora.OpenEnv(nil)
 defer env.Close()
 checkErr(err)
 srvCfg := ora.NewSrvCfg()
 srvCfg.Dblink = "//localhost/orcl"
 srv, err := env.OpenSrv(srvCfg)
 defer srv.Close()
 checkErr(err)
 sesCfg := ora.NewSesCfg()
 sesCfg.Username = "scott"
 sesCfg.Password = "tiger"
 fmt.Println("Session PrefetchRowCount :", sesCfg.StmtCfg.PrefetchRowCount())
 sesCfg.StmtCfg.SetPrefetchRowCount(uint32(2000))
 ses, err := srv.OpenSes(sesCfg)
 fmt.Println("connected")
 defer ses.Close()
 checkErr(err)
 fmt.Println("Session PrefetchRowCount :", ses.Cfg().StmtCfg.PrefetchRowCount())
 err = ses.Cfg().StmtCfg.SetPrefetchRowCount(uint32(1000))
 checkErr(err)
 fmt.Println("Session PrefetchRowCount :", ses.Cfg().StmtCfg.PrefetchRowCount())
 stmtTbl, err := ses.Prep(
  `declare
  l_sql varchar2(32767);
  l_cnt pls_integer;
begin
  l_sql := 'drop TABLE emp_go purge';
  select count(*) into l_cnt from user_tables where table_name='EMP_GO';
  if l_cnt > 0 then
    execute immediate l_sql;
  end if;
end;`)
 defer stmtTbl.Close()
 checkErr(err)
 rowsAffected, err := stmtTbl.Exe()
 checkErr(err)
 fmt.Println(rowsAffected, " rows Affected. drop table emp_go if exists.")
 rowsAffected, err = ses.PrepAndExe("CREATE TABLE emp_go (empno number(5,0), ename VARCHAR2(50))")
 checkErr(err)
 fmt.Println("table emp_go created")
 rowsAffected, err = ses.PrepAndExe("ALTER SESSION SET TRACEFILE_IDENTIFIER='go1'")
 checkErr(err)
 //SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
 rowsAffected, err = ses.PrepAndExe("begin dbms_monitor.session_trace_enable( WAITS=>TRUE, binds=>true); end;")
 checkErr(err)
 tx1, err := ses.StartTx()
 stmt, err := ses.Prep("INSERT INTO emp_go (empno, ename) VALUES (:N1, :C1)")
 defer stmt.Close()
 checkErr(err)
 fmt.Println("Demo bulk/batch insert. Bulk DML with slice (array in Go)")
 l_no := make([]int64, 4)
 l_name := make([]ora.String, 4)
 for n, _ := range l_no {
  l_no[n] = int64(n)
  l_name[n] = ora.String{Value: fmt.Sprintf("Mr. %v", n)}
  fmt.Println(n)
 }
 rowsAffected, err = stmt.Exe(l_no, l_name)
 checkErr(err)
 fmt.Println(rowsAffected, " rows add.")
 tx1.Commit()
 fmt.Println("commit")
 fmt.Println("Demo fetch records")
 stmtQry, err := ses.Prep("SELECT /* set PrefetchRowCount = 0 */ empno, ename FROM emp_go")
 defer stmtQry.Close()
 checkErr(err)
 err = stmtQry.Cfg().SetPrefetchRowCount(uint32(0))
 checkErr(err)
 fmt.Println("stmtQry.Cfg().PrefetchRowCount default:", stmtQry.Cfg().PrefetchRowCount())
 rset, err := stmtQry.Qry()
 checkErr(err)
 for rset.Next() {
  fmt.Println(rset.Row[0], rset.Row[1])
 }
 checkErr(rset.Err)
 stmtQry, err = ses.Prep("SELECT /* set PrefetchRowCount = 200 */ empno, ename FROM emp_go")
 defer stmtQry.Close()
 checkErr(err)
 err = stmtQry.Cfg().SetPrefetchRowCount(uint32(200))
 checkErr(err)
 fmt.Println("stmtQry.Cfg().SetPrefetchRowCount(200)", stmtQry.Cfg().PrefetchRowCount())
 rset, err = stmtQry.Qry()
 checkErr(err)
 for rset.Next() {
  fmt.Println(rset.Row[0], rset.Row[1])
 }
 checkErr(rset.Err)
 rowsAffected, err = ses.PrepAndExe("begin dbms_monitor.session_trace_disable(); end;")
 checkErr(err)
 rset, err = ses.PrepAndQry("SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File'")
 for rset.Next() {
  fmt.Println(rset.Row[0])
 }
}


func checkErr(err error) {
 if err != nil {
  panic(err)
 }
}

Output:

Session PrefetchRowCount : 0
connected
Session PrefetchRowCount : 2000
Session PrefetchRowCount : 1000
0  rows Affected. drop table emp_go if exists.
table emp_go created
Demo bulk/batch insert. Bulk DML with slice (array in Go)
0
1
2
3
4  rows add.
commit
Demo fetch records
stmtQry.Cfg().PrefetchRowCount default: 0
0 Mr. 0
1 Mr. 1
2 Mr. 2
3 Mr. 3
stmtQry.Cfg().SetPrefetchRowCount(200) 200
0 Mr. 0
1 Mr. 1
2 Mr. 2
3 Mr. 3
/home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_17092_go1.trc
.
SQL session trace:

tkprof /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_17092_go1.trc rpt.txt
.
TKProf report:
.
Before,  PrefetchRowCount = 0.

SELECT /* set PrefetchRowCount = 0 */ empno, ename
FROM emp_go

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0         10          0           4
.
After SetPrefetchRowCount = 200.
.
SELECT /* set PrefetchRowCount = 200 */ empno, ename
FROM emp_go

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          8          0           4
.
Bulk INSERT,
.
INSERT INTO emp_go (empno, ename)
VALUES (:N1, :C1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0             4         43           4
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00        

Reference:
.
Oracle driver basic, http://mujiang.blogspot.com/2015/12/golang-connect-to-oracle-database.html
driver readme, https://github.com/rana/ora/blob/master/README.md
Performance, http://docs.oracle.com/database/121/JJDBC/oraperf.htm

Golang connect to Oracle database

Purpose: setup Go Oracle driver, and a complete code to demo some basic DDL, DML, and bulk batching processing.

Minimum requirements are
- Go 1.3 with CGO enabled,
- a GCC C compiler,
- and Oracle 11g (11.2.0.4.0) or Oracle Instant Client (11.2.0.4.0).

This demo is tested on Go 1.5.2, Oracle Virtual Box, imported OTN_Developer_Day_VM.ova appliance/image file.

** environment settings **

------- Linux ----------
export GOROOT=/usr/local/go
#export GOPATH=/home/oracle/go
export GOPATH=/media/sf_GTD/Project/Go
export PATH=$PATH:/usr/local/go/bin:$GOPATH/bin
mkdir -p $GOPATH/src/github.com/user/hello
cd $GOPATH/src/github.com/user/hello
echo $PATH
echo $GOPATH
# Oracle client driver, environment parameters
# Set the CGO_CFLAGS and CGO_LDFLAGS environment variables to locate the OCI headers and library,
export CGO_CFLAGS=-I$ORACLE_HOME/rdbms/public
export CGO_LDFLAGS="-L$ORACLE_HOME/lib -lclntsh"

-----------
-- install Oracle driver --
go get github.com/rana/ora

-- manual install driver from source zip files --
download source zip files, and extract into $GOPATH/src/gopkg.in/rana/ora.v3/ , or rename ora-master to ora.v3

download address: https://github.com/rana/ora/tree/v3

The files structure will be looks like,
src\gopkg.in\rana\ora.v3\
                         ora.go
                         env.go
                         ...
                         \examples\...

To import this package, add the following line to your code:
import "gopkg.in/rana/ora.v3"

Code:

// Copyright 2015, Author: Charlie Database Craftsman. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
/*
Package main demo OCI array interface.
It connects to a database, create table, insert data, and query the data.
E:\GTD\Project\Go\src\github.com\user\hello\oracle_demo.go
*/
package main

import (
 "fmt"
 "gopkg.in/rana/ora.v3"
)

func main() {
 // example usage of the ora package driver
 // connect to a server and open a session
 env, err := ora.OpenEnv(nil)
 defer env.Close()
 if err != nil {
  panic(err)
 }
 srvCfg := ora.NewSrvCfg()
 srvCfg.Dblink = "//localhost/orcl"
 srv, err := env.OpenSrv(srvCfg)
 defer srv.Close()
 if err != nil {
  panic(err)
 }
 sesCfg := ora.NewSesCfg()
 sesCfg.Username = "scott"
 sesCfg.Password = "tiger"
 ses, err := srv.OpenSes(sesCfg)
 fmt.Println("connected")
 defer ses.Close()
 if err != nil {
  panic(err)
 }
 //StmtCfg.PrefetchRowCount = 1000
 stmtTbl, err := ses.Prep(
  `declare
  l_sql varchar2(32767);
  l_cnt pls_integer;
begin
  l_sql := 'drop TABLE emp_go purge';
  select count(*) into l_cnt from user_tables where table_name='EMP_GO';
  if l_cnt > 0 then
    execute immediate l_sql;
  end if;
end;`)
 defer stmtTbl.Close()
 if err != nil {
  panic(err)
 }
 rowsAffected, err := stmtTbl.Exe()
 if err != nil {
  panic(err)
 }
 fmt.Println(rowsAffected, " rows Affected. drop table emp_go if exists.")
 rowsAffected, err = ses.PrepAndExe("CREATE TABLE emp_go (empno number(5,0), ename VARCHAR2(50))")
 if err != nil {
  panic(err)
 }
 fmt.Println("table emp_go created")
 tx1, err := ses.StartTx()
 rowsAffected, err = ses.PrepAndExe("delete emp_go")
 tx1.Commit()
 stmt, err := ses.Prep("INSERT INTO emp_go (empno, ename) VALUES (:N1, :C1)")
 defer stmt.Close()
 rowsAffected, err = stmt.Exe(uint64(1001), "Charlie")
 rowsAffected, err = stmt.Exe(uint64(1002), "Vicky")
 if err != nil {
  panic(err)
 }
 fmt.Println(rowsAffected, "add")
 tx1.Commit()
 tx1.Rollback()
 fmt.Println("commit")
 fmt.Println("Demo fetch records")
 stmtQry, err := ses.Prep("SELECT empno, ename FROM emp_go")
 defer stmtQry.Close()
 if err != nil {
  panic(err)
 }
 rset, err := stmtQry.Qry()
 if err != nil {
  panic(err)
 }
 for rset.Next() {
  fmt.Println(rset.Row[0], rset.Row[1])
 }
 if rset.Err != nil {
  panic(rset.Err)
 }
}

Output:

$> go run oracle_demo.go

connected
0  rows Affected. drop table emp_go if exists.
table emp_go created
1 add
commit
Demo fetch records
1001 Charlie
1002 Vicky
.

Reference:
https://github.com/rana/ora/blob/master/README.md
http://gopkg.in/rana/ora.v3

Golang range and close channel

Code:

package main

import "golang.org/x/tour/tree"
import "fmt"
// Walk walks the tree t sending all values
// from the tree to the channel ch.
func Walk(t *tree.Tree, ch chan int) {
 if t == nil {
  fmt.Println("Null")
  return
 }
 if t.Left != nil {
  Walk(t.Left, ch)
 }
 ch <- t.Value
 fmt.Println("send ", t.Value)
 if t.Right != nil {
  Walk(t.Right, ch)
 }
}


func main() {
 ta := tree.New(1)
 ca := make(chan int)
 go func() {
  Walk(ta, ca)
  close(ca)
 }()

 for v := range ca {
  fmt.Println("get ", v)
 }
}

Comments:

See which line the channel is closed.

Golang Exercise: Equivalent Binary Trees

code:

package main

import "golang.org/x/tour/tree"
import "fmt"

// Walk walks the tree t sending all values
// from the tree to the channel ch.
func Walk(t *tree.Tree, ch chan int) {
 if t == nil {
  close(ch)
  return
 }
 if t.Left != nil {
  Walk(t.Left, ch)
 }
 ch <- t.Value
 //fmt.Println("send ", t.Value)
 if t.Right != nil {
  Walk(t.Right, ch)
 }
}

// Same determines whether the trees
// t1 and t2 contain the same values.
func Same(t1, t2 *tree.Tree) (b_same bool) {
 c1 := make(chan int)
 c2 := make(chan int)
 b_same = true
 go Walk(t1, c1)
 go Walk(t2, c2)
 for i := 0; i < 10; i++ {
  //fmt.Println("get ", <-c1, <-c2)
  if <-c1 != <-c2 {
   b_same = false
   break
  }
 }
 return
}

func main() {
 //t1 := tree.New(1)
 //fmt.Println(t1)
 fmt.Println(Same(tree.New(1), tree.New(1)))
 fmt.Println(Same(tree.New(1), tree.New(2)))
}
 
Output:

true
false

Program exited.

 

Function Same, version 2:

 

func Same(t1, t2 *tree.Tree) (b_same bool) {
 c1 := make(chan int)
 c2 := make(chan int)
 b_same = true
 go func() {
  Walk(t1, c1)
  close(c1)
 }()
 go func() {
  Walk(t2, c2)
  close(c2)
 }()
 for {
  v1, ok1 := <-c1
  v2, ok2 := <-c2
  if !ok1 || !ok2 {
   return ok1 == ok2
  }
  if v1 != v2 {
   b_same = false
   break
  }
 }
 return
}

Ref:
http://tour.golang.org/concurrency/8
https://golang.org/doc/play/tree.go
https://github.com/golang/tour/blob/master/tree/tree.go

Golang Exercise: Images

code:

package main

import "golang.org/x/tour/pic"
import "image"
import "image/color"

type Image struct{}

func (p Image) Bounds() image.Rectangle {
 return image.Rect(0, 0, 256, 256)
}

func (p Image) ColorModel() color.Model {
 return color.RGBAModel
}

func (m Image) At(x, y int) color.Color {
 v := uint8(x * y)
 return color.RGBA{v, v, 255, 255}
}

func main() {
 m := &Image{}
 pic.ShowImage(m)
}

Ref:

http://tour.golang.org/methods/16

Golang Exercise: HTTP Handlers

Code:

package main

import (
 "fmt"
 "log"
 "net/http"
)

type String string

func (h String) ServeHTTP(w http.ResponseWriter, r *http.Request) {
 fmt.Fprint(w, h)
}

type Struct struct {
    Greeting string
    Punct    string
    Who      string
}

func (h *Struct) ServeHTTP(w http.ResponseWriter, r*http.Request){
 fmt.Fprintf(w, "%v %v %v", h.Greeting, h.Punct, h.Who)
}


func main() {
 // your http.Handle calls here
 http.Handle("/string", String("I'm a good man."))
 http.Handle("/struct", &Struct{"Hello", ":", "Gophers! 你好吗？"})
 log.Fatal(http.ListenAndServe("localhost:4000", nil))
}


Call example:

http://localhost:4000/struct

http://localhost:4000/string

Reference:

http://tour.golang.org/methods/14

Golang Exercise: rot13Reader

package main

import (
 "fmt"
 "io"
 "os"
 "strings"
)

type rot13Reader struct {
 r io.Reader
}

func (p rot13Reader) Read(b []byte) (n int, err error) {
 n, err = p.r.Read(b)
 for i := 0; i < n; i++ {
  b[i] = b[i] + 13
  if b[i] > 'z' || (b[i] > 'Z' && b[i] < 'a') {
   b[i] = b[i] - 26
  }
  fmt.Printf("b[%v] = %q\n", i, b[i])
 }
 n = len(b)
 return n, err
}

func main() {
 s := strings.NewReader("Lbh penpxrq gur pbqr!")
 r := rot13Reader{s}
 io.Copy(os.Stdout, &r)
}

Output:

b[0] = 'Y'
b[1] = 'o'
b[2] = 'u'
b[3] = '-'
b[4] = 'c'
b[5] = 'r'
b[6] = 'a'
b[7] = 'c'
b[8] = 'k'
b[9] = 'e'
b[10] = 'd'
b[11] = '-'
b[12] = 't'
b[13] = 'h'
b[14] = 'e'
b[15] = '-'
b[16] = 'c'
b[17] = 'o'
b[18] = 'd'
b[19] = 'e'
b[20] = '.'
You-cracked-the-code.You-cracked-the-code.
Program exited.

Reference:

http://tour.golang.org/methods/12

Golang Exercise: Errors

package main

import (
 "fmt"
 "math"
)

type ErrNegativeSqrt float64

func (e ErrNegativeSqrt) Error() string {
 if e < 0 {
  return fmt.Sprintf("cannot Sqrt negative number: %v ", float64(e))
 }
 return ""
}

func Sqrt(x float64) (float64, error) {
 if x < 0 {
  return x, ErrNegativeSqrt(x)
 }
 return math.Sqrt(x), ErrNegativeSqrt(x)
}


func main() {
 fmt.Println(Sqrt(2))
 fmt.Println(Sqrt(-2))
}
Output:

1.4142135623730951 
-2 cannot Sqrt negative number: -2 

Program exited.

Reference:

http://tour.golang.org/methods/9

Golang Exercise: Stringers

package main

import "fmt"

type IPAddr [4]byte

// TODO: Add a "String() string" method to IPAddr.
func (ip IPAddr) String() string {
 var ls_ip string
 for i, value := range ip {
  //ls_ip = ls_ip + string(i) + ":" + string(int(value)) + "."
  switch {
  case i == 0:
   ls_ip = fmt.Sprintf("%v", value)
  default:
   ls_ip = fmt.Sprintf("%v.%v", ls_ip, value)
  }
 }
 return ls_ip
}

func main() {
 addrs := map[string]IPAddr{
  "loopback":  {127, 0, 0, 1},
  "googleDNS": {8, 8, 8, 8},
 }
 for n, a := range addrs {
  fmt.Printf("%v: %v\n", n, a)
 }
}


Output:

loopback: 127.0.0.1
googleDNS: 8.8.8.8

Program exited.

Reference:

http://tour.golang.org/methods/7

Golang Exercise: Slices

Exercise: Slices

Implement Pic. It should return a slice of length dy, each element of which is a slice of dx 8-bit unsigned integers. When you run the program, it will display your picture, interpreting the integers as grayscale (well, bluescale) values.
The choice of image is up to you. Interesting functions include (x+y)/2, x*y, and x^y.
(You need to use a loop to allocate each []uint8 inside the [][]uint8.)
(Use uint8(intValue) to convert between types.)

.
Solution:

package main

import "golang.org/x/tour/pic"
//import "fmt"

func Pic(dx, dy int) [][]uint8 {
 var la_pic [][]uint8
 var la_x []uint8
 la_x = make([]uint8, dx)
 //la_pic = make([][]uint8, 0)
 for i := 0; i < dy; i++ {
  la_pic = append(la_pic, la_x)
  for j := 0; j < dx; j++ {
   la_pic[i][j] = uint8((i ^ j))
   //fmt.Println(i, j)
  }
 }
 return la_pic
}
func main() {
 pic.Show(Pic)
}

Reference:

http://tour.golang.org/moretypes/15

Go语言 斐波那契数列 Exercise: Fibonacci closure

My code:

package main

import "fmt"

// fibonacci is a function that returns
// a function that returns an int.
func fibonacci() func() int {
 var ln_prev, ln_current, ln_next int
 ln_current = 1
 return func() int {
  ln_next = ln_current + ln_prev
  ln_prev = ln_current
  ln_current = ln_next
  fmt.Println("log:", ln_prev, ln_current, ln_next)
  return ln_next
 }
}
func main() {
 f := fibonacci()
 for i := 0; i < 10; i++ {
  fmt.Println(i, f())
 }
}

Output:

log: 1 1 1
0 1
log: 1 2 2
1 2
log: 2 3 3
2 3
log: 3 5 5
3 5
log: 5 8 8
4 8
log: 8 13 13
5 13
log: 13 21 21
6 21
log: 21 34 34
7 34
log: 34 55 55
8 55
log: 55 89 89
9 89

Program exited.

Option 2, Learned from Python way :

package main

import "fmt"

// fibonacci is a function that returns
// a function that returns an int.
func fibonacci() func() int {
 var la_f []int
 la_f = make([]int, 2)
 la_f = []int{0, 1}
 //la_f := []int{0, 1}
 var li_idx int
 li_idx = 1
 return func() int {
  la_f = append(la_f, la_f[li_idx]+la_f[li_idx-1])
  li_idx = li_idx + 1
  fmt.Printf("log: i %d, value %d, pre element %d \n", li_idx, la_f[li_idx], la_f[li_idx-1])
  return la_f[li_idx]
 }
}
func main() {
 f := fibonacci()
 for i := 0; i < 10; i++ {
  fmt.Printf("idx: %d, value: %d \n", i, f())
 }
}

Output:

log: i 2, value 1, pre element 1
idx: 0, value: 1
log: i 3, value 2, pre element 1
idx: 1, value: 2
log: i 4, value 3, pre element 2
idx: 2, value: 3
log: i 5, value 5, pre element 3
idx: 3, value: 5
log: i 6, value 8, pre element 5
idx: 4, value: 8
log: i 7, value 13, pre element 8
idx: 5, value: 13
log: i 8, value 21, pre element 13
idx: 6, value: 21
log: i 9, value 34, pre element 21
idx: 7, value: 34
log: i 10, value 55, pre element 34
idx: 8, value: 55
log: i 11, value 89, pre element 55
idx: 9, value: 89

Program exited.

 

Reference:

 

http://tour.golang.org/moretypes/23

 

https://zh.wikipedia.org/wiki/%E6%96%90%E6%B3%A2%E9%82%A3%E5%A5%91%E6%95%B0%E5%88%97

 

Oracle native out join syntax learned

One more out join syntax (+) observed.

Setup

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (id number);
create table t2 (id number);
create table t3 (id number);
insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id) select rownum from dual connect by level <= 2;
insert into t3(id) select rownum from dual connect by level <= 1;
commit;

select t1.id t1_id, t2.id t2_id
from t2,t1
where t1.id = t2.id(+)
and (t2.id= 1 or t2.id is null)
--and t2.id(+) = 1
order by t1.id;

  T1_ID T2_ID
------- ----------
  1     1
  3

Here is a new syntax I learned last week.
The output is strange.

select t1.id t1_id, t2.id t2_id
from t2,t1
where t1.id = t2.id(+)
and t2.id(+) = 1
order by t1.id;

  T1_ID T2_ID
------- ----------
  1     1
  2
  3

Exercise: Maps 第一段 Go 语言测试通过了！

打算转换职业方向，做Golang程序设计师。
第一段代码测试通过了，好激动！

My code:

package main
import (
 "golang.org/x/tour/wc"
 "strings"
)
func WordCount(s string) map[string]int {
 var la_s = strings.Split(s, " ")
 //lm_s := map[string]int{}  // simpler
 var lm_s map[string]int
 lm_s = make(map[string]int)
 for _, word := range la_s {
  lm_s[word] = lm_s[word] + 1
 }
 return lm_s
 //return map[string]int{"x": 1}
}
func main() {
 wc.Test(WordCount)
}

Output:

PASS
 f("I am learning Go!") = 
  map[string]int{"I":1, "am":1, "learning":1, "Go!":1}
PASS
 f("The quick brown fox jumped over the lazy dog.") = 
  map[string]int{"over":1, "the":1, "The":1, "quick":1, "brown":1, "jumped":1, "fox":1, "lazy":1, "dog.":1}
PASS
 f("I ate a donut. Then I ate another donut.") = 
  map[string]int{"ate":2, "a":1, "donut.":2, "Then":1, "another":1, "I":2}
PASS
 f("A man a plan a canal panama.") = 
  map[string]int{"panama.":1, "A":1, "man":1, "a":2, "plan":1, "canal":1}

Program exited.

Reference:

http://tour.golang.org/moretypes/20

2 level left outer join

Here is Oracle outer join behavior observation.
We get same results between Oracle notation (+), and ANSI join syntax (LEFT OUTER JOIN)

Oracle 12.1.0.2.0

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1 (id number);
create table t2 (id number);
create table t3 (id number);

insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id) select rownum from dual connect by level <= 2;
insert into t3(id) select rownum from dual connect by level <= 1;
commit;

select t1.id t1_id, t2.id t2_id, t3.id t3_id
from t3,t2,t1
where t1.id = t2.id(+)
and t2.id = t3.id(+)
order by t1.id;

  T1_ID   T2_ID    T3_ID
------- ------- --------
      1       1        1
      2       2
      3

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1 (id number);
create table t2 (id number, t1_id number);
create table t3 (id number, t2_id number);

insert into t1(id) select rownum from dual connect by level <= 3;
insert into t2(id, t1_id) select rownum, rownum from dual connect by level <= 2;
insert into t3(id, t2_id) select rownum, rownum from dual connect by level <= 1;
commit;

select t1.id, t2.id t2_id, t2.t1_id, t3.id t3_id, t3.t2_id
from t3,t2,t1
where t1.id = t2.t1_id(+)
and t2.id = t3.t2_id(+)
order by t1.id;

  ID  T2_ID  T1_ID  T3_ID  T2_ID
---- ------ ------ ------ ------
   1      1      1      1      1
   2      2      2
   3

Your homework is to write ANSI join syntax (LEFT OUTER JOIN) SQL, to get same result.   ^_^

reference partition

/****
Purpose:

Demo Oracle reference partition, 3 levels parent child relationships, partition key is not primary key or foreign key.

@C:\z00\dev\project\migration\reference\ref_partition.sql

Modification History:
Date         Who          What
12-Mar-2015: Charlie(Yi): Create the file,

****/

drop table order_item_color cascade constraint purge;
drop table order_line_items cascade constraint purge;
drop table orders cascade constraint purge;

create table orders
 (
 order_no number primary key,
 order_date date,
 data varchar2(30)
 )
enable row movement
partition by range (order_date)
(
 partition part_2014 values less than (to_date('01-01-2015','dd-mm-yyyy')),
 partition part_2015 values less than (to_date('01-01-2016','dd-mm-yyyy'))
);

insert into orders values ( 1, to_date( '01-jun-2014', 'dd-mon-yyyy' ), 'xxx' );
insert into orders values ( 2, to_date( '01-jun-2015', 'dd-mon-yyyy' ), 'xxx' );

drop table order_line_items cascade constraint purge;
create table order_line_items
(
order_no number 
 constraint order_line_items_nn1 not null,
line_no number,
data varchar2(30),  
constraint c1_pk primary key(line_no),
constraint c1_fk_p foreign key(order_no) references orders
)
enable row movement
 partition by reference(c1_fk_p)
;

insert into order_line_items values ( 1, 1, 'zzz' );
insert into order_line_items values ( 1, 2, 'zzz' );

insert into order_line_items values ( 2, 3, 'zzz' );
insert into order_line_items values ( 2, 4, 'zzz' );

select * from order_line_items partition(PART_2014);
/*
  ORDER_NO    LINE_NO DATA
---------- ---------- -----------------
         1          1 zzz
         1          2 zzz
*/

column table_name format a18
column partition_name format a15

select table_name, partition_name
from user_tab_partitions
where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS', 'ORDER_ITEM_COLOR' )
order by table_name, partition_name;

drop table order_item_color cascade constraint purge;
create table order_item_color
(
line_no number
 constraint order_line_size_nn1 not null,
color_no number,
data varchar2(30),
constraint order_item_color_pk primary key(color_no),
constraint order_item_color_fk foreign key(line_no) references order_line_items
)
enable row movement
 partition by reference(order_item_color_fk)
;

insert into order_item_color values ( 1, 1, 'y' );
insert into order_item_color values ( 2, 2, 'y' );
insert into order_item_color values ( 3, 3, 'yy' );
insert into order_item_color values ( 4, 4, 'yy' );
commit;

--alter table orders drop partition part_2014 update global indexes;

alter table orders add partition
part_2016 values less than
(to_date( '01-01-2017', 'dd-mm-yyyy' ));

select table_name, partition_name
from user_tab_partitions
where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS', 'ORDER_ITEM_COLOR' )
order by table_name, partition_name;

/*
TABLE_NAME         PARTITION_NAME
------------------ ---------------
ORDERS             PART_2014
ORDERS             PART_2015
ORDERS             PART_2016
ORDER_ITEM_COLOR   PART_2014
ORDER_ITEM_COLOR   PART_2015
ORDER_ITEM_COLOR   SYS_P877
ORDER_LINE_ITEMS   PART_2014
ORDER_LINE_ITEMS   PART_2015
ORDER_LINE_ITEMS   PART_2016
*/

select * from order_item_color partition(PART_2014);
/*
   LINE_NO   COLOR_NO DATA
---------- ---------- ------
         1          1 y
         2          2 y
*/

select * from order_item_color partition(PART_2015);
/*
   LINE_NO   COLOR_NO DATA
---------- ---------- ------
         3          3 yy
         4          4 yy
*/

Reading notes - Developing Successful Oracle Applications

Here we go,

• Knowing what’s out there in Oracle. 
• Solving problem simply.  (Don’t reinvent the wheel)
• Layered programming.   (Database API)

 

“A layer of abstraction and indirection is another example of defensive programming to allow for portability and code scalability. it is to layer our access to the database when necessary.”

 

“No matter what database we are using, we paid a lot for these technologies,

isn't it in our best interest to exploit it to the fullest extent possible, squeezing every last bit of functionality we can out of that product ? ”