Home » RDBMS Server » Server Administration » table partition
table partition [message #318011] Mon, 05 May 2008 05:45 Go to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,

Oracle version : 9.2.0.1 on windows

I have done hash partitioning on table. I can see partitions.
But I am getting message : Only range partitiong is supported in this version.
How to view table partition data??

Data in table is,
1 Seema
2 Meena
& so on

When I tried to do list partition

SQL> create table ctct_dup2(id number, name varchar2(30))
2 PARTITION BY LIST( substr(name,1,1))
3 (PARTITION from_a_to_f VALUES (`A','B','C','D','E','F') ,
4 PARTITION from g_to_l VALUES (`G','H','I','J','K','L') ,
5 PARTITION from m_to_S VALUES (`M','N','O','P','Q','R','S') ,
6 PARTITION from t_to_z VALUES ('T','U','V','W','X','Y','Z')
7 )
8 ENABLE ROW MOVEMENT;
PARTITION BY LIST( substr(name,1,1))
*
ERROR at line 2:
ORA-00907: missing right parenthesis

Please help

[Updated on: Mon, 05 May 2008 05:58]

Report message to a moderator

Re: table partition [message #318020 is a reply to message #318011] Mon, 05 May 2008 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

list parameter must be a column and can't be an expression.

Regards
Michel
Re: table partition [message #318030 is a reply to message #318011] Mon, 05 May 2008 06:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I have done hash partitioning on table. I can see partitions.
> But I am getting message : Only range partitiong is supported in this version.

When do you get this message? When trying to query one partition?
If so, you probably have wrong idea about the reason for hash partitioning. In that case, data shall be (ideally equally) distributed into partitions using hash function - good for parallel inserting - so one partition contains 'random' data connected only by the same result of the (internal) hash function. I see no reason for querying them.

> PARTITION BY LIST( substr(name,1,1))

List partitions can be built on columns, not expressions.
Try RANGE partitioning instead; it should be good if NAME column starts with letters A-Z in uppercase.
Re: table partition [message #318212 is a reply to message #318030] Tue, 06 May 2008 02:13 Go to previous messageGo to next message
ksharma2008
Messages: 2
Registered: May 2008
Location: INDIA
Junior Member

From the Partitionig defination provided, you have wrongly choosen to do hash or list partitioning. Range partitioning is appropriate here.Secondly partitioning the table on name columnis not the right choice because it won't be unique.
Better you choose the unique key of this table. Last but not the least your application should use the partition key in SQL, only then this is going to benifit.

Which oracle RDBMS version you are using? Upto 10gr2 you need to by the separate licenses to use this feature.


Regards.....ksharma
Re: table partition [message #318215 is a reply to message #318212] Tue, 06 May 2008 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
From the Partitionig defination provided, you have wrongly choosen to do hash or list partitioning.

Which definition? Hash may be a good choice, it depends on the purpose of the partitioning.

Quote:
Secondly partitioning the table on name columnis not the right choice because it won't be unique.

And then? Partition is not just for unique value, it is made to... partition the data.

Quote:
Last but not the least your application should use the partition key in SQL, only then this is going to benifit.

Once again it depends on the purpose of the partitioning.

Quote:
Which oracle RDBMS version you are using?

First line of OP's post:
Quote:
Oracle version : 9.2.0.1 on windows


Regards
Michel

Re: table partition [message #318218 is a reply to message #318215] Tue, 06 May 2008 02:32 Go to previous messageGo to next message
ksharma2008
Messages: 2
Registered: May 2008
Location: INDIA
Junior Member

Right, it depends on partitioning purpose.I was talking in case of range partition, partitioning on unique key is always better.
It should not be a problem in 9X, this component comes by default.

Regards.....Ksharma

Re: table partition [message #318236 is a reply to message #318218] Tue, 06 May 2008 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
partitioning on unique key is always better.

Better regarding what?
"Always"? Nothing is "always" better otherwise the other things would not exist.

Regards
Michel
Re: table partition [message #318268 is a reply to message #318236] Tue, 06 May 2008 05:22 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Thanks to all.

oracle version : 9.2.0.8 on windows
I have done list partitioning of table on different column

partition by list(c_ctp_id)
( partition ctct_analyst values('2308')
partition ctct_others values('2305','2307','2310','2405')
)

.But when I checked in script in toad for that table, it has converted into hash partitions.
Folowing is code at the end of the script
PARTITION BY HASH (C_CTP_ID)
PARTITIONS 2
STORE IN ( AHD1_DATA,AHD1_DATA);


& in partitions tab in toad, it is showing
partition name max value
ctct_analyst 2308
ctct_others 2305,2307,2310,2405

I have created empty partitioned table ctct_060508 as above & now when I try to copy data from original non parttitoned table into it, it is giving following error
SQL> insert into ctct_060508 select * from ctct;
insert into ctct_060508 select * from ctct
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

But none of the key is different than partition key.
SQL> select distinct c_ctp_id from ctct;

C_CTP_ID
----------
2305
2307
2308
2310
2405
Why error is coming??

6 rows selected.
Re: table partition [message #318273 is a reply to message #318268] Tue, 06 May 2008 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I checked in script in toad for that table, it has converted into hash partitions.

Oracle does NOT convert what you say in something else. Either it can do it and do it either it can't and returns an error.
Maybe TOAD returns wrong information.
quoting Ana (anacedent):
Those who live by the GUI, die by the GUI.


Given that we don't what is the real partioning the rest of the post is meaningless.

Use SQL*Plus, copy and paste your session from beginning (create tables) to end (errors).

Regards
Michel
Re: table partition [message #318475 is a reply to message #318273] Wed, 07 May 2008 00:16 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi ,

Below is the code( done copy & paste from sqlplus)

SQL> CREATE TABLE CTCT_070508(
  2    ID               NUMBER        NOT NULL,
  3    PERSID           VARCHAR2 (30),
  4    DEL              NUMBER        NOT NULL,
  5    ALIAS            NUMBER,
  6    LAST_MOD         NUMBER,
  7    C_RESTRICTED     NUMBER,
  8    C_LAST_NAME      VARCHAR2 (30),
  9    C_FIRST_NAME     VARCHAR2 (30),
 10    C_MIDDLE_NAME    VARCHAR2 (30),
 11    C_USERID         VARCHAR2 (85),
 12    C_AKA            VARCHAR2 (30),
 13    C_PUBLIC_PHONE   VARCHAR2 (32),
 14    C_FAX_PHONE      VARCHAR2 (32),
 15    C_VOICE_PHONE    VARCHAR2 (32),
 16    C_BEEPER_PHONE   VARCHAR2 (32),
 17    C_EMAIL_SERVICE  VARCHAR2 (30),
 18    C_EMAIL_ADDR     VARCHAR2 (120),
 19    C_PEMAIL_ADDR    VARCHAR2 (120),
 20    C_L_ID           NUMBER,
 21    C_CTP_ID         NUMBER,
 22    C_ACCTYP_ID      NUMBER,
 23    C_PREF_ORDER     VARCHAR2 (12),
 24    C_CM_ID1         NUMBER,
 25    C_CM_ID2         NUMBER,
 26    C_CM_ID3         NUMBER,
 27    C_CM_ID4         NUMBER,
 28    C_WS_ID1         VARCHAR2 (30),
 29    C_WS_ID2         VARCHAR2 (30),
 30    C_WS_ID3         VARCHAR2 (30),
 31    C_WS_ID4         VARCHAR2 (30),
 32    C_DEPT           VARCHAR2 (12),
 33    C_EXPENSE        VARCHAR2 (12),
 34    C_NOTES          VARCHAR2 (240),
 35    C_ORG_ID         NUMBER,
 36    C_ADMIN_ORG_ID   NUMBER,
 37    C_NX_STRING1     VARCHAR2 (40),
 38    C_NX_STRING2     VARCHAR2 (40),
 39    C_NX_STRING3     VARCHAR2 (40),
 40    C_NX_STRING4     VARCHAR2 (40),
 41    C_NX_STRING5     VARCHAR2 (40),
 42    C_NX_STRING6     VARCHAR2 (40),
 43    C_NX_REF_1       NUMBER,
 44    C_NX_REF_2       NUMBER,
 45    C_NX_REF_3       NUMBER,
 46    C_PARENT         NUMBER,
 47    C_VENDOR         NUMBER,
 48    C_DOMAIN         NUMBER,
 49    C_SERVICE_TYPE   VARCHAR2 (30),
 50    C_TIMEZONE       VARCHAR2 (30),
 51    C_VAL_REQ        NUMBER,
 52    C_SCHEDULE       VARCHAR2 (30),
 53    C_AVAILABLE      NUMBER,
 54    C_CONTACT_NUM    VARCHAR2 (30),
 55    C_ADDR1          VARCHAR2 (30),
 56    C_ADDR2          VARCHAR2 (30),
 57    C_ADDR3          VARCHAR2 (30),
 58    C_ADDR4          VARCHAR2 (30),
 59    C_ADDR5          VARCHAR2 (30),
 60    C_ADDR6          VARCHAR2 (30),
 61    C_CITY           VARCHAR2 (30),
 62    C_STATE          NUMBER,
 63    C_ZIP            VARCHAR2 (14),
 64    C_COUNTRY        VARCHAR2 (16),
 65    C_POSITION       NUMBER,
 66    Z_C_PASSWD       VARCHAR2 (20),
 67    PRIMARY KEY ( ID )
 68      USING INDEX
 69       TABLESPACE AHD1_IDX PCTFREE 10
 70       STORAGE ( INITIAL 106496 NEXT 106496 PCTINCREASE 0 ))
 71     TABLESPACE AHD1_DATA
 72     PCTFREE 30
 73     INITRANS 1
 74     MAXTRANS 255
 75    STORAGE (
 76     INITIAL 57344
 77     NEXT 106496
 78     PCTINCREASE 0
 79     MINEXTENTS 1
 80     MAXEXTENTS 2147483645
 81   )
 82    PARTITION BY LIST(C_CTP_ID)
 83  (
 84    PARTITION PAR1 VALUES (2308),
 85    PARTITION PAR2 VALUES (2305,2307,2310,2405)
 86   ) ENABLE ROW MOVEMENT;

Table created.

SQL> insert into ctct_070508 select * from ctct;
insert into ctct_070508 select * from ctct
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

SQL> select distinct c_ctp_id from ctct;

  C_CTP_ID
----------
      2305
      2307
      2308
      2310
      2405


6 rows selected.

SQL>


Note : structure of ctct is same as that of ctct_070508.


[Updated on: Wed, 07 May 2008 00:31]

Report message to a moderator

Re: table partition [message #318485 is a reply to message #318011] Wed, 07 May 2008 00:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
SQL> select distinct c_ctp_id from ctct;

  C_CTP_ID
----------
      2305
      2307
      2308
      2310
      2405


6 rows selected.

SQL>
I see 5 values returned, but 6 rows reported.
The last one is apparently NULL value, which does not fit to any partition you created and the error is raised.
Re: table partition [message #318486 is a reply to message #318485] Wed, 07 May 2008 00:44 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Yes, Thanks.
It was a silly mistake
But, I have created table at sql prompt & when I checked script in toad for that table , it is showing hash partitioning.
Why is it so ???

[Updated on: Wed, 07 May 2008 00:53]

Report message to a moderator

Re: table partition [message #318503 is a reply to message #318486] Wed, 07 May 2008 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I checked script in toad for that table , it is showing hash partitioning.
Why is it so ???

Never trust a tool you didn't program yourself.
quoting Ana (anacedent):
Those who live by the GUI, die by the GUI.


Regards
Michel
Re: table partition [message #318511 is a reply to message #318503] Wed, 07 May 2008 01:28 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Thanks.
I have done range partitioning as above.
 create table tab3(id number,name varchar2(30))
 partition by range(name)
 (
 partition p1 values less than ('G%'),
 partition p2 values less than ('Z%')
 )
 ENABLE ROW MOVEMENT;


How to include values start with Z??
Since range partitioning supports values less than clause.



[Updated on: Wed, 07 May 2008 03:51]

Report message to a moderator

Re: table partition [message #318684 is a reply to message #318511] Wed, 07 May 2008 10:05 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Yes,
http://download.oracle.com/docs/cd/B10501_01/server.920/a96520/parpart.htm#98145
Re: table partition [message #318723 is a reply to message #318011] Wed, 07 May 2008 14:36 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just wonder why did you issue the '%' character; it makes no sense here, as ('%' = CHR(37)) < ('A' = CHR(65)) in BINARY (ASCII) comparition rules.
Maybe you shall read about Datatype Comparison Rules: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#54959
I would use CHR(ASCII('Z')+1) for BINARY sort.

However the sort behaves differently when NLS_SORT parameter is set differently. I have not coped with it very much, but you will find useful clues in Globalization Support Guide.

[Edit: Corrected the recommended expression]

[Updated on: Wed, 07 May 2008 14:40]

Report message to a moderator

Previous Topic: using dbms_metadata
Next Topic: update sys.props$ set VALUE$='AL16UTF16' how to recovery
Goto Forum:
  


Current Time: Fri Dec 09 15:28:05 CST 2016

Total time taken to generate the page: 0.11655 seconds