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;
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;
分享到：
发表评论
相关推荐
以下是一些Oracle常用命令和SQL语句的详细说明： 1. **查询表结构**： - 使用`DESCRIBE`或` DESC`命令可以查看表的字段信息，例如：`DESC employee`。 2. **查询表内容**： - `SELECT * FROM table_name`用于...
资源名称：Oracle学习笔记-日常应用、深入管理、性能优化内容简介：Oracle学习笔记-日常应用、深入管理、性能优化Oracle 11g是最具代表性的高端关系型数据库管理系统，它在世界各地的大型商务数据库应用系统中被广泛...
以下是对Oracle学习笔记中提到的一些关键知识点的详细解释： 1. **SQL执行顺序**： SQL语句的执行顺序是：`FROM` -> `WHERE` -> `SELECT` -> `GROUP BY` -> `HAVING` -> `ORDER BY`。首先从`FROM`子句开始，确定...
这份"Oracle实用笔记 命令 文档 PDF"很可能是对Oracle数据库管理、查询和维护的一份详细指南，涵盖了各种关键的Oracle命令和操作技巧。下面我们将深入探讨其中可能涉及的一些重要知识点。 1. **Oracle SQL语法**：...
记录Oracle学习过程中的各种笔记,比较实用的总结.可以查询常用的Oracle信息
select语句是Oracle数据库中最常用的查询语句，用于查询数据。select语句可以使用where子句、order by子句、group by子句等来对查询结果进行限制和排序。 4. 条件语句： where子句是select语句中最重要的子句，用于...
以下是一些Oracle常用的知识点，包括启动和关闭数据库，以及如何有效地利用数据字典。 首先，Oracle数据库的启动和关闭是数据库管理的基础。在单机环境下，你可以通过切换到Oracle用户并使用`svrmgrl`工具来执行...
根据提供的文件内容，以下是对“李兴华--oracle课堂笔记(全)”所包含知识点的详细说明： 1. Oracle 数据库版本信息 文件内容中提到了多个Oracle数据库的版本号，如Oracle10g、Oracle8i、Oracle9i等。这些是Oracle ...
Oracle 11g是最具代表性的高端关系型数据库管理系统，它在世界各地的大型商务数据库应用系统中被广泛...本书设计了大量的应用情景，介绍了数据库管理员和开发人员常用的管理、维护和优化Oracle 11g数据库的技术和技巧。
【标题】：“(转)最全的SQL（Oracle）笔记集录” 这篇资源是一个全面的SQL，特别是针对Oracle数据库的学习笔记集合。Oracle是世界上最广泛使用的数据库管理系统之一，它提供了丰富的功能来处理各种类型的数据和满足...
笔记中提到了一些常用的SQLPlus命令，如“SELECT*FROMemp”，这是用来查询emp表中所有数据的SQL语句；“setlinesize”和“setpagesize”用来设置输出格式；“showuser”和“SELECT*FROMtab”则用于显示当前用户信息...
从提供的标题、描述以及部分文本内容来看，这份“Oracle学习笔记”主要围绕Oracle数据库中的查询语句及其各种类型进行深入探讨。尽管描述部分重复了标题的文字，但主要内容段落包含了丰富的信息，下面将对这些知识点...
本书作为一本创作之初就定位于技术应用的实践参考书，虽然前前后后串联了Oracle数据库中十余个常用特性或工具，但在章节的设计上完全遵循这一理念。在涉及特性或工具应用的每一个章节，均有相关实例演示如何应用，...
### Oracle常用命令详解 #### 一、导出与导入数据（Exp/Imp） 在Oracle数据库管理中，数据的备份和恢复是一项重要的工作。通常我们使用`exp`命令来进行数据的导出，而`imp`命令则用于数据的导入。下面我们将详细...
oracle学习笔记，涉及oracle常用函数，分析函数，sql语句。
### ORACLE经典学习笔记知识点概览 #### 第一章 ORACLE 命令 本章节主要介绍了Oracle数据库中常用的命令及其使用方法。 1. **查看参数文件**: `Desc v$parameter` - 这个命令用于查看Oracle的参数文件信息，通过...
### Oracle 常用函数及简单示例 Oracle 数据库提供了丰富的内置函数，这些函数能够帮助用户高效地处理数据、格式化输出等。本文将基于提供的部分内容介绍一些常用的 Oracle 函数及其应用示例。 #### 日期时间转换...
5. Java的应用类：进一步学习Java标准库中的常用类，如String类、集合类等，了解这些类的内部机制和最佳实践。 6. 设计模式：学习和应用常见的设计模式，如单例模式、工厂模式、策略模式等，能够提升软件设计的质量...