備忘録

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

仮想列にINSERT時に値設定

おら! オラ! Oracle どっぷり検証生活のOracle 11g検証 隠れた新機能検証 その4から SQL を借用し仮想列を持つテーブルを作って insert してみる。

SQL> insert into VTP_ADDRESS values('1','1',null);
insert into VTP_ADDRESS values('1','1',null)
*
行1でエラーが発生しました。:
ORA-54013: 仮想列ではINSERT操作は許可されていません



だめですね。無視してくれるとうれしかった。
PL/SQL の ROWTYPE で定義されたレコードを bulk insert したかったけどできない。
実体を作ってINSERTトリガーで値を入れた方が使い勝手がいい。

SQL> ed
1 declare
2 TYPE TYPE_ADDRESS IS RECORD (
3 KEN_NAME VTP_ADDRESS.KEN_NAME%TYPE
4 ,KEN_CODE VTP_ADDRESS.KEN_CODE%TYPE
5 );
6 TYPE tArray IS TABLE OF TYPE_ADDRESS INDEX BY PLS_INTEGER;
7 list tArray;
8 begin
9 FOR i IN 1..10 LOOP
10 list(i).KEN_NAME := '北海道';
11 list(i).KEN_CODE := '01';
12 END LOOP;
13 forall i in 1 .. list.count
14 insert into VTP_ADDRESS values list(i);
15* end;
SQL> /
insert into VTP_ADDRESS values list(i);
*
行14でエラーが発生しました。:
ORA-06550: 行14、列14:
PL/SQL: ORA-00947: 値の個数が不足しています。
ORA-06550: 行14、列2:
PL/SQL: SQL Statement ignored


1 declare
2 TYPE TYPE_ADDRESS IS RECORD (
3 KEN_NAME VTP_ADDRESS.KEN_NAME%TYPE
4 ,KEN_CODE VTP_ADDRESS.KEN_CODE%TYPE
5 ,AREA_CODE VTP_ADDRESS.AREA_CODE%TYPE
6 );
7 TYPE tArray IS TABLE OF TYPE_ADDRESS INDEX BY PLS_INTEGER;
8 list tArray;
9 begin
10 FOR i IN 1..10 LOOP
11 list(i).KEN_NAME := '北海道';
12 list(i).KEN_CODE := '01';
13 END LOOP;
14 forall i in 1 .. list.count
15 insert into VTP_ADDRESS values list(i);
16* end;
17 /
declare
*
行1でエラーが発生しました。:
ORA-54013: 仮想列ではINSERT操作は許可されていません
ORA-06512: 行14



20万件入れるときの性能は仮想列で実装した時には 28.28秒、トリガーの場合は 30.75秒 で誤差の範囲?なのでトリガーがいいか。参考までに、INSERTトリガーをはずしたら 20.76秒だった。
INSERTする部分を修正できるのなら、トリガーも無しでやった方が性能的にはより良い。

Virtual Box

久しぶりのブログです。

今回は、VirtualBoxのインストールです。
Oracle TimesTen In-Memory Database Cache を試そうとおもったが、環境を作るのが面倒でVMイメージころがっていないか探したらDatabase App Development VMというイメージがOracleのサイトにあったのでそれを使うことにした。

VirtualBoxは VMWareほどメジャーじゃないが、インストールの仕方とかはネットに出ているので省略。
今回特に何も調べずいきなり全部デフォルトでインストール。

インストールして、イメージをインポートして起動ってDownloads and Instructionsのリンク先に書いてあったのでその通りに。
VirtualBoxマネージャの画面が日本語化されていたのにはビックリした。ちなみにバージョンは4.2.4

「起動→」ボタンで起動すると”memory for crash kernel not within permissible range”というメッセージが出てだんまり。
VirtualBox Startup Error


Google先生で日本語のサイトを検索したら、kdumpがどうのこうのというのしかなく関係ないものしか見つからず。
英語のサイトを見るとOracleのwikiに情報があった。
「After importing the VM, go to the Settings, System, Motherboard tab and set Enable I/O APIC and Hardware Clock in UTC Time」
とのこと。
VirtualBox System Config

設定して起動すると見事に立ち上がった。
理由はちゃんと調べていないが、Windows7, 64bit, AMD, UNIX/Linuxなどが関係しそう。

ちなみにユーザ情報とは以下。
OS User Name/Password: oracle/oracle
Oracle Service Name: orcl
sys password: oracle

複数行のRETURNING

RETURNING でINSERT/UPDATEした時の1行分のデータを取得できるのは知っていたが
複数行もBULK COLLECT INTOで取得できるそうだ。
SQLリファレンスのreturning_clause の説明を見ると集計関数をかますことも出来るみたい。
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 100
RETURNING SUM(salary) INTO :bnd1;


PL/SQL言語リファレンスのRETURNING INTO句 をみると、DELETEでもRETURNINGが使える。

更新した結果をもって、カーソルを開けると言う事なしなのに。
BULK COLLECTだと件数が多いとPGAがあふれてしまう。

海外サイトだがいいサンプルがあったのでリンク

マニュアルを見ていたらこんなのも見つけた。RECORDでそのまま更新できるらしい。
ROWってカラムがあったらどんな動きをするんでしょうね。RECORDは全部カラムがないといけないかも興味あり。
今度試してみようか。。
DECLARE
default_week schedule%ROWTYPE;
BEGIN
default_week.Mon := 'Day Off';
default_week.Tue := '0900-1800';
default_week.Wed := '0900-1800';
default_week.Thu := '0900-1800';
default_week.Fri := '0900-1800';
default_week.Sat := '0900-1800';
default_week.Sun := 'Day Off';

FOR i IN 1..3 LOOP
default_week.week := i;

UPDATE schedule
SET ROW = default_week
WHERE week = i;
END LOOP;
/

元表TRUNCATE時にマテビューログもTRUNCATE

truncate table テーブル名 purge MATERIALIZED VIEW log;

マテビューログ MLOG$テーブル名 がTRUNCATEされると思ったが、SQL Traceを見ると見事 DELETE をしている。
昔の書き方の SNAPSHOT にしたらひょっとしたらTRUNCATEされると思ったがだめだった。

truncate table テーブル名 purge SNAPSHOT log;

DBMS_MVIEW にもそれらしいプロシージャ無いので、元表 TRUNCATE 後に MLOG$テーブル名 を直接 TRUNCATE するしかなさそう。

長いテーブル名のマテビューログはつくれるのか?

マテリアライズドビュー・ログのテーブル名はOracleが勝手につけている。
MLOG$_<元のテーブル名> という名前なので、30文字中6文字を使っている。25文字以上のテーブルにはマテビューログは作れないの?なんて思ったので試してみた。
結果は、実質問題なし。
長い場合は、元のテーブル名を途中でちょん切って連番でつけている。
MLOG$_<元のテーブル名の前方20文字> + 連番(最大9999と思う)
なので、普通そんな前方一致で合致するテーブルをそんなに作らんので問題ない。

最後まで使い切ったらどんなエラーがでるか見ようと思ったが、
9600テーブル超えたあたりマテビュー作成が激遅になったので止めた。

SQL> drop table TBL456789012345678901234567890;

表が削除されました。

SQL> create table TBL456789012345678901234567890 (
2 n1 number(10)
3 );

表が作成されました。

SQL> ALTER TABLE TBL456789012345678901234567890 ADD CONSTRAINT PK_XXXX PRIMARY KEY(n1);

表が変更されました。

SQL> CREATE MATERIALIZED VIEW LOG ON TBL456789012345678901234567890 WITH ROWID, PRIMARY KEY;

マテリアライズド・ビュー・ログが作成されました。

SQL>
SQL> drop table TBL45678901234567890;

表が削除されました。

SQL> create table TBL45678901234567890 (
2 n1 number(10)
3 );

表が作成されました。

SQL> ALTER TABLE TBL45678901234567890 ADD CONSTRAINT PK_TBL45678901234567890 PRIMARY KEY(n1);

表が変更されました。

SQL> CREATE MATERIALIZED VIEW LOG ON TBL45678901234567890 WITH ROWID, PRIMARY KEY;

マテリアライズド・ビュー・ログが作成されました。

SQL>
SQL> drop table TBL45678901234567890123;

表が削除されました。

SQL> create table TBL45678901234567890123 (
2 n1 number(10)
3 );

表が作成されました。

SQL> ALTER TABLE TBL45678901234567890123 ADD CONSTRAINT PK_bbbb PRIMARY KEY(n1);

表が変更されました。

SQL> CREATE MATERIALIZED VIEW LOG ON TBL45678901234567890123 WITH ROWID, PRIMARY KEY;

マテリアライズド・ビュー・ログが作成されました。

SQL>
SQL> select master, log_table from USER_MVIEW_LOGS where master like 'TBL456%';

MASTER LOG_TABLE
------------------------------------------------------------ ------------------------------------------------------------
TBL45678901234567890 MLOG$_TBL456789012345678901
TBL45678901234567890123 MLOG$_TBL456789012345678902
TBL456789012345678901234567890 MLOG$_TBL45678901234567890
前のページ 次のページ

FC2Ad

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。