日期合法性校验函数

校验日期字符串，如果是合法的日期，则返回Date类型，否则返回null。

写法一：
--=========================================
--Usage：判断日期是否合法
--Param：字符串日期形式
--Return：
-- 如果日期合法，返回合法的Date型日期
-- 否则返回null值
--Auth：Jazz
--Date：2009-03-27
--=========================================
create or replace function CheckDate(date_in Varchar2) return Date is

begin
Return to_date(date_in,'yyyy-MM-DD');
exception
when others then
return (null);
end;


写法二：这种写法返回的是字符串，即传入什么返回什么，如果非法日期返回null。

create or replace function CheckDate(date_in Varchar2) return varchar2 is
chk_date varchar2(10);
begin
select to_date(date_in,'yyyy-mm-dd') Into chk_date from dual;
return(date_in);
exception
when others then
return (null);
end;

海量数据库优化问题

最近处理了接近300万条的测试记录，优化前根据测试，出报表的速度很慢，使用了ORACLE的Explain Plan分析，发现建立的索引失效，ORACLE索引失效是比较常见的问题。有的是数据本身的构成（比如索引列存在大量的重复数据）问题，有的是对索引列施加了运算（比如函数运算）引起的，有的是SQL语句的写法不当造成的。从网上搜索了不少关于优化的文章，解决的问题也多种多样，够学习一段时间了！当然，SQL语句的写法关系也很大，下面从SQL语句入手以一个例子来分析下：

环境：
OS:Windows XP sp-2
CPU:Intel(R) Core2 Duo CPU 2.0GHz
物理内存：2G
ORACLE 10g
PL/SQL 7.0

优化前执行大概需要60s时间：
Select AGE,PERSON,nvl(ROUND((RATIO_TO_REPORT(Sum(Person)) OVER ())*100,2),'') pct From(

Select age ,person
From(
Select '00--16' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) < 16 and district like :disCode Union All
Select '16--25' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 16 And Round(months_between(Sysdate,birthday)/12) < 25 and district like :disCode Union All--16-25
Select '26--35' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 25 And Round(months_between(Sysdate,birthday)/12) < 35 and district like :disCode Union All
Select '36--45' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 35 And Round(months_between(Sysdate,birthday)/12) < 45 and district like :disCode Union All
Select '46--55' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 45 Round(months_between(Sysdate,birthday)/12) < 55 and district like :disCode Union All
Select '55--++' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 55 and district like :disCode
)

)
Group By age,person
优化后大概需要1.57s时间：
Select AGE,PERSON,nvl(ROUND((RATIO_TO_REPORT(Sum(Person)) OVER ())*100,2),'') pct From(

Select age ,person
From(
Select '00--16' As age,Count(*) As person From cca1 Where birthday>add_months(Sysdate,-16*12) and district like :disCode Union All
Select '16--25' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-25*12) And birthday<add_months(Sysdate,-16*12) and district like :disCode Union All--16-25
Select '26--35' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-35*12) And birthday<add_months(Sysdate,-25*12) and district like :disCode Union All
Select '36--45' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-45*12) And birthday<add_months(Sysdate,-35*12) and district like :disCode Union All
Select '46--55' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-55*12) And birthday<add_months(Sysdate,-45*12) and district like :disCode Union All
Select '55--++' As age,Count(*) As person From cca1 Where birthday<add_months(Sysdate,-55*12) and district like :disCode
)

)
Group By age,person

因此，少对索引施加复杂运算可以利用到索引的优势。

此外，索引的like也很容易引起失效，比如 districtCode like '35________'不能利用索引失效，而使用 districtCode like '3501________'却又能使用上索引，根据推测这可能是因为在大量数据中，以第一种方式基本上实施全表搜索，优化器可能不使用索引。

ORACLE数据文件迁移与删除方法

近日由于硬盘空间紧张，需要将ORACLE数据文件移动位置，使用的版本是ORACLE 10g，先将过程做个记录。

1. alter tablespace tablespace_name offline;[表空间脱机]
2. 将数据文件移动到新的目录；
3. alter tablespace tablespace_name rename datafile 'D:\oracle\product\10.2.0\oradata\orcl\FILE.ORA' to 'E:\DataBase\oradata\orcl\FILE.ORA';[数据文件重命名]
4. alter tablespace tablespace_name online;[表空间联机]

如果需要将不再使用的数据文件删除，可以使用如下方法：
drop tablespace tablespace_name including contents and datafiles;

需要注意的是，在设计中，一般将数据表空间和索引表空间分离，所以，迁移或删除的时候要记得指定两个表空间的名字进行操作。