Home » SQL & PL/SQL » SQL & PL/SQL » how to select data from subpartition table (oracle 10g)
how to select data from subpartition table [message #620481] Sat, 02 August 2014 02:03 Go to next message
jgjeetu
Messages: 246
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

Below I have created a table with RANGE-LIST Partiion

CREATE TABLE EMPL 
PARTITION BY RANGE(DEPTNO)
SUBPARTITION BY LIST(JOB)
(PARTITION P10 VALUES LESS THAN(20)
(SUBPARTITION P10JOBA VALUES ('ANALYST'),
SUBPARTITION P10JOBC VALUES('CLERK'),
SUBPARTITION P10JOBM VALUES('MANAGER'),
SUBPARTITION P10JOBS VALUES('SALESMAN')),
PARTITION P20 VALUES LESS THAN(30)
(SUBPARTITION P20JOBA VALUES ('ANALYST'),
SUBPARTITION P20JOBC VALUES('CLERK'),
SUBPARTITION P20JOBM VALUES('MANAGER'),
SUBPARTITION P20JOBS VALUES('SALESMAN')),
PARTITION P30 VALUES LESS THAN(40)
(SUBPARTITION P30JOBA VALUES ('ANALYST'),
SUBPARTITION P30JOBC VALUES('CLERK'),
SUBPARTITION P30JOBM VALUES('MANAGER'),
SUBPARTITION P30JOBS VALUES('SALESMAN')))
AS SELECT * FROM EMP
WHERE 1=2



then i insert data from emp table into empl table
using below query

INSERT INTO EMPL
SELECT * FROM EMP


now i am trying to select data from empl table
these 2 queries are working well:-

select * from empl and job equal to ANALYST



SELECT * FROM EMPL PARTITION(P10)


but now i want to select data for deptno 10

i tried below query but it's not working

SELECT * FROM EMPL PARTITION(P10) SUBPARTITION(P10JOBA)


Please tell me how to select data for deptno 10 and job=ANALYST from subpartition table

Thanks
Re: how to select data from subpartition table [message #620482 is a reply to message #620481] Sat, 02 August 2014 02:08 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
Before you SELECT, you have to INSERT, which you can't:
orclz> INSERT INTO EMPL
  2  SELECT * FROM EMP;
INSERT INTO EMPL
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


orclz>
you need to add a default list subpartition and a maxvalue range partition.
Then when you run SELECT, do not attempt to specify the partition, simply run a normal SELECT. Let Uncle Oracle sort it out.
Re: how to select data from subpartition table [message #620483 is a reply to message #620481] Sat, 02 August 2014 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you should have a look at the documentation
And ALWAYS post what you do and get from SQL*Plus session.

SQL> SELECT * FROM EMPL SUBPARTITION(P10JOBA);

no rows selected



Re: how to select data from subpartition table [message #620484 is a reply to message #620483] Sat, 02 August 2014 02:23 Go to previous message
jgjeetu
Messages: 246
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

Thanks @michel , now i am able to select data from subpartition table .

& @John I didn't get such error anyways thanks for the comment.
Previous Topic: case statemnent
Next Topic: Executing procedures in parallel
Goto Forum:
  


Current Time: Fri Dec 26 20:25:42 CST 2014

Total time taken to generate the page: 0.09074 seconds