-
Range partition Ascii Col
Folks, Need ur help urgently
As I’m trying to create an Index partition by range on the column with data type (varchar2)
Create index ATT_IST_IX on ATT_IST (LOT_ATTE_VAL)
GLOBAL PARTITION BY RANGE (LOT_ATTE_VAL)
(PARTITION ATT_VALE_1 VALUES LESS THAN (‘’) TABLESPACE XXXXX_TS,
..
..)
The values of the column are like this ||0||,||2||,||1821a||,||2-JUL-2004||,||2L319.989||%B-F24||,||2-11-2003@||
How can I segregate this kind of data into at least 30 partitions, since it is varchar everything is character. So, I did look for counts of first character. Now how can I group this kind of data in ascending order to create partition values less than (.).
Help me out with the script.
Thanks
sat
-
Re: Range partition Ascii Col
Since you don't have a strong idea on how to partition, you can always create a hash partition. If you know the values that are going to be in the partition, then you could do list partitioning.
-
Have you identified some need for partitioning? It sounds like you just want to balance it, which could be done through locally managed tablespaces with uniform extent size much more easily.
-
Slimdave you are right we need to balance the data equally in all partitions and our env doesn't suggest LMT, as I'm new to this env I was asked to do range partition based on first characters.Suggest me how to segregate the existing data.
And our version is 9.2 thats why HASH doesnt work for an Index
sat
-
You can't use substr function for the partitioning key.
If you want 30 partitions based on the col value, use HASH partition.
For example,
PHP Code:
SQL> create table emp (empname varchar2(30),
empid int, salary int)
partition by hash(empname)
( partition p1, partition p2, partition p3);
Table created.
Here, I created 3 hash partitions on empname column. You
However, if you attempt to use substr on the empname col in the table definition, then you will get an error.
PHP Code:
SQL> create table emp (empname varchar2(30), empid int, salary int)
2 partition by hash(substr(empname,1,2))
3 ( partition p1, partition p2, partition p3);
partition by hash(substr(empname,1,2))
*
ERROR at line 2:
ORA-00907: missing right parenthesis
I just gave an example for 3 partitions. You can have a max of 64000 partitions on a table.
Moreover, Index HASH partition is not available in Oracle.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|