[转帖]使用Oracle树形查询应注意_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3473 | 回复: 0   主题: [转帖]使用Oracle树形查询应注意        下一篇 
derek
注册用户
等级:中校
经验:1550
发帖:209
精华:0
注册:2011-7-21
状态:离线
发送短消息息给derek 加好友    发送短消息息给derek 发消息
发表于: IP:您无权察看 2011-8-3 15:55:03 | [全部帖] [楼主帖] 楼主

    【IT168 技术文档】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了。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论