80后 - 数据仓库

ORACLE EXPLAIN PLAN的总结

e_mailto@163.com(admin) — Fri,06 Mar 2009 10:38:10 +0800

在ORACLE数据库中,需要对SQL语句进行优化的话需要知道其执行计划,从而针对性的进行调整.ORACLE的执行计划的获得有几种方法,下面就来总结下
1、EXPLAIN的使用

    oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。
1.1 、安装

要使用EXPLAIN首先要执行相应的脚本，创建出Explain_plan表。

具体脚本执行如下：

SQL> @D:\oracle\ora92\rdbms\admin\utlxplan.sql

Table created

该脚本后会生成一个表这个程序会创建一个名为plan_table的表。

1.2 、使用
常规使用语法：
explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]
FOR < sql_statement >

其中：
STATEMENT_ID：是一个唯一的字符串，把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。
TABLE_NAME：是plan表名，它结构如前所示，你可以任意设定这个名称。
SQL_STATEMENT：是真正的SQL语句。
比如：
SQL> explain plan set statement_id='Store_Sell' for select * from scott.Store_Sell where Store_ID='01';

Explained

执行下面语句可以查询到执行计划

SQL>Select A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
   2   FROM PLAN_TABLE   a
   3   Where STATEMENT_ID='Store_Sell'
   4   orDER BY Id;
也可以用这句话 select * from table(dbms_xplan.display); 可以把所有PLAN_TABLE里的数据罗列出来。

2 、AUTOTRACE的使用方法

2.1、安装

用sys用户运行脚本ultxplan.sql

建立这个表的脚本是：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%\rdbms\admin）ultxplan.sql。

SQL> connect sys/sys@colm2 as sysdba;

SQL> @C:\oracle\ora92\rdbms\admin\utlxplan.sql;

SQL> create public synonym plan_table for plan_table;--建立同义词

SQL> grant all on plan_table to public;--授权所有用户

要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色，这个脚本在目录（UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%\sqlplus\admin）中；

SQL> @D:\oracle\ora92\sqlplus\admin\plustrce.sql;

然后将角色plustrace授予需要autotrace的用户；

SQL>grant plustrace to public;

经过以上步骤的设置，就可以在sql*plus中使用autotrace了

2、2 使用

使用起来非常方便，只要使用一条命令就可以了

SQL>SET AUTOTRACE ON；

*autotrace功能只能在SQL*PLUS里使用

其他一些使用方法：

2.2.1、在SQLPLUS中得到语句总的执行时间

SQL> set timing on;

2.2.2、只显示执行计划--（会同时执行语句得到结果）

SQL>set autotrace on explain

比如：

sql> select count(*) from test;

count(*)

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

4

Execution plan

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

0            select statement ptimitzer=choose (cost=3 card=1)

1     0        sort(aggregate)

2     1           partition range(all)

3     2             table access (full) of 't_test' (cost=3 card=900)

2.2.3、只显示统计信息---（会同时执行语句得到结果）

SQL>set autotrace on statistics;

（备注：对于SYS用户，统计信息将会是0）

2.2.4、显示执行计划，屏蔽执行结果--（但语句实质还执行的

SQL> set autotrace on traceonly;

（备注：同SET AUTOTRACE ON; 只不过不显示结果，显示计划和统计）

2.2.5、仅仅显示执行计划，屏蔽其他一切结果--(语句还是执行了)

SQL>set autotrace on traceonly explain;

对于仅仅查看大表的Explain Plan非常管用。

2.2.6、关闭

SQL>set autotrace off;

总结：SQLPLUS 下的自动显示功能，在看执行计划中其语句还是会被执行的。尤其在执行Update/Delete语句时请千万注意，ORACLE是先执行脚本同时显示执行计划的，即使使用set autotrace on traceonly explain;

这个时候推荐使用EXPLAIN PLAN FOR来看或者PL/SQL等第三方工具

3、第三工具来看执行计划

   如果在PL/SQL中使用选择要查询语句显示执行计划，则只需要SQL WINDOWS 窗口里面输入要查询的SQL语句，然后选择按键F5或者在菜单TOOLS>Explain Plan 菜单按键就可以在执行计划窗口查看该语句的执行计划。
在TOAD语句中在执行当前的SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划信息。

4、限制

explain真正的唯一的限制是用户不能去解释其它用户的表，视图，索引或其它类型，用户必须是所有被解释事物的所有者，如果不是所有者而只有select权限，explain会返回一个错误。

Oracle SQL Trace和TKPROF的使用

e_mailto@163.com(admin) — Thu,05 Mar 2009 15:52:37 +0800

SQL TRACE 和 tkprof sql语句分析工具

一 SQL TRACE 使用方法：
  1.初始化sql trace
    参数：
      timed_statistics=true  允许sql trace 和其他的一些动态性能视图收集与时间有关的参数、
      SQL>alter session set timed_statistics=true
      max_dump_file_size=500 指定跟踪文件的大小
      SQL> alter system set max_dump_file_size=500;
      user_dump_dest  指定跟踪文件的路径
      SQL> alter system set user_dump_dest=/oracle/oracle/diag/rdbms/orcl/orcl/trace;

  2.为一个session 启动sql trace

     2.1命令方式
      alter session set sql_trace=true
     2.2 通过存储过程启动sqltrace
       select sid,serial#,osuser from v$session;
       SID    SERIAL# OSUSER
       168         1  oracle

       execute rdbms_system.set_sql_trace_in_session (168 ,1,true);
  3.停止一个sql trace 会话
      3.1 命令方式
       alter session set sql_trace=false
      3.2 储存过程的方式
        execute rdbms_system.set_sql_trace_in_session (168 ,1,false);
  4. 为整个实例启动SQL trace （一般消耗系统性能较高，不会用）
       alter system set sql_trace=true scope=spfile
     从新启动数据库
  5. 停止一个实例的sql trace
       alter system set sql_trace=flase scope=spfile

    启动sql trace 之后收集的信息包括
      1.解析、执行、返回数据的次数
      2.cpu和执行命令的时间
      3.物理读和逻辑读的次数
      4.系统处理的记录数
      5.库缓冲区错误
二 TKPROF的使用
     tkprof 的目的是将sql trace 生成的跟踪文件转换成用户可以理解的格式
    1. 格式
      tkprof inputfile outputfile [optional | parameters ]
    参数和选项：
     explain=user/password 执行explain命令将结果放在SQL trace的输出文件中
     table=schema.table 指定tkprof处理sql trace文件时临时表的模式名和表名
     insert=scriptfile 创建一个文件名为scriptfile的文件，包含了tkprof存放的输出sql语句
     sys=[yes/no] 确定系统是否列出由sys用户产生或重调的sql语句
     print=number 将仅生成排序后的第一条sql语句的输出结果
     record=recordfile 这个选项创建一个名为recorderfile的文件，包含了所有重调用的sql语句
     sort=sort_option 按照指定的方法对sql trace的输出文件进行降序排序
             sort_option 选项
                prscnt  按解析次数排序
                prscpu  按解析所花cpu时间排序
                prsela  按解析所经历的时间排序
                prsdsk  按解析时物理的读操作的次数排序
                prsqry  按解析时以一致模式读取数据块的次数排序
                prscu   按解析时以当前读取数据块的次数进行排序
                execnt  按执行次数排序
                execpu  按执行时花的cpu时间排序
                exeela  按执行所经历的时间排序
                exedsk  按执行时物理读操作的次数排序
                exeqry  按执行时以一致模式读取数据块的次数排序
                execu   按执行时以当前模式读取数据块的次数排序
                exerow  按执行时处理的记录的次数进行排序
                exemis  按执行时库缓冲区的错误排序
                fchcnt  按返回数据的次数进行排序
                fchcpu  按返回数据cpu所花时间排序
                fchela  按返回数据所经历的时间排序
                fchdsk  按返回数据时的物理读操作的次数排序
                fchqry  按返回数据时一致模式读取数据块的次数排序
                fchcu   按返回数据时当前模式读取数据块的次数排序
                fchrow  按返回数据时处理的数据数量排序
三 sql trace 的输出结果
         count：提供OCI过程的执行次数
         CPU：  提供执行CPU所花的时间单位是秒
         Elapsed：提供了执行时所花的时间。单位是秒。这个参数值等于用户响应时间
         Disk：提供缓存区从磁盘读取的次数
         Query：以一致性模式从缓存区获得数据的次数
         Current：以当前模式从缓存区获得数据的次数
         ROWs：返回调用或执行调用时，处理的数据行的数量。

在report.txt文件头有各个数据的解释，根据以下一些指标可以分析一下SQL的执行性能: query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse。要检查Pro*C程序的MAXOPENCURSORS是不是太低了，或不适当的使用的RELEASE_CURSOR选项
rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。在Pro*C中可以用prefetch=NN,Java/JDBC中可调用SETROWPREFETCH,在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
cpu  or  elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化
执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少

四：举例：
sql>alter session set sql_trace=true
SQL>select * from dba_users;
SQL>show parameter user_dump_dest
         user_dump_dest     string  /oracle/oracle/diag/rdbms/orcl/orcl/trace
SQL>exit

Oracle分区表

e_mailto@163.com(admin) — Wed,04 Mar 2009 17:25:45 +0800

分区表：

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
oracle中提供了以下几种表分区：
一、范围分区：这种类型的分区是使用列的一组值，通常将该列成为分区键。
示例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：

 

Create TABLE CUSTOMER 
( 
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
FIRST_NAME VARCHAR2(30) NOT NULL, 
LAST_NAME VARCHAR2(30) NOT NULL, 
PHONE VARCHAR2(15) NOT NULL, 
EMAIL VARCHAR2(80), 
STATUS CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
) 

注意：在创建表进行分区时，表空间必须先存在，而且建议将不同的分区放入不同的表空间中。
示例2：假设有ORDER_ACTIVITIES表，每6个月对订单进行清理，我们可以按月份对表进行分区，分区代码如下：

 
 
Create TABLE orDER_ACTIVITIES 
( 
orDER_ID NUMBER(7) NOT NULL, 
orDER_DATE DATE, 
TOTAL_AMOUNT NUMBER, 
CUSTOTMER_ID NUMBER(7), 
PAID CHAR(1) 
) 
PARTITION BY RANGE (ORDER_DATE) 
( 
PARTITION orD_ACT_PART01 VALUES LESS THAN (TO_DATE('01-MAY-2003','DD-MON-YYYY')) TABLESPACE orD_TS01, 
PARTITION orD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE orD_TS02, 
PARTITION orD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE orD_TS03 
) 

二、列表分区：该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
示例1：

 

Create TABLE PROBLEM_TICKETS 
( 
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, 
DESCRIPTION VARCHAR2(2000), 
CUSTOMER_ID NUMBER(7) NOT NULL, 
DATE_ENTERED DATE NOT NULL, 
STATUS VARCHAR2(20) 
) 
PARTITION BY LIST (STATUS) 
( 
PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01, 
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02 
) 

三、散列分区：这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。请看下列示例：
示例1：

 

Create TABLE HASH_TABLE 
( 
COL NUMBER(8), 
INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
PARTITION PART01 TABLESPACE HASH_TS01, 
PARTITION PART02 TABLESPACE HASH_TS02, 
PARTITION PART03 TABLESPACE HASH_TS03 
) 

四、复合范围列表分区：这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
示例1：

 

Create TABLE SALES 
( 
PRODUCT_ID VARCHAR2(5), 
SALES_DATE DATE, 
SALES_COST NUMBER(10), 
STATUS VARCHAR2(20) 
) 
PARTITION BY RANGE(SALES_DATE) 
SUBPARTITION BY LIST (STATUS) 
( 
PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE P1_TS 
( 
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE SUBP1_TS1, 
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE SUBP1_TS2 
), 
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE P2_TS 
( 
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE SUBP2_TS1, 
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE SUBP2_TS2 
) 
) 

示例2：使用TEMPLATE模板

 

Create TABLE SALES 
( 
PRODUCT_ID VARCHAR2(5), 
SALES_DATE DATE, 
SALES_COST NUMBER(10), 
STATUS VARCHAR2(20) 
) 
PARTITION BY RANGE(SALES_DATE) 
SUBPARTITION BY LIST (STATUS) 
SUBPARTITION TEMPLATE 
( 
SUBPARTITION SUB1 VALUES ('ACTIVE') TABLESPACE SUBP1_TS1, 
SUBPARTITION SUB2 VALUES ('INACTIVE') TABLESPACE SUBP2_TS2 
) 
( 
PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE P1_TS, 
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE P2_TS 
) 

五、复合范围散列分区：这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。与上面的定义方式非常的类似，在此不单独举例。

表分区对于用户来说是透明的，我们在插入数据时Oracle会自动判断插入的数据，然后放入相应的表分区中。但有时我们想单独查询某个分区中的数据时，就必须手工指定分区的名称。
示例1：(此示例基于：四、复合范围列表分区的示例一)
向SALES表插入记录，不必指定表分区。

 

Insert INTO SALES VALUES('00001','01-1月-02',100,'ACTIVE') 
/ 
Insert INTO SALES VALUES('00002','01-1月-01',200,'ACTIVE') 
/ 
Insert INTO SALES VALUES('00003','01-2月-03',300,'INACTIVE') 
/ 
Insert INTO SALES VALUES('00004','04-2月-03',300,'INACTIVE') 
/ 
Insert INTO SALES VALUES('00005','04-2月-02',300,'INACTIVE') 
/ 

不指定表分区查看SALES表信息：
Select * FROM SALES; 结果如下所示：
指定P1表分区查询SALES表信息：
Select * FROM SALES PARTITION(P1); 结果如下所示：
指定P1SUB1子分区查询SALES表信息:
Select * FROM SALES SUBPARTITION(P1SUB1); 结果如下所示：
示例2：(此示例基于：四、复合范围列表分区的示例二)
示例2基于TEMPLATE模板的表分区，查询稍稍烦琐一点。
指定P1表分区查询SALES表信息：
Select * FROM SALES PARTITION(P1); 结果如下所示,和刚才查询一致。
指定SUB1子分区查询SALES表信息:
Select * FROM SALES SUBPARTITION(SUB1); 出现如下错误信息：
怎么解决以上问题呢？我们通过sys模式查看分区信息的数据字典,如下：
可以看出子分区不叫SUB1，而是P1_SUB1,重新查询信息，如下图所示：

有关表分区的一些维护性操作：
一、添加分区
以下代码给SALES表添加了一个P3分区
Alter TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
注意：以上添加的分区界限应该高于最后一个分区界限。

以下代码给SALES表的P3分区添加了一个P3SUB1子分区
Alter TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

二、删除分区
以下代码删除了P3表分区：
Alter TABLE SALES Drop PARTITION P3;

在以下代码删除了P4SUB1子分区：
Alter TABLE SALES Drop SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

三、截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
Alter TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区：
Alter TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

四、合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：
Alter TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

五、拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
Alter TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD'))
INTO (PARTITION P21,PARTITION P22);

六、接合分区(coalesca) 结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
Alter TABLE SALES COALESCA PARTITION;

七、重命名表分区
以下代码将P21更改为P2
Alter TABLE SALES RENAME PARTITION P21 TO P2;

九、跨分区查询 select sum( *) from (
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2));

十、查询表上有多少分区
Select * FROM useR_TAB_PARTITIONS Where TABLE_NAME='tableName'

十一、查询索引信息 select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc

--显示数据库所有分区表的信息：
select * from DBA_PART_TABLES

--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES

--显示当前用户所有分区表的信息：
select * from USER_PART_TABLES

--显示表分区信息显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS

--显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS

--显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS

--显示子分区信息显示数据库所有组合分区表的子分区信息：
select * from DBA_TAB_SUBPARTITIONS

--显示当前用户可访问的所有组合分区表的子分区信息：
select * from ALL_TAB_SUBPARTITIONS

--显示当前用户所有组合分区表的子分区信息：
select * from USER_TAB_SUBPARTITIONS

--显示分区列显示数据库所有分区表的分区列信息：
select * from DBA_PART_KEY_COLUMNS

--显示当前用户可访问的所有分区表的分区列信息：
select * from ALL_PART_KEY_COLUMNS

--显示当前用户所有分区表的分区列信息：
select * from USER_PART_KEY_COLUMNS

--显示子分区列显示数据库所有分区表的子分区列信息：
select * from DBA_SUBPART_KEY_COLUMNS

--显示当前用户可访问的所有分区表的子分区列信息：
select * from ALL_SUBPART_KEY_COLUMNS

--显示当前用户所有分区表的子分区列信息：
select * from USER_SUBPART_KEY_COLUMNS

--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'

--删除一个表的数据是
truncate table table_name;

--删除分区表一个分区的数据是
alter table table_name truncate partition p5;

注：分区根据具体情况选择。

表分区有以下优点：
1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。
2、数据修剪：保存历史数据非常的理想。
3、备份：将大表的数据分成多个文件，方便备份和恢复。
4、并行性：可以同时向表中进行DML操作，并行性性能提高。
================================================

索引：
1、一般索引：
create index index_name on table(col_name);
2、Oracle 分区索引详解
语法：Table Index
Create [UNIQUE|BITMAP] INDEX [schema.]index_name
ON [schema.]table_name [tbl_alias]
(col [ASC | DESC]) index_clause index_attribs

index_clauses:
分以下两种情况

1. Local Index
就是索引信息的存放位置依赖于父表的Partition信息，换句话说创建这样的索引必须保证父表是Partition
1.1 索引信息存放在父表的分区所在的表空间。但是仅可以创建在父表为HashTable或者composite分区表的。
LOCAL STORE IN (tablespace)
1.2 仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致
LOCAL STORE IN (tablespace) (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

1.3 索引信息存放在父表的分区所在的表空间，这种语法最简单，也是最常用的分区索引创建方式。
Local
1.4 并且指定的Partition 数目要与父表的Partition要一致
LOCAL (PARTITION [partition
[LOGGING|NOLOGGING]
[TABLESPACE {tablespace|DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause]
[STORE IN {tablespace_name|DEFAULT]
[SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

Global Index
索引信息的存放位置与父表的Partition信息完全不相干。甚至父表是不是分区表都无所谓的。语法如下：
GLOBAL PARTITION BY RANGE (col_list)
( PARTITION partition VALUES LESS THAN (value_list)
[LOGGING|NOLOGGING]
[TABLESPACE {tablespace|DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause] )
但是在这种情况下，如果父表是分区表，要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确
Alter TABLE TableName Drop PARTITION PartitionName Update Global Indexes

--查询索引
select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc

SQL Server重建索引和重新生成索引

e_mailto@163.com(admin) — Tue,03 Mar 2009 16:03:29 +0800

当对表中的数据进行插入、更新、删除操作时，SQL Server对自动维护索引，这些修改可能会导致索引中的信息分散在数据库中，当索引包含的页的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片，碎片非常多的索引会降低查询性能。

在 SQL Server 2005 中，可以通过重新组织索引或重新生成索引来修复索引碎片。对于基于分区方案生成的已分区索引，可以在完整索引或索引的单个分区上使用下列方法之一。

决定使用哪种碎片整理方法的第一步是分析索引以确定碎片程度。通过使用系统函数 sys.dm_db_index_physical_stats，您可以检测特定索引、表或索引视图的所有索引、数据库中所有索引或所有数据库中所有索引中的碎片。对于已分区索引，sys.dm_db_index_physical_stats 还提供每个分区的碎片信息。

SQL Server 2005 中计算碎片的算法比 SQL Server 2000 中的算法更精确。因此，碎片值显得更高。例如，在 SQL Server 2000 中，如果某表的页 11 和页 13 在同一区，而页 12 不在该区，则不会将该表视为碎片。但若要访问这两页，却需要两个物理 I/O 操作，因此在 SQL Server 2005 中，此表被计为碎片。

由 sys.dm_db_index_physical_stats 函数返回的结果集包含以下列。

--------------------------------------------------------------------------------------------
列                               |    说明
--------------------------------------------------------------------------------------------
avg_fragmentation_in_percent |    逻辑碎片（索引中的无序页）的百分比。

fragment_count                         |     索引中的碎片（物理上连续的叶页）数量。

avg_fragment_size_in_pages   |    索引中一个碎片的平均页数。
---------------------------------------------------------------------------------------------

知道碎片程度后，可以使用下表确定修复碎片的最佳方法。
-----------------------------------------------------------------
avg_fragmentation_in_percent 值 | 修复语句
> 5% 且 < = 30%                          |  Alter INDEX REORGANIZE
------------------------------------------------------------------
> 30%                                            | Alter INDEX REBUILD WITH (ONLINE = ON)*
------------------------------------------------------------------

* 重新生成索引可以联机执行，也可以脱机执行。重新组织索引始终联机执行。若要获得与重新组织选项相似的可用性，应联机重新生成索引。

这些值提供了一个大致指导原则，用于确定应在 Alter INDEX REORGANIZE 和 Alter INDEX REBUILD 之间进行切换的点。不过，实际值可能会随情况而变化。必须要通过试验来确定最适合您环境的阈值。

非常低的碎片级别（小于 5％）不应通过这些命令来解决，因为删除如此少量的碎片所获得的收益始终远低于重新组织或重新生成索引的开销。

注意：
通常，重新生成或重新组织小索引一般不会减少碎片。小索引的页面存储在混合区中。混合区最多可由八个对象共享，因此重新组织或重新生成小索引后可能不会减少索引中的碎片。有关混合区的详细信息，请参阅页和区。

重新组织索引
若要重新组织一个或多个索引，可以使用带 REORGANIZE 子句的 Alter INDEX 语句。此语句可以替代 DBCC INDEXDEFRAG 语句。若要重新组织已分区索引的单个分区，可以使用 Alter INDEX 的 PARTITION 子句。

重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使页有序可以提高索引扫描的性能。索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移页。

重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于 sys.indexes 目录视图中的填充因子值。

重新组织进程使用最少的系统资源。而且，重新组织是自动联机执行的。该进程不持有长期阻塞锁，所以不会阻止运行查询或更新。

索引碎片不太多时，可以重新组织索引。请参阅上面的表，了解有关碎片的指导原则。不过，如果索引碎片非常多，重新生成索引则可以获得更好的结果。

大型对象数据类型压缩
重新组织索引时，除了重新组织一个或多个索引外，默认情况下还将压缩聚集索引或基础表中包含的大型对象数据类型 (LOB)。数据类型 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 都是大型对象数据类型。压缩此数据可以改善磁盘空间使用情况：

重新组织指定的聚集索引将压缩该聚集索引的叶级别（数据行）包含的所有 LOB 列。

重新组织非聚集索引将压缩该索引中属于非键（包含性）列的所有 LOB 列。

如果指定 ALL，将重新组织与指定的表或视图相关联的所有索引，并压缩与聚集索引、基础表或带有包含列的非聚集索引相关联的所有 LOB 列。

如果 LOB 列不存在，则忽略 LOB_COMPACTION 子句。

重新生成索引
重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。

可以使用下列方法重新生成聚集索引和非聚集索引：

带 REBUILD 子句的 Alter INDEX。此语句将替换 DBCC DBREINDEX 语句。

带 Drop_EXISTING 子句的 Create INDEX。

每个方法执行的功能都相同，但如下表所示，也都各有优缺点需要考虑。

在重新生成操作期间禁用非聚集索引以节省磁盘空间
禁用非聚集索引后，将删除索引数据行，但索引定义仍在元数据中。重新生成索引后即启用该索引。如果不禁用非聚集索引，重新生成操作需要足够的临时磁盘空间才能同时存储新旧索引。但是，通过在单独的事务中禁用并重新生成非聚集索引，禁用索引所释放的磁盘空间可重新用于随后的重新生成或其他任何操作。除用于排序的临时磁盘空间外不需要额外空间，用于排序的临时磁盘空间通常是索引大小的 20%。如果非聚集索引在主键上，将自动禁用引用 FOREIGN KEY 的任何活动约束。必须在重新生成索引后手动启用这些约束。有关详细信息，请参阅禁用索引和启用索引和约束指南。

 
Alter Index Idx_1 ON Table.Col Rebuild

Create Index Idx_2 ON Table.Col WITH(Drop_Existing=ON)

SQL Server 更新，值来自其他的表

e_mailto@163.com(admin) — Tue,03 Mar 2009 15:21:01 +0800

SQL Server对一个表的字段进行更新，当更新的值来自其他的表时，有两种方法：

 

Create table Update_From1
(
id int,
name varchar(20)
)

Create table Update_From2
(
id int,
des varchar(20)
)

truncate table Update_From1

insert into Update_From1
select 1,'AAA'
union all
select 2,'BBB'
union all
select 3,'CCC'
union all
select 4,'DDD'


insert into Update_From2
select 1,'第一组'
union all
select 2,'第二组'

1.EXISTS

 

Update Update_From1
SET NAME=
(
Select DES
FROM Update_From2 B
Where B.id=Update_From1.id
)
Where EXISTS
(
Select DES
FROM Update_From2 B
Where B.id=Update_From1.id
)

2.FROM

 
Update Update_From1
SET name=B.des
FROM Update_From2 B
Where Update_From1.id=B.id

SQL Server 2005 分区表处理海量数据

e_mailto@163.com(admin) — Mon,29 Dec 2008 11:36:02 +0800

当单个表存储的数据量过大时，为了提高数据的速度，可以将表在水平上分别存放在若干个不同的物理文件上从而减少单个物理文件的大小，这样避免从一个大物理文件速度数据，当服务器有多CPU和多硬盘时效果更明显，而且方便管理；SQL Server 2000没有分区表的功能，只有分区视图，但不实用，Oracle也有分区表，Oracle的分区表更有技术含量且易管理，这里只讨论MS SQL 2005的分区表。

为了提高报表速度，上周将数据库从2000切换到2005，结合实际例子介绍分区表：

1.创建多个文件组（具体脚本略）
考虑数据的增长，新增6个文件组，每个文件组两个物理文件。

2.创建分区函数
创建6个分区段：
Create PARTITION FUNCTION [PF_DATESELL](int)
AS RANGE RIGHT FOR VALUES
(20080101, 20080401, 20080701, 20081001, 20090101)

3.创建分区方案
分别将上面创建的6个分区映射到6个文件组
Create PARTITION SCHEME [PC_DATESELL]
AS PARTITION [PF_DATESELL]
TO ([PART1], [PART2], [PART3], [PART4], [PART5], [PART6])

4.创建分区表，并指定在哪个字段上分区
Create TABLE orDER_DETAIL(
    ..........................
    [DATE_SELL_ID] [int] NULL,
    ..........................
) ON PC_DATESELL(DATE_SELL_ID)

5.查询每个分区的数据情况
Select $PARTITION.PF_DateSell(DATE_SELL_ID) AS PARTITION_NUM,
       MIN(DATE_SELL_ID) AS MIN_Date,
       MAX(DATE_SELL_ID) AS MAX_Date,
       COUNT(*) AS ROWS
FROM   dbo.ORDER_DETAIL
GROUP  BY $PARTITION.PF_DateSell(DATE_SELL_ID)
orDER  BY PARTITION_NUM

PARTITION_NUM    MIN_Date    MAX_Date    ROWS
-----------------------------------------------------------------------------
1    20070725    20071231    4553156
2    20080101    20080331    4376303
3    20080401    20080630    5763746
4    20080701    20080930    6966526
5    20081001    20081218    5858458

如何在 SQL Server 2005 中为安装程序增加计数器注册表项值

e_mailto@163.com(admin) — Mon,22 Dec 2008 16:51:23 +0800

在 SQL Server 安装开始前，Microsoft SQL Server 安装程序中的安装配置检查器 (SCC) 会验证计数器注册表项的值。如果 SCC 无法验证现有的注册表项，或 SCC 无法运行 lodctr.exe 系统程序，则 SCC 检查会失败，致使安装受阻。

手动设置计数器注册表项的增量
在 Microsoft Windows 2003 或 Windows XP 桌面上，依次单击“开始”、“运行”，然后在“打开”中键入 regedit.exe，再单击“确定”。在 Windows 2000 中，使用 regedt32.exe 启动注册表编辑器。

定位到以下注册表项：

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib]

"Last Counter"=dword:00000ed4 (5276)

"LastHelp"=dword:00000ed5 (5277)

上一步的“Last Counter”值 (5276) 必须与以下注册表项中“Perflib\009”（或Perflib\004，009是英文，004是英文）的“Counter”项的最大值匹配，并且上一步的“Last Help”值 (5277) 必须与以下注册表项中“Perflib\009”的“Help”项的最大值匹配。

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009]

注意 009 是英文中的一个示例。“Last Counter”和“Last Help”值是由 Windows 动态分配的；这两个值会因计算机的不同而不同。

如有必要，可修改“\Perflib”项中的“Last Counter”和“Last Help”值的值：在右侧窗格中，右键单击“Last Counter”或“Last Help”，单击“修改”，再单击“Base = "Decimal"”，在“值数据”中设置值，再单击“确定”。如有必要，对另一个项重复以上过程，然后关闭注册表编辑器。

再次运行 SQL Server 安装程序。

SQL Server 数据库备份脚本

e_mailto@163.com(admin) — Sun,09 Nov 2008 19:03:57 +0800

备份策略：每天备份一次，周日全量备份，周一至六差异备份，每次每个数据库生成一个备份文件，每天备份时删除上周同一天的备份

备份的存储过程，数据库名作为参数：
Create Procedure Pro_BackUp @DB_Name varchar(50)
as
declare @weekday varchar(20)
declare @sql_with varchar(100),@sql varchar(300)
declare @today varchar(10),@Last_week varchar(10)
declare @del varchar(100)
select @today=Year(getdate())*10000+Month(getdate())*100+Day(getdate())
select @Last_week=Year(getdate()-7)*10000+Month(getdate()-7)*100+Day(getdate()-7)
set datefirst 1
select @weekday=datepart(dw,getdate())
--增量、差异备份
if @weekday=7
set @sql_with='WITH INIT,NAME = N''CRM-完整数据库备份'+@today+''''
else
set @sql_with='WITH INIT,DIFFERENTIAL,NAME = N''CRM-差异数据库备份'+@today+''''
--备份脚本
set @sql='BACKUP DATABASE '+@DB_Name+' TO DISK = N''C:\'+@DB_Name+'_'+@today+'.bak'' '+@sql_with +',NOFORMAT,SKIP,NOREWIND,NOUNLOAD,STATS = 10'
--print @sql
EXEC (@sql)
--删除上周备份，考虑操作系统的安全，建议不用XP _cmdshell
/*
set @del='del C:\'+@DB_Name+_'+@Last_week+'.bak'
print @del
EXEC master..XP _cmdshell @del,no_output
*/

使用游标备份每个数据库：
declare @DBName varchar(50)
declare Cur_DBName cursor for
select name
from master..sysdatabases
where NAME NOT IN ('master', 'model','msdb', 'tempdb','Northwind','pubs')
open Cur_DBName
fetch next from Cur_DBName into @DBName
while @@fetch_status=0
begin
exec Pro_BackUp @DBName
fetch next from Cur_DBName into @DBName
end
close Cur_DBName
deallocate Cur_DBName

脚本适用于2000和2005版本

日志：
25% backed up。
51% backed up。
77% backed up。
99% backed up。
已处理 464 页，这些页属于数据库 'CRM' 的文件 'CRM_dat'（位于文件 1 上）。
100% backed up。
已处理 1 页，这些页属于数据库 'CRM' 的文件 'CRM_log'（位于文件 1 上）。
BACKUP DATABASE 操作成功地处理了 465 页，花费了 0.738 秒（5.151 MB/秒）。

RFM模型

e_mailto@163.com(admin) — Thu,23 Oct 2008 10:51:02 +0800

　　根据美国数据库营销研究所Arthur Hughes的研究，客户数据库中有三个神奇的要素，这三个要素构成了数据分析最好的指标：
　　最近一次消费(Recency)
　　消费频率(Frenquency)
　　消费金额(Monetary)

最近一次消费

　　最近一次消费意指上一次购买的时候——顾客上一次是几时来店里、上一次根据哪本邮购目录购买东西、什么时候买的车，或在你的超市买早餐最近的一次是什么时候。
　　理论上，上一次消费时间越近的顾客应该是比较好的顾客，对提供即时的商品或是服务也最有可能会有反应。营销人员若想业绩有所成长，只能靠偷取竞争对手的市场占有率，而如果要密切地注意消费者的购买行为，那么最近的一次消费就是营销人员第一个要利用的工具。历史显示，如果我们能让消费者购买，他们就会持续购买。这也就是为什么，0至6个月的顾客收到营销人员的沟通信息多于31至36个月的顾客。
　　最近一次消费的过程是持续变动的。在顾客距上一次购买时间满一个月之后，在数据库里就成为最近一次消费为两个月的客户。反之，同一天，最近一次消费为3个月前的客户作了其下一次的购买，他就成为最近一次消费为一天前的顾客，也就有可能在很短的期间内就收到新的折价信息。
　　最近一次消费的功能不仅在于提供的促销信息而已，营销人员的最近一次消费报告可以监督事业的健全度。优秀的营销人员会定期查看最近一次消费分析，以掌握趋势。月报告如果显示上一次购买很近的客户，(最近一次消费为1个月)人数如增加，则表示该公司是个稳健成长的公司；反之，如上一次消费为一个月的客户越来越少，则是该公司迈向不健全之路的征兆。
　　最近一次消费报告是维系顾客的一个重要指标。最近才买你的商品、服务或是光顾你商店的消费者，是最有可能再向你购买东西的顾客。再则，要吸引一个几个月前才上门的顾客购买，比吸引一个一年多以前来过的顾客要容易得多。营销人员如接受这种强有力的营销哲学——与顾客建立长期的关系而不仅是卖东西，会让顾客持续保持往来，并赢得他们的忠诚度。

消费频率

　　消费频率是顾客在限定的期间内所购买的次数。我们可以说最常购买的顾客，也是满意度最高的顾客。如果相信品牌及商店忠诚度的话，最常购买的消费者，忠诚度也就最高。增加顾客购买的次数意味着从竞争对手处偷取市场占有率，由别人的手中赚取营业额。
　　根据这个指标，我们又把客户分成五等分，这个五等分分析相当于是一个“忠诚度的阶梯”(loyalty ladder)，其诀窍在于让消费者一直顺着阶梯往上爬，把销售想象成是要将两次购买的顾客往上推成三次购买的顾客，把一次购买者变成两次的。

消费金额

　　消费金额是所有数据库报告的支柱，也可以验证“帕雷托法则”(Pareto’s Law)——公司80％的收入来自20％的顾客。它显示出排名前10％的顾客所花费的金额比下一个等级者多出至少2倍，占公司所有营业额的40％以上。如看累计百分比的那一栏，我们会发现有40％的顾客贡献公司总营业额的80％；而有60％的客户占营业额的90％以上。最右的一栏显示每一等分顾客的平均消费，表现最好的 10％的顾客平均花费1195美元，而最差的10％仅有18美元。
　　如果你的预算不多，而且只能提供服务信息给2000或 3000个顾客，你会将信息邮寄给贡献40％收入的顾客，还是那些不到1％的顾客？数据库营销有时候就是这么简单。这样的营销所节省下来的成本会很可观。
　　结合这三个指标，我们就可以把顾客分成5＊5＊5 = 125类，对其进行数据分析，然后制定我们的营销策略。
　　最近一次消费、消费频率、消费金额是测算消费者价值最重要也是最容易的方法，这充分的表现了这三个指标对营销活动的指导意义。而其中，最近一次消费是最有力的预测指标。

RFM模型的应用意义

　　在众多的客户关系管理(CRM)的分析模式中，RFM模型是被广泛提到的。RFM模型是衡量客户价值和客户创利能力的重要工具和手段。该模型通过一个客户的近期购买行为、购买的总体频率以及花了多少钱三项指标来描述该客户的价值状况。
　　RFM模型较为动态地层示了一个客户的全部轮廓，这对个性化的沟通和服务提供了依据，同时，如果与该客户打交道的时间足够长，也能够较为精确地判断该客户的长期价值(甚至是终身价值)，通过改善三项指标的状况，从而为更多的营销决策提供支持。
　　在RFM模式中，R(Recency)表示客户最近一次购买的时间有多远，F(Frequency)表示客户在最近一段时间内购买的次数，M (Monetary)表示客户在最近一段时间内购买的金额。一般的分析型CRM着重在对于客户贡献度的分析，RFM则强调以客户的行为来区分客户。
　　RFM非常适用于生产多种商品的企业，而且这些商品单价相对不高，如消费品、化妆品、小家电、录像带店、超市等；它也适合在一个企业内只有少数耐久商品，但是该商品中有一部分属于消耗品，如复印机、打印机、汽车维修等消耗品；RFM对于加油站、旅行保险、运输、快递、快餐店、KTV、行动电话信用卡、证券公司等也很适合。
　　RFM可以用来提高客户的交易次数。业界常用的DM(直接邮寄)，常常一次寄发成千上万封邮购清单，其实这是很浪费钱的。根据统计(以一般邮购日用品而言)，如果将所有R(Recency)的客户分为五级，最好的第五级回函率是第四级的三倍，因为这些客户刚完成交易不久，所以会更注意同一公司的产品信息。如果用M(Monetary)来把客户分为五级，最好与次好的平均回复率，几乎没有显著差异。
　　有些人会用客户绝对贡献金额来分析客户是否流失，但是绝对金额有时会曲解客户行为。因为每个商品价格可能不同，对不同产品的促销有不同的折扣，所以采用相对的分级(例如R、F、M都各分为五级)来比较消费者在级别区间的变动，则更可以显现出相对行为。企业用R、F的变化，可以推测客户消费的异动状况，根据客户流失的可能性，列出客户，再从M（消费金额）的角度来分析，就可以把重点放在贡献度高且流失机会也高的客户上，重点拜访或联系，以最有效的方式挽回更多的商机。
　　RFM也不可以用过头，而造成高交易的客户不断收到信函。每一个企业应该设计一个客户接触频率规则，如购买三天或一周内应该发出一个感谢的电话或Email，并主动关心消费者是否有使用方面的问题，一个月后发出使用是否满意的询问，而三个月后则提供交叉销售的建议，并开始注意客户的流失可能性，不断地创造主动接触客户的机会。这样一来，客户再购买的机会也会大幅提高。
　　企业在推行CRM时，就要根据RFM模型的原理，了解客户差异，并以此为主轴进行企业流程重建，才能创新业绩与利润。否则，将无法在新世纪的市场立足

9月31号！

e_mailto@163.com(admin) — Thu,09 Oct 2008 22:50:42 +0800

下午客户说数据导入系统有个配置导不进去数了，别的配置还可以，初步怀疑是模板问题，发了最新的模板过去还是不行，让同事按照新模板重新配置，还是导不进去，当初开发人员做的程序错误日志太少了，最后没办法我也过去。

重新修改配置、新建配置，不行，后来发现业务人员上报的数据日期列竟然是9月31号，汗。本来很简单的一个问题，若是程序里错误提示能清晰点、业务人员细心点、当初我怎么也没想到。