Oracel 常用笔记

oracle中Where子句的条件顺序对性能的影响

oracle中Where子句的条件顺序对性能的影响 - Oracle - 网站开发-技术教程-中国素材网

经常有人问到oracle中的Where子句的条件书写顺序是否对SQL性能有影响，我的直觉是没有影响，因为如果这个顺序有影响，Oracle应该早就能够做到自动优化，但一直没有关于这方面的确凿证据。在网上查到的文章，一般认为在RBO优化器模式下无影响（10G开始，缺省为RBO优化器模式），而在CBO优化器模式下有影响，主要有两种观点：

    a.能使结果最少的条件放在最右边，SQL执行是按从右到左进行结果集的筛选的；

    b.有人试验表明，能使结果最少的条件放在最左边，SQL性能更高。

    查过oracle8到11G的在线文档，关于SQL优化相关章节，没有任何文档说过where子句中的条件对SQL性能有影响，到底哪种观点是对的，没有一种确切的结论，只好自己来做实验证明。结果表明，SQL条件的执行是从右到左的，但条件的顺序对SQL性能没有影响。

    实验一：证明了SQL的语法分析是从右到左的

    下面的试验在9i和10G都可以得到相同的结果: 第1条语句执行不会出错，第2条语句会提示除数不能为零。

    1.Select 'ok' From Dual Where 1 / 0 = 1 And 1 = 2;

    2.Select 'ok' From Dual Where 1 = 2 And 1 / 0 = 1;

    证明了SQL的语法分析是从右到左的。

    实验二：证明了SQL条件的执行是从右到左的

    drop table temp;

    create table temp( t1 varchar2(10),t2 varchar2(10));

    insert into temp values('zm','abcde');

    insert into temp values('sz','1');

    insert into temp values('sz','2');

    commit;

    1. select * from temp where to_number(t2)>1 and t1='sz';

    2. select * from temp where t1='sz' and to_number(t2)>1;

    在9i上执行, 第1条语句执行不会出错，第2条语句会提示“无效的数字”

    在10G上执行，两条语句都不会出错。

    说明：9i上，SQL条件的执行确实是从右到左的，但是10G做了什么调整呢？

    实验三：证明了在10g上SQL条件的执行是从右到左的

    Create Or Replace Function F1(v_In Varchar2) Return Varchar2 Is

    Begin

    Dbms_Output.Put_Line('exec F1');

    Return v_In;

    End F1;

    /

    Create Or Replace Function F2(v_In Varchar2) Return Varchar2 Is

    Begin

    Dbms_Output.Put_Line('exec F2');

    Return v_In;

    End F2;

    /

    SQL> set serverout on;

    SQL> select 1 from dual where f1('1')='1' and f2('1')='1';

    1

  ----------

    1

    exec F2

    exec F1

    SQL> select 1 from dual where f2('1')='1' and f1('1')='1';

    1

  ----------

    1

    exec F1

    exec F2

    结果表明，SQL条件的执行顺序是从右到左的。

    那么，根据这个结果来分析，把能使结果最少的条件放在最右边，是否会减少其它条件执行时所用的记录数量，从而提高性能呢？

    例如：下面的SQL条件,是否应该调整SQL条件的顺序呢？

    Where A.结帐id Is Not Null

    And A.记录状态<>0

    And A.记帐费用=1

    And (Nvl(A.实收金额, 0)<>Nvl(A.结帐金额, 0) Or Nvl(A.结帐金额, 0)=0)

    And A.病人ID=[1] And Instr([2],','||Nvl(A.主页ID,0)||',')>0

    And A.登记时间Between [3] And [4]

    And A.门诊标志<>1

    实际上，从这条SQL语句的执行计划来分析，Oracle首先会找出条件中使用索引或表间连接的条件，以此来过滤数据集，然后对这些结果数据块所涉及的记录逐一检查是否符合所有条件，所以条件顺序对性能几乎没有影响。

    如果没有索引和表间连接的情况，条件的顺序是否对性能有影响呢？再来看一个实验。

    实验四：证明了条件的顺序对性能没有影响。

    SQL> select count(*) from诊疗项目目录where操作类型='1';

    COUNT(*)

  ----------

    3251

    SQL> select count(*) from诊疗项目目录where类别='Z';

    COUNT(*)

  ----------

    170

    SQL> select count(*) from诊疗项目目录where类别='Z' and操作类型='1';

    COUNT(*)

  ----------

    1

    Declare

    V1 Varchar2(20);

    Begin

    For I In 1 .. 1000 Loop

    --Select名称Into V1 From诊疗项目目录Where类别= 'Z' And操作类型= '1';

    select名称Into V1 from诊疗项目目录where操作类型='1' and类别='Z';

    End Loop;

    End;

    /

    上面的SQL按两种方式分别执行了1000次查询，结果如下：

    操作类型= '1'在最右|类别='Z'在最右

    0.093                            |      1.014

    1.06                              |      0.999

    0.998                            |      1.014

    按理说，从右到左的顺序执行，“类别='Z'”在最右边时，先过滤得到170条记录，再从中找符合“操作类型 = '1'”的，比较而言，“操作类型 = '1'”在最右边时，先过滤得到3251条记录，再从中找符合“类别='Z'”，效率应该要低些，而实际结果却是两者所共的时间差不多。

    其实，从Oracle的数据访问原理来分析，两种顺序的写法，执行计划都是一样的，都是全表扫描，都要依次访问该表的所有数据块，对每一个数据块中的行，逐一检查是否同时符合两个条件。所以，就不存在先过滤出多少条数据的问题。

    综上所述，Where子句中条件的顺序对性能没有影响（不管是CBO还是RBO优化器模式），注意，额外说一下，这里只是说条件的顺序，不包含表的顺序。在RBO优化器模式下，表应按结果记录数从大到小的顺序从左到右来排列，因为表间连接时，最右边的表会被放到嵌套循环的最外层。最外层的循环次数越少，效率越高

oracle连接语句
conn 用户名/密码@数据库名称 as 身份(如;DBA)
conn sys/123456@sunlight as sysdba

修改优化器类型
1:alter system set optimizer_mode=(ALL_ROWS or FIRST_ROWS)系统级别的修改，永久改变;
2:alter session set optimizer_mode=(ALL_ROWS or FIRST_ROWS)会话级别的修改，会话关闭后修改不起作用，返回到原来默认状态

建立(本地索引实际上就是分区索引，即为每个分区建立一个索引)
create index index_cphid1 on sb1(cphid) tablespace sb1space local;
create bitmap index index_jcdid1 on sb1(jcdid) tablespace sb1space local;
create bitmap index index_cplx1 on sb1(cplx) tablespace sb1space local;
（或者
create index index_cphid1
on sb1(cphid)
local
(
partition part0 tablespace sb1space,
partition part1 tablespace sb1space,
partition part2 tablespace sb1space,
partition part3 tablespace sb1space,
partition part4 tablespace sb1space,
partition part5 tablespace sb1space
);
create bitmap index index_jcdid1
on sb1(jcdid)
local
(
partition part0 tablespace sb1space,
partition part1 tablespace sb1space,
partition part2 tablespace sb1space,
partition part3 tablespace sb1space,
partition part4 tablespace sb1space,
partition part5 tablespace sb1space
);

create bitmap index index_cplx1
on sb1(cplx)
local
(
partition part0 tablespace sb1space,
partition part1 tablespace sb1space,
partition part2 tablespace sb1space,
partition part3 tablespace sb1space,
partition part4 tablespace sb1space,
partition part5 tablespace sb1space
);
）
查看索引类型(局部的还是全局的)
SELECT index_name,table_name,locality FROM user_part_indexes

测试语句
select partition_name, num_rows
  from user_tab_partitions
  where table_name='SB31'
  order by partition_name;
测试语句
select count(1) from sb15
where sbsj between to_date('2008-01-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
and to_date('2008-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
and cphid like '粤B%'
and jcdid='10100204'
and cplx='1'

分析统计信息1

exec dbms_stats.gather_table_stats(ownname =>'TRAFFIC',tabname =>'SB1',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade =>true);

分析统计信息2
analyze table sb1 compute statistics;
analyze table sb2 compute statistics;
analyze table sb3 compute statistics;
analyze table sb4 compute statistics;
analyze table sb5 compute statistics;
analyze table sb6 compute statistics;
analyze table sb7 compute statistics;
analyze table sb8 compute statistics;
analyze table sb9 compute statistics;
analyze table sb10 compute statistics;
analyze table sb11 compute statistics;
analyze table sb12 compute statistics;
analyze table sb13 compute statistics;
analyze table sb14 compute statistics;
analyze table sb15 compute statistics;
analyze table sb16 compute statistics;
analyze table sb17 compute statistics;
analyze table sb18 compute statistics;
analyze table sb19 compute statistics;
analyze table sb20 compute statistics;
analyze table sb21 compute statistics;
analyze table sb22 compute statistics;
analyze table sb23 compute statistics;
analyze table sb24 compute statistics;
analyze table sb25 compute statistics;
analyze table sb26 compute statistics;
analyze table sb27 compute statistics;
analyze table sb28 compute statistics;
analyze table sb29 compute statistics;
analyze table sb30 compute statistics;
analyze table sb31 compute statistics;


建立(本地索引实际上就是分区索引，即为每个分区建立一个索引)
create index index_cphid1 on sb1(cphid) tablespace sb1space local;
create bitmap index index_jcdid1 on sb1(jcdid) tablespace sb1space local;
create bitmap index index_cplx1 on sb1(cplx) tablespace sb1space local;



select partition_name, num_rows
  from user_tab_partitions
  where table_name='SB31'
  order by partition_name;
测试语句
select count(1) from sb15
where sbsj between to_date('2008-01-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
and to_date('2008-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
and cphid like '粤B%'
and jcdid='10100204'
and cplx='1'

分析统计信息1

exec dbms_stats.gather_table_stats(ownname =>'TRAFFIC',tabname =>'SB1',estimate_percent =>NULL,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade =>true);
删除统计信息
begin dbms_stats.delete_table_stats(ownname=>'TRAFFIC',tabname=>'SB31');
end;
/
分析统计信息2
analyze table sb1 compute statistics;
analyze table sb2 compute statistics;
analyze table sb3 compute statistics;
analyze table sb4 compute statistics;
analyze table sb5 compute statistics;
analyze table sb6 compute statistics;
analyze table sb7 compute statistics;
analyze table sb8 compute statistics;
analyze table sb9 compute statistics;
analyze table sb10 compute statistics;
analyze table sb11 compute statistics;
analyze table sb12 compute statistics;
analyze table sb13 compute statistics;
analyze table sb14 compute statistics;
analyze table sb15 compute statistics;
analyze table sb16 compute statistics;
analyze table sb17 compute statistics;
analyze table sb18 compute statistics;
analyze table sb19 compute statistics;
analyze table sb20 compute statistics;
analyze table sb21 compute statistics;
analyze table sb22 compute statistics;
analyze table sb23 compute statistics;
analyze table sb24 compute statistics;
analyze table sb25 compute statistics;
analyze table sb26 compute statistics;
analyze table sb27 compute statistics;
analyze table sb28 compute statistics;
analyze table sb29 compute statistics;
analyze table sb30 compute statistics;
analyze table sb31 compute statistics;

查看列的直方图信息
select * from dba_tab_histograms where owner='TRAFFIC' and table_name like 'SB%' and column_name='CPLX';
select * from user_tab_columns where table_name like 'SB%' ;

alter system set PARALLEL_AUTOMATIC_TUNING=FALSE scope=spfile;
alter system set db_file_multiblock_read_count=128;
这个参数的意思是，在Table scan中，一次连续读（sequential read）能获取的最大块数。
当然这个值，不能超过操作系统和硬件的I/O极限。如果超过了，Oracle则会使用实际最大值。






车牌号在前
SELECT COUNT(1) FROM SB2 PARTITION(PART2)
WHERE 1=1
AND INSTR(CPHID,'粤B')>0
AND CPLX='0'
AND JCDID='10101004'
AND SBSJ BETWEEN TO_DATE('2008-01-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
AND TO_DATE('2008-06-30 00:00:00','yyyy-MM-dd HH24:mi:ss');

(
  COUNT(1)
----------
     15310

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2956 Card=1 Bytes=
          29)

   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (SINGLE) (Cost=2956 Card=833 Bytes=24157
          )

   3    2       TABLE ACCESS (FULL) OF 'SB2' (TABLE) (Cost=2956 Card=8
          33 Bytes=24157)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
)

车辆类型在前
SELECT COUNT(1) FROM SB2 PARTITION(PART2)
WHERE 1=1
AND CPLX='0'
AND JCDID='10101004'
AND INSTR(CPHID,'粤B')>0
AND SBSJ BETWEEN TO_DATE('2008-01-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
AND TO_DATE('2008-06-30 00:00:00','yyyy-MM-dd HH24:mi:ss');

(

  COUNT(1)
----------
     15310

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2956 Card=1 Bytes=
          29)

   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (SINGLE) (Cost=2956 Card=833 Bytes=24157
          )

   3    2       TABLE ACCESS (FULL) OF 'SB2' (TABLE) (Cost=2956 Card=8
          33 Bytes=24157)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
)

监测点在前
SELECT COUNT(1) FROM SB2 PARTITION(PART2)
WHERE 1=1
AND JCDID='10101004'
AND CPLX='0'
AND INSTR(CPHID,'粤B')>0
AND SBSJ BETWEEN TO_DATE('2008-01-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
AND TO_DATE('2008-06-30 00:00:00','yyyy-MM-dd HH24:mi:ss');
(
  COUNT(1)
----------
     15310

Elapsed: 00:00:00.32

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2956 Card=1 Bytes=
          29)

   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (SINGLE) (Cost=2956 Card=833 Bytes=24157
          )

   3    2       TABLE ACCESS (FULL) OF 'SB2' (TABLE) (Cost=2956 Card=8
          33 Bytes=24157)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
)

oracel 表空间语句

{\rtf1\ansi\ansicpg936\deff0{\fonttbl{\f0\fnil\fcharset134 \'cb\'ce\'cc\'e5;}{\f1\fmodern Courier New;}{\f2\fnil\fcharset0 ??;}}
{\colortbl ;\red128\green0\blue128;\red0\green128\blue128;\red255\green255\blue255;\red0\green0\blue128;\red0\green0\blue255;}
{\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\lang2052\f0\fs20\'b7\'d6\'b1\'f0\'d4\'dajcdid, cplx\'c1\'d0\'c9\'cf\'bd\'a8\'c1\'a2\'ce\'bb\'cd\'bc\'cb\'f7\'d2\'fd\'a3\'ac\'d4\'dacphid\'c9\'cf\'bd\'a8\'c1\'a2B*-\'cb\'f7\'d2\'fd\'a3\'actpid1\'ce\'aa\'d6\'f7\'bc\'fc\'a3\'bb\par
\'c9\'be\'b3\'fd\'b1\'ed\'bf\'d5\'bc\'e4\par
drop tablespace sb6space including contents and datafiles\par
\'bd\'a8\'c1\'a2\'b1\'ed\'bf\'d5\'bc\'e4\par
create TABLESPACE sb1space\par
logging\par
DATAFILE '/data/oracle/sunlight/sb1space.dbf' size 4500M\par
EXTENT MANAGEMENT LOCAL\par
SEGMENT SPACE MANAGEMENT\par
auto\par
\cf1\'bd\'a8\'b1\'ed\'d3\'ef\'be\'e4\par
\cf0 create table SB1\par
(\par
  CPHID  VARCHAR2(12) not null,\par
  JCDID  VARCHAR2(8) not null,\par
  CDID   VARCHAR2(5),\par
  SBSJ   DATE,\par
  CPLX   VARCHAR2(2),\par
  TPID1  VARCHAR2(34) not null,\par
  FQH    NUMBER(1) not null,\par
  CB     VARCHAR2(3),\par
  CLLX   VARCHAR2(2),\par
  TPID2  VARCHAR2(34),\par
  TXTP   VARCHAR2(34),\par
  SFWC   VARCHAR2(1),\par
  FXM    VARCHAR2(1),\par
  FBCD   NUMBER(12,3),\par
  TXTP2  VARCHAR2(34),\par
  TXTP3  VARCHAR2(34),\par
  HOMDDJ VARCHAR2(16),\par
  CPHID2 VARCHAR2(12),\par
  CPLX2  VARCHAR2(2),\par
  HPSFWC VARCHAR2(1),\par
  QHSFYZ VARCHAR2(1),\par
  BCBZ   VARCHAR2(2)\par
)\par
\cf1\'b7\'d6\'c7\'f8\'d3\'ef\'be\'e4\par
\cf0 partition by list (FQH)\par
\cf1 (\par
partition part0 \cf0 values (0) \cf1 tablespace sb1space,\par
partition part1 \cf0 values (1) \cf1 tablespace sb1space,\par
partition part2 \cf0 values (2) \cf1 tablespace sb1space,\par
partition part3 \cf0 values (3) \cf1 tablespace sb1space,\par
partition part4 \cf0 values (4) \cf1 tablespace sb1space,\par
partition part5 \cf0 values (5) \cf1 tablespace sb1space\par
);\par
alter table SB1\par
  add constraint PK_SB1 primary key (TPID1)\par
  using index \par
  tablespace USERS\par
  pctfree 10\par
  initrans 2\par
  maxtrans 255\par
  storage\par
  (\par
    initial 64K\par
    minextents 1\par
    maxextents unlimited\par
  );\par
\cf0\'b2\'e9\'d1\'af\'cc\'d8\'b6\'a8\'b7\'d6\'c7\'f8\'ca\'fd\'be\'dd\par
select * from sb1 partition (part3)\par
\cf1\b\fs36\'bd\'a8\'c1\'a2B\'ca\'f7\'cb\'f7\'d2\'fd(\'b1\'be\'b5\'d8\'ca\'b5\'bc\'ca\'c9\'cf\'be\'cd\'ca\'c7\'b7\'d6\'c7\'f8\'cb\'f7\'d2\'fd\'a3\'ac\'bc\'b4\'ce\'aa\'c3\'bf\'b8\'f6\'b7\'d6\'c7\'f8\'bd\'a8\'c1\'a2\'d2\'bb\'b8\'f6\'cb\'f7\'d2\'fd)\par
create index index_cphid1 on sb1(cphid) tablespace sb1space local;\par
\fs28\'bd\'a8\'c1\'a2\'ce\'bb\'cd\'bc\'cb\'f7\'d2\'fd\par
create bitmap index index_jcdid1 on sb1(jcdid) tablespace sb1space local;\par
create bitmap index index_cplx1 on sb1(cplx) tablespace sb1space local;\b0\fs20\par
\b\fs36\'b7\'d6\'c7\'f8\'cb\'f7\'d2\'fd\par
\b0\fs28\'b7\'d6\'c7\'f8\'cb\'f7\'d2\'fd\'be\'cd\'ca\'c7\'d4\'da\'cb\'f9\'d3\'d0\'c3\'bf\'b8\'f6\'c7\'f8\'c9\'cf\'b5\'a5\'b6\'c0\'b4\'b4\'bd\'a8\'cb\'f7\'d2\'fd\'a3\'ac\'cb\'fc\'c4\'dc\'d7\'d4\'b6\'af\'ce\'ac\'bb\'a4\'a3\'ac\'d4\'dadrop\'bb\'f2truncate\'c4\'b3\'b8\'f6\'b7\'d6\'c7\'f8\'ca\'b1\'b2\'bb\'d3\'b0\'cf\'ec\'b8\'c3\'cb\'f7\'d2\'fd\'b5\'c4\'c6\'e4\'cb\'fb\'b7\'d6\'c7\'f8\'cb\'f7\'d2\'fd\'b5\'c4\'ca\'b9\'d3\'c3\'a3\'ac\'d2\'b2\'be\'cd\'ca\'c7\'cb\'f7\'d2\'fd\'b2\'bb\'bb\'e1\'ca\'a7\'d0\'a7\'a3\'ac\'ce\'ac\'bb\'a4\'c6\'f0\'c0\'b4\'b1\'c8\'bd\'cf\'b7\'bd\'b1\'e3\'a3\'ac\'b5\'ab\'ca\'c7\'d4\'da\'b2\'e9\'d1\'af\'d0\'d4\'c4\'dc\'c9\'d4\'ce\'a2\'d3\'d0\'b5\'e3\'d3\'b0\'cf\'ec\'a1\'a3\par
\b\fs36 CREATE INDEX index_cphid ON sb1(cphid) \par
LOCAL \par
(\par
PARTITION part1 tablespace sb1space,\par
PARTITION part2 tablespace sb1space,\par
PARTITION part3 tablespace sb1space,\par
PARTITION part4 tablespace sb1space,\par
PARTITION part5 tablespace sb1space,\par
PARTITION part6 tablespace sb1space\par
);  \par
\cf0\'b2\'e9\'bf\'b4\'cb\'f7\'d2\'fd\'b5\'c4\'b7\'d6\'c7\'f8\'c7\'e9\'bf\'f6\par
\cf2\highlight3\lang1033\b0\f1\fs20 select\cf4  \cf2 INDEX_NAME\cf4 , \cf2 PARTITION_NAME\cf4  \f2 ,\f1 tablespace_name\f2  status \cf2\f1 from\cf4  \cf2 dba_ind_partitions\cf4  \cf2 where\cf4  \cf2 index_name\cf4 =\cf2 upper\cf4 (\cf5 'index_cphid1'\cf4 );\par
\cf0\highlight0\lang2052\f0\par
\'cf\'d4\'ca\'be\'b2\'e9\'d1\'af\'bc\'c6\'bb\'ae\par
\tab explain plan for select * from sb1 where cphid like '\'d4\'c1B%'\par
\tab\'c9\'cf\'d2\'bb\'b2\'bd\'d6\'b4\'d0\'d0\'cd\'ea\'b3\'c9\'ba\'f3\par
\tab select * from table(dbms_xplan.display())\par
\'bf\'e7\'b1\'ed\'bf\'e7\'b7\'d6\'c7\'f8\'b2\'e9\'d1\'af\par
select cphid from sb1 partition(part1)\par
union all\par
select cphid from sb1 partition(part2)\par
union all\par
select cphid from sb2 partition(part1)\par
union all\par
select cphid from sb2 partition(part2)\par
\par
\'b7\'d6\'ce\'f6\'b2\'a2\'b2\'e9\'bf\'b4\'ca\'fd\'be\'dd\'c8\'e7\'ba\'ce\'d4\'da\'b2\'bb\'cd\'ac\'b5\'c4\'b7\'d6\'c7\'f8\'b7\'d6\'b2\'bc\par
select partition_name, num_rows\par
  from user_tab_partitions\par
  where table_name = 'SB1'\par
  order by partition_name;\par
}


oracel  to _data

TO_DATE格式(以时间:2007-11-02   13:45:25为例)
  
        Year:     
        yy two digits 两位年                显示值:07
        yyy three digits 三位年                显示值:007
        yyyy four digits 四位年                显示值:2007
           
        Month:     
        mm    number     两位月              显示值:11
        mon    abbreviated 字符集表示          显示值:11月,若是英文版,显示nov    
        month spelled out 字符集表示          显示值:11月,若是英文版,显示november
         
        Day:     
        dd    number         当月第几天        显示值:02
        ddd    number         当年第几天        显示值:02
        dy    abbreviated 当周第几天简写    显示值:星期五,若是英文版,显示fri
        day    spelled out   当周第几天全写    显示值:星期五,若是英文版,显示friday       
        ddspth spelled out, ordinal twelfth
            
              Hour:
              hh    two digits 12小时进制            显示值:01
              hh24 two digits 24小时进制            显示值:13
             
              Minute:
              mi    two digits 60进制                显示值:45
             
              Second:
              ss    two digits 60进制                显示值:25
             
              其它
              Q     digit         季度                  显示值:4
              WW    digit         当年第几周            显示值:44
              W    digit          当月第几周            显示值:1
             
        24小时格式下时间范围为： 0:00:00 - 23:59:59....     
        12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
           
1. 日期和字符转换函数用法（to_date,to_char）
        
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串  
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年  
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月  
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日  
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时  
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分  
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒
   
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//

2.     
    select to_char( to_date(222,'J'),'Jsp') from dual     
   
    显示Two Hundred Twenty-Two    

3.求某天是星期几     
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;     
   星期一     
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;     
   monday     
   设置日期语言     
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';     
   也可以这样     
   TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')    

4. 两个日期间的天数     
    select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;    

5. 时间为null的用法     
   select id, active_date from table1     
   UNION     
   select 1, TO_DATE(null) from dual;     
  
   注意要用TO_DATE(null)    

6.月份差  
   a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')     
   那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。     
   所以，当时间需要精确的时候，觉得to_char还是必要的
     
7. 日期格式冲突问题     
    输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'     
    alter system set NLS_DATE_LANGUAGE = American     
    alter session set NLS_DATE_LANGUAGE = American     
    或者在to_date中写     
    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;     
    注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，     
    可查看     
    select * from nls_session_parameters     
    select * from V$NLS_PARAMETERS    

8.     
   select count(*)     
   from ( select rownum-1 rnum     
       from all_objects     
       where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-     
       02-01','yyyy-mm-dd')+1     
      )     
   where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )     
        not in ( '1', '7' )     
  
   查找2002-02-28至2002-02-01间除星期一和七的天数     
   在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).    

9. 查找月份    
    select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;     
    1     
   select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;     
    1.03225806451613
      
10. Next_day的用法     
    Next_day(date, day)     
   
    Monday-Sunday, for format code DAY     
    Mon-Sun, for format code DY     
    1-7, for format code D    

11     
   select to_char(sysdate,'hh:mi:ss') TIME from all_objects     
   注意：第一条记录的TIME 与最后一行是一样的     
   可以建立一个函数来处理这个问题     
   create or replace function sys_date return date is     
   begin     
   return sysdate;     
   end;     
  
   select to_char(sys_date,'hh:mi:ss') from all_objects;  
    
12.获得小时数     
     extract()找出日期或间隔值的字段值
    SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer     
    SQL> select sysdate ,to_char(sysdate,'hh') from dual;     
   
    SYSDATE TO_CHAR(SYSDATE,'HH')     
    -------------------- ---------------------     
    2003-10-13 19:35:21 07     
   
    SQL> select sysdate ,to_char(sysdate,'hh24') from dual;     
   
    SYSDATE TO_CHAR(SYSDATE,'HH24')     
    -------------------- -----------------------     
    2003-10-13 19:35:21 19    

      
13.年月日的处理     
   select older_date,     
       newer_date,     
       years,     
       months,     
       abs(     
        trunc(     
         newer_date-     
         add_months( older_date,years*12+months )     
        )     
       ) days
      
   from ( select     
        trunc(months_between( newer_date, older_date )/12) YEARS,     
        mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,     
        newer_date,     
        older_date     
        from (
              select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date     
              from emp
             )     
      )    

14.处理月份天数不定的办法     
   select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual    

16.找出今年的天数     
   select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual    

   闰年的处理方法     
   to_char( last_day( to_date('02'    | | :year,'mmyyyy') ), 'dd' )     
   如果是28就不是闰年    

17.yyyy与rrrr的区别     
   'YYYY99 TO_C     
   ------- ----     
   yyyy 99 0099     
   rrrr 99 1999     
   yyyy 01 0001     
   rrrr 01 2001    

18.不同时区的处理     
   select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate     
   from dual;    

19.5秒钟一个间隔     
   Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')     
   from dual    

   2002-11-1 9:55:00 35786     
   SSSSS表示5位秒数    

20.一年的第几天     
   select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
       
   310 2002-11-6 10:03:51    

21.计算小时,分,秒,毫秒     
    select     
     Days,     
     A,     
     TRUNC(A*24) Hours,     
     TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,     
     TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,     
     TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds     
    from     
    (     
     select     
     trunc(sysdate) Days,     
     sysdate - trunc(sysdate) A     
     from dual     
   )    


   select * from tabname     
   order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');     
  
   //     
   floor((date2-date1) /365) 作为年     
   floor((date2-date1, 365) /30) 作为月     
   d(mod(date2-date1, 365), 30)作为日.

23.next_day函数      返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
   next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。     
   1 2 3 4 5 6 7     
   日 一 二 三 四 五 六   
  
   ---------------------------------------------------------------
  
   select    (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
   日期 返回的是天 然后 转换为ss
    
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
   select sysdate S1,
   round(sysdate) S2 ,
   round(sysdate,'year') YEAR,
   round(sysdate,'month') MONTH ,
   round(sysdate,'day') DAY from dual

25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
   select sysdate S1,                    
     trunc(sysdate) S2,                 //返回当前日期,无时分秒
     trunc(sysdate,'year') YEAR,        //返回当前年的1月1日,无时分秒
     trunc(sysdate,'month') MONTH ,     //返回当前月的1日,无时分秒
     trunc(sysdate,'day') DAY           //返回当前星期的星期天,无时分秒
   from dual

26,返回日期列表中最晚日期
   select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

27.计算时间差
     注:oracle时间差是以天数为单位,所以换算成年月,日
    
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        //时间差-年
      select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        //时间差-月
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual             //时间差-天
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual         //时间差-时
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    //时间差-分
      select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒

28.更新时间
     注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual        //改变时间-年
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual                                 //改变时间-月
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual            //改变时间-日
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual         //改变时间-时
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual      //改变时间-分
     select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual   //改变时间-秒

29.查找月的第一天,最后一天
     SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
       LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
   FROM dual;


10G ,用户

create user test
  identified by "test"
  default tablespace test
  temporary tablespace TEMP
  profile DEFAULT;

grant connect to test;
grant dba to test;
grant resource to test;

grant unlimited tablespace to test;




创建用户以及权限

create user TRAFFIC
  identified by ""
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to TRAFFIC;
grant dba to TRAFFIC;
-- Grant/Revoke system privileges
grant create session to TRAFFIC;
grant unlimited tablespace to TRAFFIC;



存储过程



create or replace procedure exe_cal_speed as
    jcdid1 varchar2(20);
    jcdid2 varchar2(20);
    jl number;
    cssjyz number;
    tpsjyz number;
    fx varchar2(2);
    cursor csyz is   --定义游标
      select jcdid1,jcdid2,jl,cssjyz,tpsjyz,fx from tpyz where cssjyz > '0' order by jcdid1,jcdid2;
begin
    open csyz;  --打开游标

    loop
      fetch csyz into jcdid1,jcdid2,jl,cssjyz,tpsjyz,fx;
      exit when csyz%notfound;  --退出条件
      if fx = '0' then    --正方向
        begin
          cal_speed(jcdid1,jcdid2,jl,cssjyz,tpsjyz);
        end;
      else           --反方向
        begin
          cal_speed(jcdid2,jcdid1,jl,cssjyz,tpsjyz);
        end;
      end if;
    end loop;
    close csyz;

    --去除重复记录
    delete from tpcsjz where rowid in
      (select a.rowid from tpcsjz a, tpcsjz b where a.cphid = b.cphid
        and a.sbsj1 = b.sbsj1 and a.jcdid1 = b.jcdid1 and a.jcdid2 = b.jcdid2
        and a.sbjlid1 = b.sbjlid1 and a.sbjlid2 = b.sbjlid2 and a.rowid > b.rowid);

    commit;

    --删除未识别数据
    delete from traffic.tpcsjz where cphid like '%XXXX%' or cphid like '%xxxx%';
    commit;
    --删除监测点位置颠倒的数据
    delete from traffic.tpcsjz where sbsj1 > sbsj2;
    commit;
end;


create or replace procedure cal_speed(jcdid1 in varchar2, jcdid2 in varchar2,jl in integer, yz in integer,tpyz in integer) as
    month number;
    day   number;
    stabname varchar2(30);
    sSQLExecString varchar2(5000);
    CURSOR_HANDLE INTEGER;
    lrlx varchar2(1);
    CNT  number;
begin
    --取前一天的月份及日，用于识别表名及分区
    select to_number(to_char(sysdate-1,'MM')) into month from dual;
    select to_number(to_char(sysdate-1,'DD')) into day from dual;

    stabname := 'traffic.sb'||to_char((day));

    lrlx := 'a';

    sSQLExecString := 'insert into tpcsjz
(id,cphid,cplx,jcdid1,cdid1,sbsj1,sbjlid1,jcdid2,cdid2,sbsj2,sbjlid2,lrlx,lxsj,cssd)';
    sSQLExecString := sSQLExecString||'(select traffic.s_tpcsjz.nextval,
a.cphid,a.cplx,a.jcdid,a.cdid,a.sbsj,a.tpid1,b.jcdid,b.cdid,b.sbsj,b.tpid1,'||''''||lrlx||''''||',';  --两个单引号转义为：'
    sSQLExecString := sSQLExecString||'MOD(TRUNC((abs(b.sbsj-a.sbsj)*24*3600)/(60*60),0),24)||'':''||MOD(TRUNC((abs(b.sbsj -a.sbsj)*24*3600)/60,0),60)||'':''||MOD(TRUNC((abs(b.sbsj -a.sbsj)*24*3600),0),60)'||','||jl||'/(abs(b.sbsj -a.sbsj )*24*3600)*3.6 from ';
    sSQLExecString := sSQLExecString||'(select sbsj,jcdid,cphid,cdid,cplx,tpid1  from '||stabname||' where sfwc='||'''1'''||' and jcdid='||''''||jcdid1||'''';
    sSQLExecString := sSQLExecString||' and to_number(to_char(sbsj,''DD''))='||day||') a ,';
    sSQLExecString := sSQLExecString||'(select sbsj,jcdid,cphid,cdid,cplx,tpid1  from '||stabname||' where sfwc='||'''1'''||' and jcdid='||''''||jcdid2||'''';
    sSQLExecString := sSQLExecString||' and to_number(to_char(sbsj,''DD''))='||day||') b ';
    sSQLExecString := sSQLExecString||' where a.cphid =b.cphid and a.cplx =b.cplx and (b.sbsj -a.sbsj )*24*3600 < '||yz||' and (b.sbsj-a.sbsj)*24*3600 > '||tpyz||')';

    CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(CURSOR_HANDLE, sSQLExecString, DBMS_SQL.NATIVE);
    CNT:= DBMS_SQL.EXECUTE(CURSOR_HANDLE);
    DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);

    --提交
    commit;
end;

按天导出 TXT 数据

set heading off
set feedback off
set pagesize 0
set trims on
set termout off
set echo off
set trimspool on
spool F:/newdata/20110701.txt
select a.cphid||','||a.jcdid||','||a.cdid||','||to_char(a.sbsj,'yyyy-mm-dd hh24:mi:ss')||','||a.cplx||','||a.tpid1||','||a.sfwc||','||a.bdbz from sb7 a where to_char(sbsj,'yyyy-MM-dd')='2011-07-01';
spool off
set trimspool off
set heading on
set feedback on
set term on
set echo on



SET echo off
　　SET feedback off
　　SET newpage none
　　SET linesize 2000
　　SET pagesize 0
　　SET heading  off
SET trimspool on
　　SET trimout ON
　　SET timing off
　　SET verify off
　　SET colsep |
　　spool  e:\2012-06-01.txt
SELECT cphid || '#' || jcdid || '#' || cdid || '#' || to_char(sbsj,’yyyy-MM-dd HH24:mi:ss’) || '#' || cplx|| '#' || tpid1|| '#' || fqh || '#' || cb || '#' || cllx || '#' || tpid2 || '#' || txtp || '#' || sfwc FROM sb1 WHERE to_char(sbsj,’yyyy-MM-dd’)=’2012-06-01’;
spool off





conn traffic/traffic@sunlight
set heading off
set feedback off
set pagesize 0
set trims on
set termout off
set echo off
set trimspool on
spool d:/t.txt
select a.id||', '||a.ajmc||', '||a.ajlx||', '||to_char(a.jlsj,'yyyy-mm-dd hh24:mi:ss')||', '||a.ajnr  from ajgl a;
spool off
set trimspool off
set heading on
set feedback on
set term on
set echo on



序列
prompt
prompt Creating sequence S_BKSQ
prompt ===========================
prompt
create sequence TRAFFIC.S_BKSQ
minvalue 0
maxvalue 999999
start with 10000
increment by 1
cache 20;

create sequence S_TZDXLB
minvalue 1
maxvalue 9999999999999999999
start with 5000
increment by 1
cache 20;





select s_bksq.nextval from dual


Tom Cat  虚拟路径


<Context path="/epolice" docBase="F:/picture" debug="0" reloadable="true"/>


数据库 DB link

Create database link
create public database link DLWHJJ.REGRESS.RDBMS.DEV.US.ORACLE.COM
  connect to SA
  using 'whjj';



# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SUNLIGHT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sunlight)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sunlight)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

whjj =
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=10.103.13.195)
         (PORT=1521)
      )
      (CONNECT_DATA=
         (SID=whjj))
      (HS=OK) #.....
    )



触发器 ----

CREATE OR REPLACE TRIGGER Spd_AlarmMsg_WHJJ
AFTER INSERT ON tpcsjz     --仅在插入套牌超速记载表数据时触发t_syssendinfo@dlwhjj
FOR EACH ROW               --必须加这句，对每条记录除法，后面才能用":new"引用
when (new.lrlx='a')
DECLARE
    PRAGMA autonomous_transaction; --由于使用DBLINK连接SQLSERVER数据库表(t_syssendinfo@dlwhjj)，所以必须使用自治事务才能处理DDL(commit or rollback)
    v_type alarmmsg_log.operatetype%TYPE;
    cnt int;
    telno varchar(20);
    info  varchar(500); --%s(时间)%s(车牌号)%s(车牌类型)在%s(起点)至%s(终点)路段超速行驶，平均速度为%s，路段限制速度为%s！
    startp varchar(100);--不能用"start","stop","desc"等关键字
    stopp  varchar(100);
    cartype varchar(10);
    cursor cur_ret is   --定义游标
      select telno from tel_list where type='1'; --超速通知类型的手机号码
BEGIN
    cnt := 0;
    open cur_ret;  --打开游标

    loop
      fetch cur_ret into telno;
      exit when cur_ret%notfound;  --退出条件
      --填充字段
      select jcdmc into startp from jcd where id=:new.jcdid1;
      select jcdmc into stopp from jcd where id=:new.jcdid2;
      select lbms into cartype from sjzd where lbdm='0001' and lbxh=:new.cplx;
      info := to_char(:new.sbsj2,'yyyy-mm-dd hh:mi:ss') || :new.cphid || cartype || '在' || startp || '至' || stopp  || '路段超速行驶，平均速度为' || :new.cssd || ',' || '路段限制速度为' || :new.xs || '!';
      insert into t_syssendinfo@dlwhjj ("d_vUserName","d_vMoveTel","d_vSendInfo","d_vFlag") values('卡口系统',telno,info,'');
      commit;  --使用自治事务后，必须commit
      cnt := cnt + 1;
    end loop;
    close cur_ret;
    if cnt > 0 then
        v_type := 'INSERT';
        INSERT INTO alarmmsg_log VALUES(s_alarmmsg_log.nextval,v_type,:new.id,TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));  --字符类型需加单引号
        commit;  --使用自治事务后，必须commit
    end if;
   
    exception  --捕获异常后回滚
      when others then
        rollback;
END;