Oracle分区表例子

复制代码 代码如下:select rownum as
dateIndex,to_date(‘20090701′,’yyyy-mm-dd’)+ rownum-1 AS DATE_ FROM
DUAL CONNECT BY LEVEL = 1 AND LEVEL
=(TO_DATE(‘20090731′,’yyyy-mm-dd’)-TO_DATE(‘20090701′,’yyyy-mm-dd’)+1)
result: 1 2009/07/01 2 2009/07/02 3 2009/07/03 4 2009/07/04 5
2009/07/05 6 2009/07/06 7 2009/07/07 8 2009/07/08 9 2009/07/09 10
2009/07/10 11 2009/07/11 12 2009/07/12 13 2009/07/13 14 2009/07/14 15
2009/07/15

分区表

TO_DATE格式(以时间:2007-11-02 13:45:25为例)

Oracle提供的分区方法有以下几种。

  1. 日期和字符转换函数用法

1.范围分区(range

复制代码 代码如下: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;
//获取时间的秒

范围分区是应用范围比较广的表分区方式,它是以列的值的范围来作为分区的划分条

  1. 字符串和时间互转复制代码
    代码如下:select to_date(‘2004-05-07 13:23:44′,’yyyy-mm-dd hh24:mi:ss’)
    from dualselect to_char( to_date(222,’J’),’Jsp’) from dual //显示Two
    Hundred Twenty-Two

件,将记录存放到列值所在的 range分区中。

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’)

示例 1

  1. 两个日期间的天数复制代码
    代码如下:select floor(sysdate – to_date(‘20020405′,’yyyymmdd’)) from
    dual;

  2. 时间为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还是必要的

在某购物中心销售系统中,要求统计某季度的销售信息。

  1. 日期格式冲突问题输入的格式要看你安装的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

销售信息表包含如下列:销售流水号、产品
id、销售日期、销售金额、销售区域。

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秒,
而不是毫秒)

表 1 销售信息表 SALES

  1. 查找月份复制代码 代码如下: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

  2. 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.获得小时数复制代码
代码如下://extract()找出日期或间隔值的字段值SELECT EXTRACT(HOUR FROM
TIMESTAMP ‘2001-02-16 2:38:40′) from offer select sysdate
,to_char(sysdate,’hh’) from dual; SYSDATE TO_CHAR(SYSDATE,’HH’)
——————– ——————— 2003-10-13 19:35:21 07 select
sysdate ,to_char(sysdate,’hh24′) from dual; SYSDATE
TO_CHAR(SYSDATE,’HH24′) ——————– ———————–
2003-10-13 19:35:21 19

SALES_ID NUMBER 销售流水号

12.年月日的处理复制代码 代码如下:SELECT
older_date, newer_date, years, months, ABS ( TRUNC ( newer_date –
ADD_MONTHS (older_date, years * 12 + months) ) ) daysFROM ( 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 ) )

PRODUCT_ID VARCHAR2(5) 产品 id

13.处理月份天数不定的办法复制代码
代码如下:select to_char(add_months(last_day(sysdate) +1, -2),
‘yyyymmdd’),last_day(sysdate) from dual

SALES_DATE DATE 销售日期

14.找出今年的天数复制代码 代码如下:select
add_months(trunc(sysdate,’year’), 12) – trunc(sysdate,’year’) from dual
//闰年的处理方法 to_char( last_day( to_date(’02’ | | :year,’mmyyyy’)
), ‘dd’ ) //如果是28就不是闰年

SALES_COST NUMBER(10) 销售金额

15.yyyy与rrrr的区别

AREACODE VARCHAR2(5) 销售区域

复制代码 代码如下:YYYY99 TO_C ——-
—- yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 2001

解决方案一:没学过表分区时,根据要求查询的季度找到对应的所有月份,找出相应的

16.不同时区的处理复制代码 代码如下:select
to_char( NEW_TIME( sysdate, ‘GMT’,’EST’), ‘dd/mm/yyyy hh:mi:ss’)
,sysdate from dual;

记录,求和汇总来分析。

  1. 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位秒数

解决方案二:运用表分区的特性,根据范围分区的概念,按照季度将销售记录表创建为

18.一年的第几天复制代码 代码如下:select
TO_CHAR(SYSDATE,’DDD’),sysdate from dual //310 2002-11-6 10:03:51

范围分区表,某一季度的数据就包含在某一个分区中,只查询某一分区的数据就可获得该季

19.计算小时,分,秒,毫秒复制代码
代码如下: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) ) mSecondsFROM ( 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)作为日.

度的销售信息,而不用进行全表的筛选,从而提高数据库性能。

20.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 dual//日期 返回的是天 然后 转换为ss

分析:

21,round[舍入到最接近的日期](day:舍入到最接近的星期日)复制代码 代码如下:select sysdate
S1,round(sysdate) S2 ,round(sysdate,’year’) YEAR,round(sysdate,’month’)
MONTH ,round(sysdate,’day’) DAY from dual

如按照时间划分,2013年 4月 1日前的数据放到 P1分区,代表第一季度;2013年
7月

22,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型

1日前的数据放到 P2分区,代表第二季度;2013年 10月 1日前的数据放到
P3分区,代表

复制代码 代码如下:select sysdate S1,
trunc(sysdate) S2, //返回当前日期,无时分秒 trunc(sysdate,’year’) YEAR,
//返回当前年的1月1日,无时分秒 trunc(sysdate,’month’) MONTH ,
//返回当前月的1日,无时分秒 trunc(sysdate,’day’) DAY
//返回当前星期的星期天,无时分秒from dual

第三季度;2014年 1月 1日前的数据放到
P4分区,代表第四季度。在创建的时候,需要指

23,返回日期列表中最晚日期

定基于的列,以及分区的范围值。

复制代码 代码如下:select
greatest(’01-1月-04′,’04-1月-04′,’10-2月-04′) from dual

提示:

24.计算时间差复制代码
代码如下:注: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’))*澳门金沙vip,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 //时间差-秒

在按时间分区时,如果某些记录暂无法预测范围,可以创建
maxvalue分区,所有不在

25.更新时间复制代码
代码如下://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 //改变时间-秒

指定范围内的记录都会被存储到 maxvalue所在分区中。

26.查找月的第一天,最后一天复制代码
代码如下: 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_MonthFROM dual;

关键代码:

CREATE TABLE SALES1

(

1

 

SALES_ID NUMBER,

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE NOT NULL,

SALES_COST NUMBER(10),

AREACODE VARCHAR2(5)

)

PARTITION BY RANGE (SALES_DATE)

(

PARTITION P1 VALUES LESS THAN (to_date(‘2013-04-1’, ‘yyyy-mm-dd’)),

PARTITION P2 VALUES LESS THAN (to_date(‘2013-07-1’, ‘yyyy-mm-dd’)),

PARTITION P3 VALUES LESS THAN (to_date(‘2013-10-1’, ‘yyyy-mm-dd’)),

PARTITION P4 VALUES LESS THAN (to_date(‘2014-01-1’, ‘yyyy-mm-dd’)),

PARTITION P5 VALUES LESS THAN (maxvalue)

);

要查看在第三季度的数据,请输入以下语句。

SELECT SUM(sales_cost) FROM SALES1 partition(P3);

经验

一般创建范围分区时都会将最后一个分区设置为 maxValue,将其他数据落入

此分区,一旦需要时可以利用拆分分区的技术将需要的数据从最后一个分区分离

出去,单独形成一个分区。如果没有创建最大的分区,插入的数据超出范围就会

报错。

如果插入的数据就是分区键上的值,则该数据落入下一分区。例如插入数据位

‘2013-10-1’,则数据会落入 P4 分区。

2.散列分区(hash

对于那些无法有效划分范围的表,可以使用
hash分区。hash分区会将表中的数据平均

分配到指定的几个分区中,由于数据被平均分配到不同的分区,减少了查询时对数据块的竞

争,这样对于提高性能还是会有一定的帮助。列所在分区是依据分区列的
hash值自动分配,

因此并不能控制,也不知道哪条记录会被放到哪个分区中,hash分区也可以支持多个依赖

列。

示例 2

需求说明:

根据散列分区的特性,为了提高数据库的性能,将销售流水号作为分区键创建销售记录

表。

关键代码:

–准备工作,提前创建每个分区的表空间

–创建散列分区表

CREATE TABLE SALES2

2

 

(

SALES_ID NUMBER,

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE NOT NULL,

SALES_COST NUMBER(10),

AREACODE VARCHAR2(5)

)

PARTITION BY HASH (SALES_ID)

(

PARTITION P1 tablespace tablespace01,

PARTITION P2 tablespace tablespace02,

PARTITION P3 tablespace tablespace03

);

3.列表分区(list

列表分区也需要指定列的值,其分区值必须明确指定,该分区列只能有一个,不能像

range或者
hash分区那样同时指定多个列作为分区依赖列,但它的单个分区对应值可以是

多个。

在分区时,必须确定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/

更新就会失败,因此通常建议使用 list分区时,要创建一个
default分区存储那些不在指

定范围内的记录,类似 range分区中的 maxvalue分区。

示例 3

需求说明:

按照销售区域统计销售记录。

分析:

创建列表分区,分区键为销售区域代码列。

提示:

可以指定 default,把非分区规则的数据全部放到 default分区。

关键代码:

CREATE TABLE SALES3

(