关于表分区的后续问题？

[DoMyBest_Stone]

为某个大表创建表分区后，在查询语句中比如select * from table_name a where a.csrq like '200901%' 其中表按csrq分区，这个语句会只sacn对应的分区么？

[Comer]

mark

[crazylaa]

这个语句应该是全表扫描了。。。
如果要查某个表的某个分区，应该是
from table_name partition(part_02)。
part_02表示你要查的对应的分区名，跟建表语句里面的对应。

[crazylaa]

以前写的。参考下吧。
表结构

--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条记录。