动态SQL与绑定变量

SUNBLAZE

动态SQL与绑定变量

    作者：sunblaze

msn sunblaze@live.cn 欢迎交流

1 什么是动态SQL(Dynamic SQL)动态SQL使你在运行时，以字符串的形式构造SQL语句。这些语句包含在PL/SQL块中，并且常常包含占位符来使用绑定变量。这一点于静态SQL是不同的。静态SQL在编译时就固定了。

由于在编译时SQL的全文还不确定，所以你可使用动态SQL构建灵活的，多用途的
应用程序。可以在PL/SQL, Pro*C/C++, and Java 这几种不同的开发环境中使用动态SQL。

举一个使用动态SQL的例子，比如在一个数据仓库的环境中，在运行时才知道表的名字。这些表是以年月来明明的，例如：inv_01_2003, inv_04_2003, inv_07_2003, inv_10_2003, inv_01_2004,等等。你可以在报表程序中运行时定义这些表的名字。

　再举另外一个例子，你现在要运行一个复杂的查询，并且排序字段是要由用户来选择。为了不因为排序顺序的改变而编写更多的查询语句，你可以使用一个包含定义的排序子句的动态SQL.
2 为什么要使用动态SQL静态SQL和动态SQL都有各自的优缺点
静态SQL的优点是：
编译成功就表示访问的数据库对象有效并且有权限来访问数据库的对象。
静态SQL的性能一般要好于动态SQL

尽管如此，静态SQL也有他的局限，而这些局限动态SQL却能克服。下面列举了这些局限
　不知道PL/SQL中语句的全文。这些语句也许依赖于传入参数，或者需要程序进行处理而得到。
　执行DDL语句或者其他静态SQL不支持的语句

需要一个应对不同的数据定义，而不需要重新编译的程序。动态SQL比静态SQL更加灵活，因为它可以构造出面对不同环境都可重用的代码。

作为一般的准则，你应该在静态SQL不能达到目标，或者使用静态SQL比较繁杂的时候使用动态SQL。下面列举出使用动态SQL的典型情况。


在PL/SQL中运行Data definitionlanguage (DDL)和Session controllanguage (SCL)

运行动态查询

参照在编译时不存在的对象

为了更佳的性能


3 避免写动态SQL 使用DECODE,CASE 构造半动态SQLfile:///C:/DOCUME%7E1/SUNBLAZE/LOCALS%7E1/Temp/msohtml1/05/clip_image002.jpg
上图是淘宝网中手机选项。
经常有这样的需求。
如果输入手机型号
那么就用手机型号筛选，如果没有输入
那么就不用筛选了。

常规解决办法动态SQL

查询A

declare

c1
SYS_REFCURSOR;

i_phone_name
varchar2(20);

i_phone_type
varchar2(20);

i_phone_service varchar(20);

v_sql
varchar2(2000);

r
phone_info%rowtype;

begin

i_phone_name :='''SYS''';

v_sql := 'select * from
phone_info where 1=1 ';

if i_phone_name is not null then

v_sql := v_sql || ' and OWNER = ' || i_phone_name;

end if;

if i_phone_type is not null then

v_sql := v_sql || ' and OBJECT_NAME = ' || i_phone_type;

end if;

if i_phone_service is not null then

v_sql := v_sql || ' and OBJECT_ID= ' || i_phone_service;

end if;

v_sql := v_sql || ' and rownum < 20 ';

dbms_output.put_line(v_sql);

open c1 for v_sql;

fetch c1

into r;

while (c1%found) loop

null;

dbms_output.put_line(' OBJECT_NAME = ' || r.OBJECT_NAME);

fetch c1

into r;

end loop;

close c1;

end;

输出结果

select * from
phone_info where 1=1
and rownum < 20

OBJECT_NAME = ICOL$

OBJECT_NAME = I_USER1

OBJECT_NAME = CON$

OBJECT_NAME = UNDO$

OBJECT_NAME = C_COBJ#

OBJECT_NAME = I_OBJ#

OBJECT_NAME = PROXY_ROLE_DATA$

OBJECT_NAME = I_IND1

OBJECT_NAME = I_CDEF2

OBJECT_NAME = I_PROXY_ROLE_DATA$_1

OBJECT_NAME = FILE$

OBJECT_NAME = UET$

OBJECT_NAME = I_FILE#_BLOCK#

OBJECT_NAME = I_FILE1

OBJECT_NAME = I_CON1

OBJECT_NAME = I_OBJ3

OBJECT_NAME = I_TS#

OBJECT_NAME = I_CDEF4

OBJECT_NAME = IND$

本例中
并没有使用绑定变量。如果使用绑定变量，那么还要根据传入参数是否为空以及顺序进行判断。至于使用绑定变量的用法
将在后面得章节进行讲述。

但是通过在WHERE子句中使用DECODECASE 函数
可以避免此种情况的绑定变量。
上面的存储过程可以改写成为

declare

c1
cursor;

i_phone_name
varchar2(20);

i_phone_type
varchar2(20);


i_phone_service varchar(20);

v_sql
varchar2(2000);

r
phone_info%rowtype;
begin


open c1 for

select *

from phone_info

where owner likenvl(i_phone_name, '%')

and OBJECT_NAME likenvl(i_phone_name, '%')

and OBJECT_ID likenvl(i_phone_name, '%')

and rownum < 20;
fetch c1 into r ;

while (c1%found) loop


dbms_output.put_line(' OBJECT_NAME =' || r.OBJECT_NAME);

fetch c1

into r;

end loop;

close c1;

end;

输出结果

OBJECT_NAME = ICOL$

OBJECT_NAME = I_USER1

OBJECT_NAME = CON$

OBJECT_NAME = UNDO$

OBJECT_NAME = C_COBJ#

OBJECT_NAME = I_OBJ#

OBJECT_NAME = PROXY_ROLE_DATA$

OBJECT_NAME = I_IND1

OBJECT_NAME = I_CDEF2

OBJECT_NAME = I_PROXY_ROLE_DATA$_1

OBJECT_NAME = FILE$

OBJECT_NAME = UET$

OBJECT_NAME = I_FILE#_BLOCK#

OBJECT_NAME = I_FILE1

OBJECT_NAME = I_CON1

OBJECT_NAME = I_OBJ3

OBJECT_NAME = I_TS#

OBJECT_NAME = I_CDEF4

OBJECT_NAME = IND$

或者更简洁些

begin

for r in (select *

from sys.phone_info

where owner likenvl(i_phone_name, '%')

and OBJECT_NAME likenvl(i_phone_name, '%')

and OBJECT_ID likenvl(i_phone_name, '%')

and rownum< 20) loop

dbms_output.put_line(' OBJECT_NAME =' || r.OBJECT_NAME);

end loop;
end;

结果

OBJECT_NAME = ICOL$

OBJECT_NAME = I_USER1

OBJECT_NAME = CON$

OBJECT_NAME = UNDO$

OBJECT_NAME = C_COBJ#

OBJECT_NAME = I_OBJ#

OBJECT_NAME = PROXY_ROLE_DATA$

OBJECT_NAME = I_IND1

OBJECT_NAME = I_CDEF2

OBJECT_NAME = I_PROXY_ROLE_DATA$_1

OBJECT_NAME = FILE$

OBJECT_NAME = UET$

OBJECT_NAME = I_FILE#_BLOCK#

OBJECT_NAME = I_FILE1

OBJECT_NAME = I_CON1

OBJECT_NAME = I_OBJ3

OBJECT_NAME = I_TS#

OBJECT_NAME = I_CDEF4

OBJECT_NAME = IND$

当根据条件不同，访问不同的列时，也可以通过构造半动态SQL来达到同样的效果。
下面这个例子根据传入列名，返回对应列的值

declare
i_var varchar2(200) :='owner';
begin
dbms_output.put_line('************begin**************');
for r in (
select
(case

when upper(i_var) ='OWNER' then to_char( OWNER)
when upper(i_var) ='OBJECT_NAME' then to_char( OBJECT_NAME)
when upper(i_var) ='SUBOBJECT_NAME' then to_char( SUBOBJECT_NAME)
when upper(i_var) ='OBJECT_ID' then to_char( OBJECT_ID)
when upper(i_var) ='DATA_OBJECT_ID' then to_char( DATA_OBJECT_ID)
when upper(i_var) ='OBJECT_TYPE' then to_char( OBJECT_TYPE)
when upper(i_var) ='CREATED' then to_char( CREATED)
when upper(i_var) ='LAST_DDL_TIME' then to_char( LAST_DDL_TIME)
when upper(i_var) ='TIMESTAMP =' then to_char( TIMESTAMP)
when upper(i_var) ='STATUS' then to_char( STATUS)
when upper(i_var) ='TEMPORARY' then to_char( TEMPORARY)
when upper(i_var) ='GENERATED' then to_char( GENERATED)
when upper(i_var) ='SECONDARY' then to_char( SECONDARY)
else null
end
) col_value
from phone_info
where rownum <20)
loop
dbms_output.put_line(r.col_value);
end loop;
dbms_output.put_line('************end**************');
end ;

理论（自己总结的）
如果输入的条件没有使操作的表发生改变，那么就可以使用这种方法
构造半动态语句。

4 什么是绑定变量，为什么要使用绑定变量
绑定变量就是在动态SQL的一个占位符。它告诉Oracle现在需要生成一个执行计划，我随后会为这个占位符提供一个值。
SQL 分为动态部分与静态部分。静态部分是不变的（如数据库对象），动态部分是不固定的（过滤条件的值）。很明显的，整个数据库中所包含的对象数量是有限的，而其中所包含的数据则是无限的。而占位符替代的就是动态部分。而动态部分在一般情况下（数据倾斜除外），对执行计划的生成的影响是微乎其微的。不同的动态部分产生的执行计划都是相同的。
为什么要使用绑定变量呢。是因为绑定变量可以使游标共享（此游标非SQL中的游标）。
避免了多次硬解析。


5 游标共享与软硬解析

下面来看一下SQL解析的过程
大约可以分为如下的过程

1、客户端发出一条SQL语句，SQL语句进入共享池

该SQL语句以及相关的辅助信息组成游标
2、对SQL语句进行hash运算，得到一个hash值，转入相应的bucket中去
3、对bucket进行遍历，查找是否存在这条SQL语句
4、如果不存在这条语句

1、获得shared pool latch，然后在shared pool中进行chunk链条的遍历，找到合适的chunk，之后释放shared pool latch，chunk进入library cache中

2、硬解析开始

硬解析过程

1 语法检查

2 涉及对象存在性检查

3 同义词转换

4 对用户的对象权限检查

5 计算成本，生成执行计划
6 该游标所产生的执行计划、sql文本等装载进 library cache的heap中
在解析的过程中，进程会一直持有library cache latch，一直到硬解析结束。（而这个latch 就有可能是 引发Latch free 等待事件的Latch）

其中最消耗资源的步骤就是第五步。因为它要访问数据字典，统计信息来估算各种路径的成本，找到最合理的路径，来生成执行计划。


而软解析就不需要执行第5步。

游标
1、硬解析结束以后，产生两个游标

父游标和子游标
父游标里面包含的信息包括SQL文本和优化目标

session打开该游标以后，就会锁定父游标

所有的session都关闭该游标以后，锁定才能够释放

父游标在被锁定的时候，不能被交换出内存

父游标交换出内存、子游标也会被交换出内存

子游标被交换出内存、父游标可以不被交换出内存

因为一个父游标可能会有多个子游标 （这种情况的出现可能是因为文本相同但是用户不同，或由于动态值的不同引起的执行计划不同）。
子游标包换所有的信息：执行计划、绑定变量等

子游标随时可能被交换出内存

Oracle根据父游标的信息可以构建出一个子游标，这个过程叫reload


软解析
1、硬解析是一个完整的解析过程

如果解析过程中去掉一个或者多个步骤的话，这样的解析叫做软解析
2、在bucket中找到了sql语句，就会进行软解析，因为如果没有找到，那么说明这个语句是第一次执行，第一次执行肯定是硬解析

软解析的三种情况

1、某个session发出的SQL语句与library cache里其他session发出的SQL语句相同，解析可以省略5和6，但是2、3、4还是需要执行的
2、某个session发出的sql语句是这个session之前发出的曾经执行过的语句，那么2、3、5、6可以省略，但是4不能省略，因为中间可能发生过grant等操作
3、设置了初始化参数session_cached_cursors时，当某个session第三次执行相同的sql语句时，这个sql语句的游标信息会转移到该session的PGA中去，这样以后可以直接在session的PGA中寻找SQL，提高了效率，但是内存的消耗会很大。

Cache机制本身来讲就是以空间换时间的机制。将计算过的结果放到cache中，下次重用时，直接调用。无需再次计算结果。

6 绑定变量的优缺点及窥视特性任何方法都不是银弹，而是双刃剑。（这是一个哲学问题…… ……）

优点：绑定变量的优势是可以在库缓存中共享游标，这样就可以避免硬解析以及与之相关的额外开销..

绑定变量是一种减少应用程序在分析查询时使用栓锁数目的可靠方法。

SQL语句使用绑定变量会可以避免被注入攻击
缺点: 绑定变量在SQL要访问的表存在数据倾斜（如果某个列的数据分布不均匀，那么就称之为数据倾斜）会提供错误的执行计划。在数据仓库中这种情况尤为明显。


从Oracle 9i 开始。在执行硬解析之前，Oracle讲窥视绑定变量的字面值。然后根据字面值来优化查询。比如
一张表有一列 col1 只有1 2 两个不同的值。其中1占95% 2占5%并且改列有索引的情况下。
如果第一次运行（硬解析）时
字面值为1 那么之后的软解析都将使用索引，即使绑定变量传入的值是2 反之亦然。再次强调 Oracle只在硬解析的时候窥视绑定变量的字面值。


7 使用绑定变量的方法下面给出动态SQL与使用绑定变量的例子。并同时给出类似于Select into的例子。

declare



c1

sys_refcursor;

i_phone_name
varchar2(20);

i_phone_type
varchar2(20);

i_phone_service varchar(20);

v_sql
varchar2(2000);

r
phone_info%rowtype;

v_owner
phone_info.owner%type;

v_into_sql
varchar2(2000) := 'select ownerfrom phone_info where 1=1';
begin

i_phone_name := '''SYS''';

v_sql := 'select * from
phone_info where 1=1 ';

if i_phone_name is notnull then

v_sql := v_sql || 'and
OWNER = ' || i_phone_name;

end if;

if i_phone_type is notnull then

v_sql := v_sql || 'and OBJECT_NAME =' || i_phone_type;

end if;

if i_phone_service is notnull then

v_sql := v_sql || 'and OBJECT_ID= '|| i_phone_service;

end if;

v_sql
:= v_sql || ' and rownum < 20 ';

v_into_sql := v_into_sql ||substr(v_sql, instr(v_sql, '1=1') + 3) ||

' and rownum = 1';

dbms_output.put_line(v_sql);

open c1 for v_sql;

fetch c1

into r;

while (c1%found) loop

dbms_output.put_line(' OBJECT_NAME =' || r.OBJECT_NAME);

fetch c1

into r;

end loop;

close c1;

dbms_output.put_line('**********************into***********');


execute immediatev_into_sql

into v_owner;

dbms_output.put_line(v_owner);

end;

使用绑定变量来达到上述目的

declare

c1

sys_refcursor;

i_phone_name
varchar2(20);

i_phone_type
varchar2(20);

i_phone_service varchar(20);

v_sql
varchar2(2000);

r
phone_info%rowtype;

v_owner
phone_info.owner%type;

v_into_sql
varchar2(2000) := 'select ownerfrom phone_info where 1=1';
begin

i_phone_name := 'SYS';

i_phone_type := 'I_USER1';

v_sql := 'select * from
phone_info where 1=1 ';

if i_phone_name is notnull then

v_sql := v_sql || ' and
OWNER = :i_phone_name ';

end if;

if i_phone_type is notnull then

v_sql := v_sql || '
and
OBJECT_NAME= :i_phone_type ';

end if;

if i_phone_service is notnull then

v_sql := v_sql || ' and
OBJECT_ID= :i_phone_service ';

end if;

v_sql
:= v_sql || ' and rownum < 20 ';

v_into_sql := v_into_sql ||substr(v_sql, instr(v_sql, '1=1') + 3) || ' and rownum = 1';

dbms_output.put_line(v_into_sql);
dbms_output.put_line('****************************************************************************');

if i_phone_name is notnull then

if i_phone_type is notnull then

if i_phone_service isnot null then

open c1 for v_sql

using i_phone_name,i_phone_type, i_phone_service;

execute immediatev_into_sql

into v_owner

using i_phone_name,i_phone_type, i_phone_service;

else

open c1 for v_sql

using i_phone_name,i_phone_type;

execute immediatev_into_sql

into v_owner

using i_phone_name,i_phone_type;

end if;

else

if i_phone_service isnot null then

open c1 for v_sql


using i_phone_name,i_phone_service;

execute immediatev_into_sql

into v_owner

using i_phone_name,i_phone_service;

else

open c1 for v_sql

using i_phone_name;

execute immediatev_into_sql


into v_owner

using i_phone_name;

end if;

end if;

else

if i_phone_type is notnull then

if i_phone_service isnot null then

open c1 for v_sql

using i_phone_type,i_phone_service;

execute immediatev_into_sql

into v_owner

using i_phone_type,i_phone_service;

else

open c1 for v_sql

using i_phone_type;

execute immediatev_into_sql

into v_owner

using i_phone_type;

end if;

else

if i_phone_service isnot null then

open c1 for v_sql

using i_phone_service;

execute immediatev_into_sql

into v_owner

using i_phone_service;

else

open c1 for v_sql;

execute immediatev_into_sql

into v_owner;

end if;

end if;

end if;

fetch c1

into r;

while (c1%found) loop

null;

dbms_output.put_line(' OBJECT_NAME =' || r.OBJECT_NAME);

fetch c1

into r;

end loop;

close c1;


dbms_output.put_line('**********************into***********');

dbms_output.put_line(v_owner);
end;

其中 因为传入参数不同（参数是否为空）的不同。拼的串就不相同。这时使用的绑定变量的个数也就不相同。这个程序比较长。请大家粘贴出来看。

下面提供一个灵活动态SQL版本的。

declare

c1
SYS_REFCURSOR;

i_phone_name
varchar2(20);

i_phone_type
varchar2(20);

i_phone_service varchar(20);

v_sql
varchar2(2000);

v_into_sql
varchar2(2000) := 'select ownerfrom phone_info where 1=1';

r
phone_info%rowtype;

v_owner
varchar(2000);
begin

i_phone_name := '''SYS''';

i_phone_type := '''ICOL$''';

v_sql
:= q '{select * from
phone_info where 1=1 }';

if i_phone_name is notnull then

v_sql := v_sql || q '{and
owner = }' || i_phone_name;

end if;

if i_phone_type is notnull then

v_sql := v_sql || q '{and
OBJECT_NAME = }' || i_phone_type;

end if;

if i_phone_service is notnull then

v_sql := v_sql || q '{and
OBJECT_ID = }' || i_phone_service;

end if;

v_sql
:= v_sql || q '{ and rownum < 20 }';

v_into_sql := v_into_sql ||substr(v_sql, instr(v_sql, '1=1') + 3) ||

' and rownum = 1';

dbms_output.put_line(v_sql);

dbms_output.put_line(v_into_sql);

dbms_output.put_line('***************************************');

execute immediatev_into_sql

into v_owner;

open c1 for v_sql;

fetch c1

into r;

while (c1%found) loop

null;


dbms_output.put_line(' OBJECT_NAME = ' || r.OBJECT_NAME);

fetch c1

into r;

end loop;

close c1;
end;

[ 本帖最后由 SUNBLAZE 于 2009-12-2 10:44 编辑 ]

SUNBLAZE

8
DBA应对未使用绑定变量的方法1 前期预防 （上策）
在项目开发初期 对程序员进行培训与指导
1 引入后台开发程序员 尽量将SQL语句放入后台包（PACKAGE）中。

这样的好处：
省资源： 省去了网络传输语句的消耗。应用服务器只要传输接口与结果集即可。不需要传送SQL文本
分工明确：前台程序只是用于界面的转发。
　　　　　后台程序负责数据的提取与变更。
　　　　　前后台的程序员都只需要关心一个层面。久而久之开发技能更加专业。一般来说一个后台程序员可以配合三至五个前台开发程序员进行开发。
开发效率高：分别用专业的工具进行前台，后台开发。开发效率高。

如 ECLIPSE 的工作界面就非常适合JAVA而不适合PL/SQL

PL/SQLDEVELOPER 只能进行后台开发。

但是这两种工具在其领域内都非常的专业，高效

2 后期挽救
1 对尚在开发期的程序 建议重新开发 （中策）
2 调整数据库参数cursor_sharing与使用存储纲要 （下策）

CURSOR_SHARING参数介绍
CURSOR_SHARING 定义了SQL共享的程度
EXACT:
SQL文本完全相同，并且所操作的对象也相同时 共享游标
SIMILAR
文本不同，并且不因为文本不同而影响了语句的含义或者优化的维度。

即如果条件列上使用了直方图，则使用硬解析。反之使用软解析。
FORCE
除非文本不同改变了语句的含义，否则强制使用游标共享。
2 存储纲要

数据倾斜情况下直方图的使用Oracle分析是默认为列的不同值的行数是相同的。所以在数据倾斜的情况下，就会给出错误的执行计划。而使用直方图，就是避免这种情况。优化器在解析sql语句生成执行计划的时候会考虑到直方图的统计信息。
但是过度使用直方图也是不好的。它增加了表，索引的分析时间。也增加了执行计划的生成时间。
只有在数据倾斜的情况下，才使用直方图。

SQL> create table test_for_col
as select rownum a,rownum b from dualconnect by level<=20000 ;

Table created.

SQL> update test_for_col set a=20000 where a between10 and 20000 ;

19991 rows updated.

SQL> commit;

Commit complete.

SQL> create index idx_test on test_for_col(a);

Index created.

SQL> analyze table
test_for_col compute statistics;

Table analyzed.

SQL> analyze index idx_test compute statistics ;

Index analyzed.

SQL>
setautotrace trace exp ;

SQL> set linesize 150 ;

SQL>
select *from test_for_col where a= 1 ;

Execution Plan

----------------------------------------------------------

Planhash value: 3390667667

--------------------------------------------------------------------------------------------

|Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------------------

|
0 | SELECT STATEMENT
|
|
2000 | 12000 |
8
(0)| 00:00:01 |

|
1 |
TABLE ACCESS BY INDEX ROWID| TEST_FOR_COL |
2000 | 12000 |
8
(0)| 00:00:01 |

|*
2 |
INDEX RANGE SCAN
|IDX_TEST
|
2000 |
|
4
(0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 -access("A"=1)

SQL> select * from test_for_col where a= 20000 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 3390667667

--------------------------------------------------------------------------------------------

| Id
|Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------------------

|
0 | SELECTSTATEMENT
|
|
2000 | 12000 |
8
(0)| 00:00:01 |

|
1 |
TABLE ACCESS BY INDEX ROWID| TEST_FOR_COL|
2000 | 12000 |
8
(0)| 00:00:01 |

|*
2 |
INDEX RANGE SCAN
| IDX_TEST
|
2000 |
|
4
(0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 -access("A"=20000)

SQL> select a,count(*) from test_for_col group by aorder by a ;

A
COUNT(*)

---------- ----------

1
1

2
1

3
1

4
1

5
1

6
1

7
1

8
1

9
1

20000
19991

10 rows selected.

可以看出数据极度倾斜，并且在访问的时候给出了错误的执行计划。
现在修改分析命令构造直方图

SQL>
analyzetable
test_for_col compute statisticsfor all indexed
columns ;

Table analyzed.

这句命令的意思是
统计这个表所有索引列的信息。并构造直方图

SQL> select num_rows, blocks, empty_blocks, avg_space,chain_cnt, avg_row_len

FROM DBA_TABLES

where owner = 'SYS'

AND TABLE_NAME = UPPER('test_for_col')
2
3
4
;

NUM_ROWS
BLOCKS EMPTY_BLOCKS
AVG_SPACE
CHAIN_CNT AVG_ROW_LEN

---------- ---------- ------------ ---------- ---------------------

20000
39
0
1651
0
10

SQL> select num_distinct, low_value, high_value,density, num_buckets, last_analyzed, sample_size

fromdba_tab_columns

wheretable_name = UPPER('test_for_col') ;
2
3

NUM_DISTINCT LOW_VALUE
HIGH_VALUE
DENSITY NUM_BUCKETS LAST_ANALYZESAMPLE_SIZE

------------ -------------------------------------------- ----------- ------------ -----------

10C102
C303
.000025
10 13-OCT-09
20000

20000C102
C303
.00005
1 13-OCT-09
20000

NUM_DISTINCT
该列不同值的数量

NUM_BUCKETS
柱状图的数量

SAMPLE_SIZE
采样的数量
可以使用SAMPLE子句来指定采样的百分比或者行数

重复刚才的实验

SQL> select * from test_for_col where a= 1 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 3390667667

--------------------------------------------------------------------------------------------

| Id
|Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------------------

|
0 | SELECTSTATEMENT
|
|
1 |
6 |
2
(0)| 00:00:01 |

|
1 |
TABLE ACCESS BY INDEX ROWID| TEST_FOR_COL|
1 |
6 |
2
(0)| 00:00:01 |

|*
2 |
INDEX RANGE SCAN
| IDX_TEST
|
1 |
|
1
(0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 -access("A"=1)

SQL> select * from test_for_col where a= 20000 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 170577590

----------------------------------------------------------------------------------

| Id
|Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|

----------------------------------------------------------------------------------

|
0 | SELECTSTATEMENT
|
| 19991 |
117K|
11
(10)| 00:00:01 |

|*
1 |
TABLE ACCESS FULL| TEST_FOR_COL | 19991|
117K|
11
(10)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -filter("A"=20000)

查看直方图内容

SQL>
selecttable_name, column_name, endpoint_number, endpoint_value

fromdba_histograms

wheretable_name =
UPPER('test_for_col');

2
3

TABLE_NAME
COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE

-------------------- ---------- -----------------------------

TEST_FOR_COL
A
1
1

TEST_FOR_COL
A

2
2

TEST_FOR_COL
A
3
3

TEST_FOR_COL
A
4
4

TEST_FOR_COL
A
5
5

TEST_FOR_COL
A

6
6

TEST_FOR_COL
A
7
7

TEST_FOR_COL
A
8
8

TEST_FOR_COL
A
9
9

TEST_FOR_COL
A
20000
20000

TEST_FOR_COL
B
0
1

TABLE_NAME
COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE

-------------------- ---------- -----------------------------

TEST_FOR_COL
B
1
20000

12 rows selected.

直方图中的ENDPOINT_VALUE表示列值，ENDPOINT_NUMBER表示累积的行数。
TEST_FOR_COL
A
9
9
TEST_FOR_COL
A
20000
20000
表示A列值为20000的数据有20000-9=199991行。

nyfor

支持鼓励原创!

SUNBLAZE

以后还要再接再励

dingjun123

建议把格式修改下，支持原创！

〇〇

完全同意,要不提供一个附件

SUNBLAZE

完全没问题 提供完全的附件

[ 本帖最后由 SUNBLAZE 于 2009-12-2 13:25 编辑 ]

alynn_chan

好帖

xilangxiangwa

好样的!

smallnavy

比较复杂，慢慢看。