データベース・アーキテクト・サミット - askTom Live に行って来た

http://www.oracle.co.jp/campaign/11g/asktom/ に行って来た。
11gR2 を使いたくなって、後日インストールした。
Oracle Database 11gR2 を CentOS 5.4 にインストールしてみた - ablog

What are we still doing wrong?

how do I archive this goal
my car won't start
Always Question Everything
Question Authority

同感なことばかりだった。

Top 11 things about Oracle Database 11g Release 2

DBMS_PARALLEL_EXECUTE パッケージ

このパッケージを使うと簡単に並列処理を実現できる。
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_parallel_ex.htm#CHDEABDB を参考に使ってみる。

SQL> create user scott identified by tiger
default tablespace users
temporary tablespace temp
quota unlimited on users;
SQL> grant connect, resource to scott;
SQL> conn scott/tiger
SQL> create table t1 (id number(10));
SQL> create sequence seq1
	start with 1
	increment by 1
	maxvalue 1000000
	minvalue 1
	cycle;
SQL> begin
	for i in 1..100000 loop
		insert into t1 (id) values(seq1.nextval);
	end loop;
end;
/
SQL> commit;
SQL> select count(1) from t1;

  COUNT(1)
----------
    100000

SQL> conn / as sysdba
SQL> DECLARE
sql_stmt VARCHAR2(100);
BEGIN
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'SCOTT', 'T1', true, 100);
sql_stmt := 'update t1 set id = id + 1';
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', sql_stmt, DBMS_SQL.NATIVE,parallel_level => 10);
DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');   
END;
/

おお、サーバープロセスがいっぱい起動して並列処理してる！
CPU いっぱい積んでるマシンだと速くなりそー

ListAgg

11gR2 では ListAgg 関数が追加された。ListAgg はグループ化された行に存在する一連の値を1つのリストにして出力する機能を持った関数。集約関数や分析関数によって集約された値の内訳を簡単に出力することができる。

SQL> set pagesize 10000
SQL> set linesize 300
SQL> col account_status for a20
SQL> col username for a80
--普通に group by すると
SQL> select  account_status, username from dba_users group by account_status, username order by account_status, username;

ACCOUNT_STATUS	     USERNAME
-------------------- --------------------------------------------------------------------------------
EXPIRED & LOCKED     ANONYMOUS
EXPIRED & LOCKED     APEX_030200
EXPIRED & LOCKED     APEX_PUBLIC_USER
EXPIRED & LOCKED     APPQOSSYS
EXPIRED & LOCKED     CTXSYS
EXPIRED & LOCKED     DBSNMP
EXPIRED & LOCKED     DIP
EXPIRED & LOCKED     EXFSYS
EXPIRED & LOCKED     FLOWS_FILES
EXPIRED & LOCKED     MDDATA
EXPIRED & LOCKED     MDSYS
EXPIRED & LOCKED     MGMT_VIEW
EXPIRED & LOCKED     OLAPSYS
EXPIRED & LOCKED     ORACLE_OCM
EXPIRED & LOCKED     ORDDATA
EXPIRED & LOCKED     ORDPLUGINS
EXPIRED & LOCKED     ORDSYS
EXPIRED & LOCKED     OUTLN
EXPIRED & LOCKED     OWBSYS
EXPIRED & LOCKED     OWBSYS_AUDIT
EXPIRED & LOCKED     SI_INFORMTN_SCHEMA
EXPIRED & LOCKED     SPATIAL_CSW_ADMIN_USR
EXPIRED & LOCKED     SPATIAL_WFS_ADMIN_USR
EXPIRED & LOCKED     SYSMAN
EXPIRED & LOCKED     WMSYS
EXPIRED & LOCKED     XDB
EXPIRED & LOCKED     XS$NULL
OPEN		     SCOTT
OPEN		     SYS
OPEN		     SYSTEM

30 rows selected.

--listagg を使って、縦横変換してみる
SQL> select account_status, listagg(username, ', ') within group (order by username) "username" from dba_users group by account_status;

ACCOUNT_STATUS	     username
-------------------- --------------------------------------------------------------------------------
EXPIRED & LOCKED     ANONYMOUS, APEX_030200, APEX_PUBLIC_USER, APPQOSSYS, CTXSYS, DBSNMP, DIP, EXFSYS
		     , FLOWS_FILES, MDDATA, MDSYS, MGMT_VIEW, OLAPSYS, ORACLE_OCM, ORDDATA, ORDPLUGIN
		     S, ORDSYS, OUTLN, OWBSYS, OWBSYS_AUDIT, SI_INFORMTN_SCHEMA, SPATIAL_CSW_ADMIN_US
		     R, SPATIAL_WFS_ADMIN_USR, SYSMAN, WMSYS, XDB, XS$NULL

OPEN		     SCOTT, SYS, SYSTEM

GRANT EXECUTE ON DIRECTORY

ディレクトリオブジェクトに対する Read/Write に加えて、11gR2 では Execute も可能になった。これによって任意のプログラムを実行し、その結果をテーブルに格納するなどの処理を実現できる。例えば、gzipファイルを、gunzipコマンドで解凍して直接テーブルに書き込むことも可能。

$ cd /home/oracle
$ mkdir foo
$ cd foo
$ vi bunzip2.sh
#!/bin/bash

/usr/bin/bunzip2 -c $*
$ chmod +x bunzip2.sh
$ perl -le 'print "c1$_, c2$_" for 1..10'|bzip2 -c > bar.bz2
$ sqlplus / as sysdba
SQL> CREATE or replace DIRECTORY dir  AS '/home/oracle/foo'
/
SQL> CREATE TABLE et
  ( 
    "col1" VARCHAR2(5), 
    "col2" VARCHAR2(5)
 ) 
  ORGANIZATION external 
  ( TYPE oracle_loader 
    DEFAULT DIRECTORY dir 
    ACCESS PARAMETERS 
    ( RECORDS DELIMITED BY NEWLINE 
          preprocessor  dir:'bunzip2.sh' 
      FIELDS TERMINATED BY "," LDRTRIM 
    ) 
    location ( 'bar.bz2') 
  ) 
  / 
 
SQL> select * from et;

col1  col2
----- -----
c11    c21
c12    c22
c13    c23
c14    c24
c15    c25
c16    c26
c17    c27
c18    c28
c19    c29
c110   c210

10 rows selected.

11gR2 の新機能じゃないけど、外部表は使えるなー。例えば、csv ファイルを外部表にして、DB 内のテーブルと join するとか。

Recursive Subquery Factoring

Oracleでは従来より connect by 句による階層問い合わせをサポートしていたが、11gR2 からはそれに加えて再帰with句を使うことができるようになった。connect by とできることは同じだけど、直感的に理解しやすい SQL を書けるのがメリット。

SQL> with data(r) 
as 
(select 1 r from dual 
   union all 
   select r+1 from data where r < 5 
  ) 
select r, sysdate+r 
from data; 

	 R SYSDATE+R
---------- ------------
	 1 21-FEB-10
	 2 22-FEB-10
	 3 23-FEB-10
	 4 24-FEB-10
	 5 25-FEB-10

では、以前 connect by を使って書いた SQL を書き換えてみる。

select * from (select level id from dual connect by level <= 10);
連続する数値を作成するSQL - ablog

実行してみると、

SQL> with data(r)
as
(select 1 from dual
union all
select r+1 from data where r<10)
select r from data;

	 R
----------
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10

10 rows selected.

おー、いけた。

Improved Time Travel

Flashback Data Archive を使って、DDL の変更をまたがって、フラッシュバッククエリーが使えるようになった。例えば、drop table した後もフラッシュバッククエリーで表を参照できる。フラッシュバックデータベースでデータベース全体を巻き戻さなくても DDL 変更前のデータを参照できるのは便利だな。

File Watchers

DEFERRED_SEGMENT_CREATION

Flash Cache

Edition-based Redefinition

askTom Live

Q. ORA-600は素晴らしい機能だけれど、同じようにネガティブイメージだけど誇るべき機能ってあるの？
A. ADRとインシデントパッケージ

Q. これからは NoSQL、RDBMS は終わった？
A. 適材適所。 AskTom は APEX という KVS を使っている。セッション情報を保持するときはこちらが向いている。

Q. BLOB と RAW、CLOB、VARCHAR2 と CLOB の使い分けは？
A. サイズで決める。