为某个大表创建表分区后,在查询语句中比如select * from table_name a where a.csrq like '200901%' 其中表按csrq分区,这个语句会只sacn对应的分区么?
解决方案 »
- Win 7 + 64位的Oracle数据库 + 32位的Oracle客户端, PLSQL没有“登录为“选项
- Oracle数据库登陆的一个非常奇怪的问题!!!
- oracle 服务全都关掉了也没有oracle进程只启动了plsql(有plsql进程)
- sql查询
- 问个不连续编号的问题
- 看看下面的SQL怎么写,哪样最简单?
- oracle一定要用到firewire吗?firewire又是什么?
- 关闭数据库shutdown normal和shutdown immediate有什么区别?
- Ora-12560:TNS:协议适配器错误!急!!
- 安装ORACLE 9i出的问题
- 紧急,复杂查询TimesTen 与oracle对比问题
- Oracle导出多个表时如何判断列名是否存在
如果要查某个表的某个分区,应该是
from table_name partition(part_02)。
part_02表示你要查的对应的分区名,跟建表语句里面的对应。
表结构--drop table sms_confirm_month;/*==============================================================*/
/* Table: sms_confirm_month */
/*==============================================================*/
create table sms_confirm_month (
send_id number(32) not null,
sms_id number(32) not null,
req_seq varchar2(32),
msg_id varchar2(32),
task_id number(32) not null,
temp_id number(32) not null,
user_id number(32) not null,
user_group_id number(32) not null,
sender_num varchar2(21),
mobile_no varchar2(21) not null,
contents varchar2(210) not null,
send_time timestamp not null,
confirm_time timestamp,
part_pos number(2) not null,
sms_status number(1) not null,
pk_id number(27)
) tablespace sms_data9
partition by range (part_pos)
(
partition part_01 values less than(1) tablespace SMS_DATA1,
partition part_02 values less than(2) tablespace SMS_DATA2,
partition part_03 values less than(3) tablespace SMS_DATA3,
partition part_04 values less than(4) tablespace SMS_DATA4,
partition part_05 values less than(5) tablespace SMS_DATA1,
partition part_06 values less than(6) tablespace SMS_DATA2,
partition part_07 values less than(7) tablespace SMS_DATA3,
partition part_08 values less than(8) tablespace SMS_DATA4,
partition part_09 values less than(9) tablespace SMS_DATA1,
partition part_10 values less than(10) tablespace SMS_DATA2,
partition part_11 values less than(11) tablespace SMS_DATA3,
partition part_12 values less than(12) tablespace SMS_DATA4
);alter table sms_confirm_month enable row movement;create index idx_m_send_id on sms_confirm_month(send_id)
local
(
partition idx_m_send_id_1 tablespace SMS_INDEX1,
partition idx_m_send_id_2 tablespace SMS_INDEX1,
partition idx_m_send_id_3 tablespace SMS_INDEX1,
partition idx_m_send_id_4 tablespace SMS_INDEX1,
partition idx_m_send_id_5 tablespace SMS_INDEX1,
partition idx_m_send_id_6 tablespace SMS_INDEX1,
partition idx_m_send_id_7 tablespace SMS_INDEX1,
partition idx_m_send_id_8 tablespace SMS_INDEX1,
partition idx_m_send_id_9 tablespace SMS_INDEX1,
partition idx_m_send_id_10 tablespace SMS_INDEX1,
partition idx_m_send_id_11 tablespace SMS_INDEX1,
partition idx_m_send_id_12 tablespace SMS_INDEX1
);create index idx_m_mobile_no on sms_confirm_month(mobile_no)
local
(
partition idx_m_mobile_no_1 tablespace SMS_INDEX1,
partition idx_m_mobile_no_2 tablespace SMS_INDEX1,
partition idx_m_mobile_no_3 tablespace SMS_INDEX1,
partition idx_m_mobile_no_4 tablespace SMS_INDEX1,
partition idx_m_mobile_no_5 tablespace SMS_INDEX1,
partition idx_m_mobile_no_6 tablespace SMS_INDEX1,
partition idx_m_mobile_no_7 tablespace SMS_INDEX1,
partition idx_m_mobile_no_8 tablespace SMS_INDEX1,
partition idx_m_mobile_no_9 tablespace SMS_INDEX1,
partition idx_m_mobile_no_10 tablespace SMS_INDEX1,
partition idx_m_mobile_no_11 tablespace SMS_INDEX1,
partition idx_m_mobile_no_12 tablespace SMS_INDEX1
);create index idx_m_msg_id on sms_confirm_month(msg_id) tablespace SMS_INDEX1;comment on table sms_confirm_month is
'月份非实时短信回执表';select * from sms_confirm_month scm,
(select t.sms_id from
(
select rownum r,uall.sms_id from
(
select distinct utt.sms_id from
(
(
select sms_confirm_month.sms_id,sms_confirm_month.send_time
from sms_confirm_month partition(part_03)
where send_time >= to_timestamp('20080706 00:00:00','yyyyMMdd HH24:MI:SS')
and task_id = 0
)
union all
(
select sms_confirm_month.sms_id,sms_confirm_month.send_time
from sms_confirm_month partition(part_02)
where task_id = 0
)
union all
(
select sms_confirm_month.sms_id,sms_confirm_month.send_time
from sms_confirm_month partition(part_01)
where send_time <= to_timestamp('20080905 00:00:00','yyyyMMdd HH24:MI:SS')
and task_id = 0
)
) utt
order by utt.sms_id
) uall
where rownum <= 200
) t
where t.r>=101
)pagedata
where scm.sms_id in(pagedata.sms_id)
order by scm.send_time
;--以上为进行了分页的,其中 rownum<=200 和t.r>=101 表示按100条记录进行分页,查出第2页的数据,即第101条到200条记录。