老熊的三分地-Oracle及数据恢复

Oracle提供了方便的树形查询功能，也就是connect by 语句。树形结构是很常见的，比如组织机构树，产品目录树等。本文不讲述connect by 如何使用，只是提出在使用树形查询时需要考虑的一个有关于性能方面的问题。

这里提到的问题，主要是Oracle优化器在评估connect by 语句的cardinality时，存在的缺陷，下面将举例说明。

在这个例子中所使用的表，是一个真实的生产系统中的表，BSS_ORG：

SQL> desc bss_org
 名称                         是否为空? 类型
 ---------------------------- -------- --------------

 BSS_ORG_ID                   NOT NULL NUMBER(9)
 NAME                         NOT NULL VARCHAR2(64)
 BSS_PARENT_ORG_ID                     NUMBER(9)
 BSS_ORG_LEVEL_ID             NOT NULL NUMBER(3)
 STATE                        NOT NULL VARCHAR2(3)
 STATE_DATE                            DATE
 BSS_ORG_CODE                          VARCHAR2(15)

在这个BSS_ORG表中，BSS_ORG_ID是主键，BSS_PARENT_ORG_ID与BSS_ORG_ID形成上下层级关系。这个表的统计信息如下：

Table                   Number                 Empty Average    Chain Average Global
Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats
--------------- -------------- -------- ------------ ------- -------- ------- ------
BSS_ORG                  5,739       52            0       0        0      53 YES

Column                    Column                       Distinct            Number
Name                      Details                        Values   Density Buckets
------------------------- ------------------------ ------------ --------- ------- --
BSS_ORG_ID                NUMBER(9,0) NOT NULL            5,739   .000174       1
NAME                      VARCHAR2(64) NOT NULL           5,034   .000361     200
BSS_PARENT_ORG_ID         NUMBER(9,0)                       905   .002189     200
BSS_ORG_LEVEL_ID          NUMBER(3,0) NOT NULL                6   .000087       6
STATE                     VARCHAR2(3) NOT NULL                2   .000087       2
STATE_DATE                DATE                            1,624   .001434     200
BSS_ORG_CODE              VARCHAR2(15)                    5,639   .000179     200

下面的测试，是在Oracle 11.1.0.6 for Windows版本下进行的测试。在Oracle 9i、Oracle 10g下测试的结果与Oracle 11g下测试的结果是相符的。当然这里谈到的问题是cardinality，因此在三个版本下，SQL的执行计划可能有所不同，但最终的结论是一致的。(BTW：从10g开始，connect by语句有一个新的执行步骤，称为CONNECT BY NO FILTERING，对应的Hint是no_connect_by_filtering）。

SQL> explain plan for
  2  SELECT   bss_org_id
  3        FROM bss_org t
  4  START WITH bss_org_id = 1
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |  5739 | 80346 |    16   (0)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |
|   2 |   TABLE ACCESS FULL                     | BSS_ORG |  5739 | 80346 |    16   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
       filter("BSS_ORG_ID"=1)

SQL> explain plan for
  2  SELECT   bss_org_id
  3        FROM bss_org t
  4  START WITH bss_org_id = 832044754
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

---------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |  5739 | 80346 |    16   (0)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |
|   2 |   TABLE ACCESS FULL                     | BSS_ORG |  5739 | 80346 |    16   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
       filter("BSS_ORG_ID"=832044754)

上面的2条SQL，第1条实际应该返回的行数为5739，第2条SQL实际应该返回的行数为4，但是从执行计划上看，Oracle优化器评估的行数均为5739。

SQL> create index bss_org_idx1 on bss_org(bss_parent_org_id);

索引已创建。

SQL> explain plan for
  2  SELECT   *
  3        FROM bss_org t
  4  START WITH bss_org_id = 1
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     6 |   318 |     4   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING    |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | BSS_ORG      |     1 |    53 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN          | PK_BSS_ORG   |     1 |       |     1   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                |              |       |       |            |          |
|   5 |    CONNECT BY PUMP            |              |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| BSS_ORG      |     6 |   318 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | BSS_ORG_IDX1 |     6 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
   3 - access("BSS_ORG_ID"=1)
   7 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")

SQL> explain plan for
  2  SELECT   *
  3        FROM bss_org t
  4  START WITH bss_org_id = 832044754
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id;

已解释。

SQL> @showplan

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     6 |   318 |     4   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING    |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | BSS_ORG      |     1 |    53 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN          | PK_BSS_ORG   |     1 |       |     1   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                |              |       |       |            |          |
|   5 |    CONNECT BY PUMP            |              |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| BSS_ORG      |     6 |   318 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | BSS_ORG_IDX1 |     6 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")
   3 - access("BSS_ORG_ID"=832044754)
   7 - access("BSS_PARENT_ORG_ID"=PRIOR "BSS_ORG_ID")

这里在BSS_PARENT_ORG_ID列上建一个索引，是为了使执行计划与9i、10g下的一致。
这2条SQL返回的结果行数，与前面的2条SQL一样，分别是5739和4，但是从执行计划上看，Oracle优化器评估出来的行数都是6。

从前面的两个测试来看，优化器评估出来的SQL返回的行数要么是5739（表BSS_ORG的总行数），要么是6（总行数/BSS_PARENT_ORG_ID的Disctint Values）。但无论如何，随着不同的start with条件，这个行数（cardinality）与实际返回的结果行数可能会存在非常大的差异。如果仅仅是测试中这样一个简单的SQL，实际上不会有什么问题，很容易出现问题的地方在于，一个复杂的SQL中，有类似于测试SQL这样的子查询，这样使得表连接的评估出现很大的偏差，这样容易引起非常大的性能问题。

在9i下，如果BSS_PARENT_ORG_ID上如果没有索引，那么最后一个测试SQL的执行计划如下：

SQL> explain plan for
  2  SELECT   *
  3        FROM bss_org t
  4  START WITH bss_org_id = 832044754
  5  CONNECT BY bss_parent_org_id =
  6                                PRIOR bss_org_id
  7                                ;

已解释。

SQL> @showplan

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  5739 |   297K|     9 |
|*  1 |  CONNECT BY WITH FILTERING   |             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| BSS_ORG     |       |       |       |
|*  3 |    INDEX UNIQUE SCAN         | PK_BSS_ORG  |     1 |     7 |     1 |
|   4 |   HASH JOIN                  |             |       |       |       |
|   5 |    CONNECT BY PUMP           |             |       |       |       |
|   6 |    TABLE ACCESS FULL         | BSS_ORG     |  5739 |   297K|     9 |
|   7 |   TABLE ACCESS FULL          | BSS_ORG     |  5739 |   297K|     9 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."BSS_ORG_ID"=832044754)
   3 - access("T"."BSS_ORG_ID"=832044754)

这里9i的优化器评估出来的cardinality为5739，而11g与此同样的执行计划，评估的cardinality是6。

前段时间就遇上由于connect by语句引起的性能问题。数据库为Oracle 9208，开始由于bss_org表的bss_parent_org_id列上没有索引，导致connect by部分得到的cardinality为5739，结果SQL性能非常差，在bss_parent_org_id上建索引后，执行计划改变，connect by 部分得到的cardinality为6，SQL性能大幅提升。

对于Oracle优化器不能准确评估connect by 语句的cardinality，目前没有比较好的解决办法。必要的时候只有考虑使用Hint了。欢迎朋友们针对此问题进行讨论。

此前关于字符集转换的文章，已经有三篇。写这新的一篇来源于最近有几次朋友问到的关于导入导出（exp/imp）的问题。这个问题是这样的：
使用imp导入数据后，发现数据是正确的，没有乱码，但是表和列上的注释（comments）、中文列名、Procedure/Package里面的中文全部变成了乱码。

网上很少有文章讨论到这一点，其实exp/imp与通常执行SQL引起的字符集转换有一些不同。这得从dmp文件的格式说起。
先看看下面的测试：

SQL> create table t1 ( a number,b varchar2(100));
SQL> insert into t1 values (123456,'aaaaaa');
SQL> insert into t1 values (67890,'中中中中');
SQL> commit;
SQL> comment on table t1 is '测试表';

现在将NLS_LANG设置为AMERICAN_AMERICA.ZHS16GBK，导出T1表，然后看看导出的dmp文件中的数据：

000008f0h: 22 54 31 22 0A 43 52 45 41 54 45 20 54 41 42 4C ; "T1".CREATE TABL
00000900h: 45 20 22 54 31 22 20 28 22 41 22 20 4E 55 4D 42 ; E "T1" ("A" NUMB
00000910h: 45 52 2C 20 22 42 22 20 56 41 52 43 48 41 52 32 ; ER, "B" VARCHAR2
00000920h: 28 31 30 30 29 29 20 20 50 43 54 46 52 45 45 20 ; (100)) PCTFREE
00000930h: 31 30 20 50 43 54 55 53 45 44 20 34 30 20 49 4E ; 10 PCTUSED 40 IN
00000940h: 49 54 52 41 4E 53 20 31 20 4D 41 58 54 52 41 4E ; ITRANS 1 MAXTRAN
00000950h: 53 20 32 35 35 20 53 54 4F 52 41 47 45 28 49 4E ; S 255 STORAGE(IN
00000960h: 49 54 49 41 4C 20 31 30 34 38 35 37 36 20 46 52 ; ITIAL 1048576 FR
00000970h: 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C 49 ; EELISTS 1 FREELI
00000980h: 53 54 20 47 52 4F 55 50 53 20 31 29 20 54 41 42 ; ST GROUPS 1) TAB
00000990h: 4C 45 53 50 41 43 45 20 22 54 45 53 54 5F 38 4B ; LESPACE "TEST_8K
000009a0h: 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 4F 4D 50 ; " LOGGING NOCOMP
000009b0h: 52 45 53 53 0A 49 4E 53 45 52 54 20 49 4E 54 4F ; RESS.INSERT INTO
000009c0h: 20 22 54 31 22 20 28 22 41 22 2C 20 22 42 22 29 ; "T1" ("A", "B")
000009d0h: 20 56 41 4C 55 45 53 20 28 3A 31 2C 20 3A 32 29 ; VALUES (:1, :2)
000009e0h: 0A 02 00 02 00 16 00 01 00 64 00 54 03 01 00 00 ; .........d.T....
000009f0h: 00 00 00 04 00 C3 0D 23 39 06 00 61 61 61 61 61 ; .....?#9..aaaaa
00000a00h: 61 00 00 04 00 C3 07 4F 5B 08 00 D6 D0 D6 D0 D6 ; a....?O[..中中?
00000a10h: D0 D6 D0 00 00 FF FF 0A 43 4F 4D 4D 45 4E 54 20 ; 兄?..COMMENT
00000a20h: 4F 4E 20 54 41 42 4C 45 20 22 54 31 22 20 49 53 ; ON TABLE "T1" IS
00000a30h: 20 0A 08 00 27 B2 E2 CA D4 B1 ED 27 0A 45 58 49 ; ...'测试表'.EXI
00000a40h: 54 0A 45 58 49 54 0A 00 00 00 00 00 00 00 00 00 ; T.EXIT..........

从上面的数据可以看到，有两部分数据，一部分是代码性质的数据，包括CREATE TABLE、COMMIT、INSERT等SQL语句；另一部分就是表T1的实际数据了,红色部分就是表的实际数据。

我们先看看表中的实际数据：

SQL> select a,dump(a,16) da,dump(b,16) db from t1;

         A DA                             DB
---------- ------------------------------ --------------------------------------
    123456 Typ=2 Len=4: c3,d,23,39        Typ=1 Len=6: 61,61,61,61,61,61
     67890 Typ=2 Len=4: c3,7,4f,5b        Typ=1 Len=8: d6,d0,d6,d0,d6,d0,d6,d0

对比一下就可以发现，dmp文件中T1表的数据，与数据库中原始数据是一模一样的，没有发生任何变化，也就是说，dmp文件中存储的表数据实际上与数据在数据库中存储的相同的二进制形式。

现在将NLS_LANG设置为AMERICAN_AMERICA.US7ASCII，导出T1表，然后看看导出的dmp文件中的数据：

000008f0h: 22 54 31 22 0A 43 52 45 41 54 45 20 54 41 42 4C ; "T1".CREATE TABL
00000900h: 45 20 22 54 31 22 20 28 22 41 22 20 4E 55 4D 42 ; E "T1" ("A" NUMB
00000910h: 45 52 2C 20 22 42 22 20 56 41 52 43 48 41 52 32 ; ER, "B" VARCHAR2
00000920h: 28 31 30 30 29 29 20 20 50 43 54 46 52 45 45 20 ; (100)) PCTFREE
00000930h: 31 30 20 50 43 54 55 53 45 44 20 34 30 20 49 4E ; 10 PCTUSED 40 IN
00000940h: 49 54 52 41 4E 53 20 31 20 4D 41 58 54 52 41 4E ; ITRANS 1 MAXTRAN
00000950h: 53 20 32 35 35 20 53 54 4F 52 41 47 45 28 49 4E ; S 255 STORAGE(IN
00000960h: 49 54 49 41 4C 20 31 30 34 38 35 37 36 20 46 52 ; ITIAL 1048576 FR
00000970h: 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C 49 ; EELISTS 1 FREELI
00000980h: 53 54 20 47 52 4F 55 50 53 20 31 29 20 54 41 42 ; ST GROUPS 1) TAB
00000990h: 4C 45 53 50 41 43 45 20 22 54 45 53 54 5F 38 4B ; LESPACE "TEST_8K
000009a0h: 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 4F 4D 50 ; " LOGGING NOCOMP
000009b0h: 52 45 53 53 0A 49 4E 53 45 52 54 20 49 4E 54 4F ; RESS.INSERT INTO
000009c0h: 20 22 54 31 22 20 28 22 41 22 2C 20 22 42 22 29 ; "T1" ("A", "B")
000009d0h: 20 56 41 4C 55 45 53 20 28 3A 31 2C 20 3A 32 29 ; VALUES (:1, :2)
000009e0h: 0A 02 00 02 00 16 00 01 00 64 00 54 03 01 00 00 ; .........d.T....
000009f0h: 00 00 00 04 00 C3 0D 23 39 06 00 61 61 61 61 61 ; .....?#9..aaaaa
00000a00h: 61 00 00 04 00 C3 07 4F 5B 08 00 D6 D0 D6 D0 D6 ; a....?O[..中中?
00000a10h: D0 D6 D0 00 00 FF FF 0A 43 4F 4D 4D 45 4E 54 20 ; 兄?..COMMENT
00000a20h: 4F 4E 20 54 41 42 4C 45 20 22 54 31 22 20 49 53 ; ON TABLE "T1" IS
00000a30h: 20 0A 05 00 27 3F 3F 3F 27 0A 45 58 49 54 0A 45 ; ...'???'.EXIT.E
00000a40h: 58 49 54 0A 00 00 00 00 00 00 00 00 00 00 00 00 ; XIT.............

这一次我们可以看到，红色部分的数据，也就是表的实际数据没有发生任何变化。
有变化的地方在哪里？稍微对比就可以发现，代码部分发生了变化。第一次导出时的COMMENT语句，明显可以看到“测试表”三个汉字，而第二次导出时，这三个汉字变成了三个问号。显然就是导出时GBK转换为US7ASCII码时，发生了乱码。

测试到这里，结论已经很明了了。exp导出时，表中的数据没有发生任何变化，以存储在数据库时的二进制一致的形式存储在了dmp文件中。然而，代码部分则于是纯文本形式的数据，在导出时要遵循字符集转换原则，发生转换。既然转换部分是代码形式的数据，那么下列代码中的数据都会发生转换：建表（CREATE TABLE），注释（COMMENT），创建视图（CREATE VIEW），其他程序代码（FUNCTION/PACKAGE/TRIGGER/PROCEDURE）如此等等。因此如果exp/imp时字符集不兼容，那么中文列名，注释、视图、程序代码中的中文都将会是乱码，而表中的实际数据则不会发生变化。这也是本文开头提到的问题的来源。

有下面一段SQL脚本，朋友们看能不能找出问题所在？

create table t1_bak as select * from t1 order by col_1,col_2;
truncate table t1;
insert /*+ append */ into t1 select * from t1_bak;

先不看这段SQL代码的效率如何，我们来关注一下这段代码存在的严重的安全问题。

这段代码从语法上看完全没有问题，然而....
假如这段代码是用sqlplus来运行，而第1条create table语句由于空间不足，或者由于数据量太大，临时表空间不够，排序出错，那么t1_bak的创建就会失败，而紧接着，t1会被truncate掉，结果可想而知。

这不是我临时想来的问题，而是真真实实发生在现实中的。现实中发生的这件事，比我提到的隐含的问题就明显，就是第1条create table语句，存在语法问题，结果，表被truncate了，数据丢失了。

仔细考虑维护脚本，甚为重要。