AnySQL.net

    Oracle的外部表是一个很不错的功能, 可以轻松地将一个格式化的文本文件虚拟成数据库的表, 并可以用SELECT语句去访问, 正在被越来越多的人使用. 早上有人问我如何在外部表中定义一个日期类型的列, 我问他知不知道SQLLDR如何装载日期字段, 他说是知道的, 可他就是没有将这个日期在外部表上搞出来. 还是我做一个演示吧!

    先建一个目录对象:

SQL> conn / as sysdba
Connected.
SQL> create directory anysql as 'c:\anysql';

Directory created.

SQL> grant read on directory anysql to anysql;

Grant succeeded.

    接下来准备一个文本文件(Today.txt), 内容如下:

2007-03-01 12:01:02

    现在连接到anysql用户去创建外部表吧, 很简单的, 如下所示:

SQL> conn anysql/anysql
Connected.
SQL> CREATE TABLE ext_date
  2  (
  3     TODAY DATE
  4  )
  5  ORGANIZATION EXTERNAL (
  6    TYPE oracle_loader
  7    DEFAULT DIRECTORY ANYSQL
  8      ACCESS PARAMETERS (
  9      FIELDS TERMINATED BY ','
10      MISSING FIELD VALUES ARE NULL
11      (
12         TODAY DATE "YYYY-MM-DD HH24:MI:SS"
13      ))
14      LOCATION ('today.txt'));

Table created.

SQL> desc ext_date
Name                Null?    Type
------------------- -------- --------
TODAY                        DATE

    要最后查询一下才放心:

SQL> SELECT * FROM EXT_DATE;

TODAY
------------
01-MAR-07

    如果你的文件格式和这个例子不一样, 请自行修改建表的语法, 应当没有问题了吧?

    最近在Solaris上安装Oracle 9.2.0.7, 却发现运行"relink all"时报了如下错误, 这已经第二次发现有关libsrvm.so的链接错误了. 只要不是运行"relink all"则都不会出错, 如"relink oracle"或"relink client_sharelib"等. 具体错误信息如下:

ld: fatal: file ${ORACLE_HOME}/lib/libskgxn9.so: wrong ELF class: ELFCLASS64
ld: fatal: File processing errors. No output written to libsrvm.so
*** Error code 1
make: Fatal error: Command failed for target `libsrvm.so'

    用"file"命令去看"libskgxn9.so"文件的属性, 发现他是一个编译成64位的库, 而libsrvm.so应当是一个32位的动态库, 于是到$ORACLE_HOME/srvm/lib目录下去修改了一下env_rdbms.mk文件, 如下所示:

# EXSYSLIBS = -lposix4 -lm -lskgxn9 #original line
EXSYSLIBS = -lposix4 -lm

    然后运行"make -f ins_rdbms.mk isrvm"去单独编译这个模块, 没有报错. 再重新运行"relink all"也没有报错了.

    定义LOB字段时可以加上ENABLE/DISABLE STORAGE IN ROW的存贮属性, 默认情况下是ENABLE STORAGE IN ROW的, 这种情况除了在值小于4000字节时会将值直接存在行内以提高性能外, 对于再大一点的LOB值的存取也是有性能影响的, 请看下面的在10g版本中做的测试例子, 先建一个表, 包括两个CLOB字段, 并插入一样一样的大于4000字节的值.

SQL> create table t_lobtest (col1 clob, col2 clob)
  2  lob (col2) store as (disable storage in row);

Table created.

ASQL> SELECT DBMS_LOB.GETLENGTH(COL1) COL1_LEN,
    2        DBMS_LOB.GETLENGTH(COL2) COL2_LEN
    3 FROM T_LOBTEST;

COL1_LEN COL2_LEN
-------- --------
    7989     7989

1 rows returned.

    这两个字段的内容都是一致的, 并有长度大于4000个字节, 因此真正的值都是存放在行外的, 但是我们去看一下查询这两个不同字段的SQL的跟踪信息的话, 会发现所需要的逻辑读是不一样的.

SQL> select col1 from t_lobtest;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          4  physical reads
          0  redo size
       1213  bytes sent via SQL*Net to client
        829  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col2 from t_lobtest;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          4  physical reads
          0  redo size
       1141  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

    可以发现Physical Read都是一样的, 这是由于Direct Path Read (LOB)引起的, 但在逻辑读上, DISABLE STORAGE IN ROW的LOB例就要高出很多了, 根据我前段时间对于LOB类型值的存贮格式研究, 在ENABLE STORAGE IN ROW时, LOB的前12个CHUNK的地址会放在行内, 这十二个CHUNK的地址不会存放在LOB索引中, 相当于在行内存放了LOB_ID, CHUNK1, CHUNK2, ..., CHUNK12, 而在DISABLE STORAGE IN ROW的LOB列上, 行内只存放了一个LOB_ID, 而每一个CHUNK的地址都存放在LOB索引中. 在这个例子中, CHUNK的数量为两个, 查询COL1时不需要去访问LOB索引, 而查询COL2时需要去查询LOB索引.

    当去频繁地查询LOB字段内容时, 这些COST还是值得考虑的, 定义适当的CHUNK大小, 将LOB CHUNK控制在12个以内, 尽最大程度地减少LOB索引的访问, 有助于提高性能.

    从Oracle 8i开始提供了实体化视图, 能过预先计算好的中间表来提高应用的访问速度, 在特定的情况下是很有用的一项技术. 另外实体化视图还可用于数据复制, 在这个上面的应用越来越多. MVIEW中经常跗以遇到刷新很慢的情况, 如何提高呢? 首先来研究一下刷新的过程. 下面是用来创建演示表的角本:

CREATE TABLE T_MVLOG (COL1 VARCHAR2(20));
CREATE MATERIALIZED VIEW LOG ON T_MVLOG
    WITH ROWID, sequence;
CREATE MATERIALIZED VIEW MV_T_MVLOG
   REFRESH FAST
   WITH ROWID
AS
   SELECT ROWID R_ID, A.* FROM T_MVLOG A;

    我们对DBMS_MVIEW.REFRESH作一个SQL_TRACE, 在这个例子中, 我在基表中插入了一打记录, 然后作跟踪的. 可以看到第一步为:

update "ANYSQL"."MLOG$_T_MVLOG" set snaptime$$ = :1  
   where snaptime$$ >
      to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

    第二步, 取得在这段时间内发生修改的每一行的ROWID

SELECT DISTINCT M_ROW$$ FROM
(
   SELECT M_ROW$$
        FROM "ANYSQL"."MLOG$_T_MVLOG" MLOG$
        WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'I')
) LOG$
WHERE (M_ROW$$) NOT IN
     (
       SELECT ROWID FROM "T_MVLOG" "MAS_TAB$"
           WHERE MAS_TAB$.ROWID = LOG$.M_ROW$$
     )

    第三步, 取得刷新后的值

SELECT CURRENT$."R_ID",
       CURRENT$."COL1",
       ROWIDTOCHAR(CURRENT$.ROWID) M_ROW$$
FROM
(
  SELECT "A".ROWID "R_ID","A"."COL1" "COL1" FROM "T_MVLOG" "A"
) CURRENT$,
(
  SELECT DISTINCT M_ROW$$ FROM "ANYSQL"."MLOG$_T_MVLOG" MLOG$
      WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')
) LOG$
WHERE CURRENT$.ROWID = LOG$.M_ROW$$

    第四步, 对MVIEW进行插入

INSERT INTO "ANYSQL"."MV_T_MVLOG"  ("R_ID","COL1","M_ROW$$")
   VALUES (:1,:2,:3)

    最后一步, 删除MVLOG中的值

delete from "ANYSQL"."MLOG$_T_MVLOG" where snaptime$$ <= :1

    从这外过程来看, 可以调的方法有四个, 首先尽量使用快速刷新, 提高刷新频率, 其次可以在MLOG$_T_MVLOG这个表的snaptime$$字段上建索引, 第三为刷新的过程设定会话级的DB_FILE_MULTIBLOCK_READ_COUNT以及SORT_AREA_SIZE等参数, 第四选择时间对MLOG$_T_MVLOG这个表进行重组以减少表的大小. 这些方法仅供参考.

    当在一个表上建了物化视图的日志(Materialized View Log)后, 所有的DML操作都会被相应地记录到物化视图日志表(MLOG$_)中, 如果想对这个表进行操作, 但不想这些操作被记录到日志(MVIEW LOG)中, 应当怎么办呢? 在DBMS_MVIEW包中有两个过程可以用来完成这个要求. 这里我们需要打开两个会话, 其中一个会话以DBA的身份登陆(Session DBA), 另一个会话随便了(Session USER), 按如下次序来进行操作:

    在Session USER中先运行以下语句去看一下MVIEW LOG表中有多少条记录:

SQL> SELECT count(*) FROM MLOG$_T_REORG;

  COUNT(*)
----------
         0

    在Session DBA中运行BEGIN_TABLE_REORGANIZATION过程开始维护, 记得执行完后要运行COMMIT命令, 否则会阻塞(Block)别的进程:

SQL> exec dbms_mview.begin_table_reorganization('ANYSQL','T_REORG');

PL/SQL procedure successfully completed.

    在Session USER中先运行DML直接修改表的数据, 提交后看一下MVIEW LOG表中的记录有没有增加:

SQL> insert into t_reorg select * from tab;

13 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT count(*) FROM MLOG$_T_REORG;

  COUNT(*)
----------
         0

    在Session DBA中运行END_TABLE_REORGANIZATION过程结束维护, 记得执行完后要运行COMMIT命令, 否则会阻塞(Block)别的进程:

SQL> exec dbms_mview.end_table_reorganization('ANYSQL','T_REORG');

PL/SQL procedure successfully completed.

    在Session USER中先运行一些DML语句, 检查一下MVIEW LOG表中的记录数是不是增多了:

SQL> delete t_reorg;

13 rows deleted.

SQL> commit;

Commit complete.

SQL> SELECT count(*) FROM MLOG$_T_REORG;

  COUNT(*)
----------
        13

    在这儿说了一大堆, 其实是如何让表不生成实体化视图日志的问题.