现有一范围列表组合分区如下:我想查询其中列表子分区的信息SQL该如何写?SQL> select * from popcount partition (p9_subp1);
select * from popcount partition (p9_subp1)
*
ERROR 位于第 1 行:
ORA-02149: 指定的分区不存在----------------------------------------create table popcount
(
id number(3),
name varchar2(20),
sex varchar2(4),
age number(3)
)
partition by range (age)
subpartition by list (sex)
(
partition p1 values less than (10)
(
subpartition p1_subp1 values('boy'),
subpartition p1_subp2 values('girl')
),
partition p2 values less than (20)
(
subpartition p2_subp1 values('boy'),
subpartition p2_subp2 values('girl')
),
partition p3 values less than (30)
(
subpartition p3_subp1 values('boy'),
subpartition p3_subp2 values('girl')
),
partition p4 values less than (40)
(
subpartition p4_subp1 values('boy'),
subpartition p4_subp2 values('girl')
),
partition p5 values less than (50)
(
subpartition p5_subp1 values('boy'),
subpartition p5_subp2 values('girl')
),
partition p6 values less than (60)
(
subpartition p6_subp1 values('boy'),
subpartition p6_subp2 values('girl')
),
partition p7 values less than (70)
(
subpartition p7_subp1 values('boy'),
subpartition p7_subp2 values('girl')
),
partition p8 values less than (80)
(
subpartition p8_subp1 values('boy'),
subpartition p8_subp2 values('girl')
),
partition p9 values less than (90)
(
subpartition p9_subp1 values('boy'),
subpartition p9_subp2 values('girl')
),
partition p10 values less than (100)
(
subpartition p10_subp1 values('boy'),
subpartition p10_subp2 values('girl')
)
);
insert into popcount values(1,'aaa','boy',23);
insert into popcount values(2,'bbb','girl',45);
insert into popcount values(3,'ccc','boy',87);
insert into popcount values(4,'ddd','boy',56);
insert into popcount values(5,'eee','girl',83);
insert into popcount values(6,'fff','boy',35);
insert into popcount values(7,'ggg','boy',90);
insert into popcount values(8,'hhh','boy',76);
insert into popcount values(9,'iii','girl',16);
insert into popcount values(10,'jjj','boy',36);
insert into popcount values(11,'kkk','girl',88);
select * from popcount; ID NAME SEX AGE
---------- -------------------- ---- ----------
9 iii girl 16
1 aaa boy 23
6 fff boy 35
10 jjj boy 36
2 bbb girl 45
4 ddd boy 56
8 hhh boy 76
3 ccc boy 87
5 eee girl 83
11 kkk girl 88
7 ggg boy 90已选择11行。
select * from popcount partition (p9_subp1)
*
ERROR 位于第 1 行:
ORA-02149: 指定的分区不存在----------------------------------------create table popcount
(
id number(3),
name varchar2(20),
sex varchar2(4),
age number(3)
)
partition by range (age)
subpartition by list (sex)
(
partition p1 values less than (10)
(
subpartition p1_subp1 values('boy'),
subpartition p1_subp2 values('girl')
),
partition p2 values less than (20)
(
subpartition p2_subp1 values('boy'),
subpartition p2_subp2 values('girl')
),
partition p3 values less than (30)
(
subpartition p3_subp1 values('boy'),
subpartition p3_subp2 values('girl')
),
partition p4 values less than (40)
(
subpartition p4_subp1 values('boy'),
subpartition p4_subp2 values('girl')
),
partition p5 values less than (50)
(
subpartition p5_subp1 values('boy'),
subpartition p5_subp2 values('girl')
),
partition p6 values less than (60)
(
subpartition p6_subp1 values('boy'),
subpartition p6_subp2 values('girl')
),
partition p7 values less than (70)
(
subpartition p7_subp1 values('boy'),
subpartition p7_subp2 values('girl')
),
partition p8 values less than (80)
(
subpartition p8_subp1 values('boy'),
subpartition p8_subp2 values('girl')
),
partition p9 values less than (90)
(
subpartition p9_subp1 values('boy'),
subpartition p9_subp2 values('girl')
),
partition p10 values less than (100)
(
subpartition p10_subp1 values('boy'),
subpartition p10_subp2 values('girl')
)
);
insert into popcount values(1,'aaa','boy',23);
insert into popcount values(2,'bbb','girl',45);
insert into popcount values(3,'ccc','boy',87);
insert into popcount values(4,'ddd','boy',56);
insert into popcount values(5,'eee','girl',83);
insert into popcount values(6,'fff','boy',35);
insert into popcount values(7,'ggg','boy',90);
insert into popcount values(8,'hhh','boy',76);
insert into popcount values(9,'iii','girl',16);
insert into popcount values(10,'jjj','boy',36);
insert into popcount values(11,'kkk','girl',88);
select * from popcount; ID NAME SEX AGE
---------- -------------------- ---- ----------
9 iii girl 16
1 aaa boy 23
6 fff boy 35
10 jjj boy 36
2 bbb girl 45
4 ddd boy 56
8 hhh boy 76
3 ccc boy 87
5 eee girl 83
11 kkk girl 88
7 ggg boy 90已选择11行。
解决方案 »
- oracle存储过程中临时表的使用
- 大家都有分!oracle forms连接数据库问题
- 为什么使用union 或者union all的连接查询的语句中不能够使用 Order 排序? 怎么查看具体的insert into a select ... from ...的过程呢?
- 创建任务报错,如何搞?
- SQL中传入时间参数
- 请问这条SQL语句该怎么写
- 语句问题---
- 关于存储过程里COUNT总数
- 请教一个查询SQL的问题
- 高手请进,如何select这个表??
- Linux:Red Hat Enterprise Linux Server release 5 (Tikanga) 内核:2.6.18-8.el5xen ORACLE:10201_database_linux_x86_64.cpio.gz 安装说系统不支持
- imp 工具导入数据库,把带有外键的表的外键约束自动删除了,如何避免这个问题呀
create table popcount
(
id number(3),
name varchar2(20),
sex varchar2(4),
age number(3)
)
partition by range (age)
subpartition by list (sex)
SUBPARTITION TEMPLATE
(
subpartition p1_subp1 values('boy'),
subpartition p1_subp2 values('girl')
)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40),
partition p5 values less than (50),
partition p6 values less than (60),
partition p7 values less than (70),
partition p8 values less than (80),
partition p9 values less than (90),
partition p10 values less than (100)
);
ALL_TAB_PARTITIONS;
from user_tab_subpartitions a where table_name like 'popcount'