DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Range partition Ascii Col

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    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.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    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

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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:
    SQLcreate table emp (empname varchar2(30), 
         
    empid intsalary int)
         
    partition by hash(empname)
         ( 
    partition p1partition p2partition 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:
    SQLcreate table emp (empname varchar2(30), empid intsalary int)
      
    2  partition by hash(substr(empname,1,2))
      
    3  partition p1partition p2partition 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
  •  


Click Here to Expand Forum to Full Width