彩票走势图

　　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
　　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)。

　　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
　　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

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

　　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
　　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

　　这里在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)
　　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了。