This discussion is archived
5 Replies Latest reply: Dec 2, 2012 2:03 AM by 949210 RSS

partitioning (range) a table values less than 'A'

949210 Newbie
Currently Being Moderated
i am referring
http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
"CORE     10.2.0.1.0     Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

create table drop_it as select * from mv_prod_search_det2;     
CREATE TABLE DROP_IT_P(
PROD_DETAILS VARCHAR2(1000 BYTE), 
     SIGN VARCHAR2(42 BYTE)
)
PARTITION BY RANGE(PROD_DETAILS)
(
PARTITION MAX_VALUE VALUES LESS THAN (MAXVALUE)
);
update drop_it set prod_details=upper(prod_details);     
72000 rows updated
ALTER TABLE drop_it_p EXCHANGE PARTITION MAX_VALUE WITH TABLE drop_it WITH VALIDATION;     
select * from mv_prod_search_det2
72000 rows selected
exec dbms_stats.gather_database_stats;
select * from drop_it_p partition(max_value)
ALTER TABLE DROP_IT_P 
  SPLIT PARTITION MAX_VALUE AT ('B%') 
  INTO (PARTITION p_a, 
        PARTITION MAX_VALUE);     
select * from drop_it_p partition(p_a);
6785 rows selected
select * from drop_it_p partition(p_a) where prod_details not like 'A%'     
696 rows selected
it even shows me values that start with W,V,I,1,2,3,4,24,5 etc
although the number is less(696out of 6785) this is undesired
please help me eliminate these rows
thank you

this thread is related to tuning regexp_like by author 946207
please refer
tuning regexp_like
and partitioning a table by 946207

Edited by: 946207 on Dec 2, 2012 1:52 AM

Edited by: 946207 on Dec 2, 2012 11:02 PM
  • 1. Re: partitioning(range) a table values less than 'A'
    sb92075 Guru
    Currently Being Moderated
    946207 wrote:
    i am referring
    http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm
    http://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    "CORE     10.2.0.1.0     Production"
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    create table drop_it as select * from mv_prod_search_det2;     
    CREATE TABLE DROP_IT_P(
    PROD_DETAILS VARCHAR2(1000 BYTE), 
         SIGN VARCHAR2(42 BYTE)
    )
    PARTITION BY RANGE(PROD_DETAILS)
    (
    PARTITION MAX_VALUE VALUES LESS THAN (MAXVALUE)
    );
    update drop_it set prod_details=upper(prod_details);     
    72000 rows updated
    ALTER TABLE drop_it_p EXCHANGE PARTITION MAX_VALUE WITH TABLE drop_it WITH VALIDATION;     
    select * from mv_prod_search_det2
    72000 rows selected
    exec dbms_stats.gather_database_stats;
    select * from drop_it_p partition(max_value)
    ALTER TABLE DROP_IT_P 
    SPLIT PARTITION MAX_VALUE AT ('B%') 
    INTO (PARTITION p_a, 
    PARTITION MAX_VALUE);     
    select * from drop_it_p partition(p_a);
    6785 rows selected
    select * from drop_it_p partition(p_a) where prod_details not like 'A%'     
    696 rows selected
    it even shows me values that start with W,V,I,1,2,3,4,24,5 etc
    although the number is less(696out of 6785) this is undesired
    please help me eliminate these rows
    thank you

    Edited by: 946207 on Dec 1, 2012 6:06 PM
    DIGITS (0,1,2,3,4,5,6,7,8,9) have ASCII values less than "A"
    CHR(41) = A
    while the numeric digits have values 30 - 39 inclusive.
  • 2. Re: partitioning(range) a table values less than 'A'
    rp0428 Guru
    Currently Being Moderated
    First, when you post related threads you should cross-link them so people have access to all of the information about the problem you are trying to work with.
    partitioning a table
    >
    it even shows me values that start with W,V,I,1,2,3,4,24,5 etc
    although the number is less(696out of 6785) this is undesired
    >
    Yes - that is what it should be doing.

    These are the steps you took to populate the table

    1. You originally inserted ALL data into table 'drop_it' with no restriction on the PROD_DETAILS values.
    create table drop_it as select * from mv_prod_search_det2;     
    2. Then you converted the PROD_DETAILS value to upper case. That has no effect on numbers or other non-alphabetic characters.
    update drop_it set prod_details=upper(prod_details);
    3. Then you create a new table with only one partition using MAXVALUe
    PROD_DETAILS VARCHAR2(1000 BYTE), 
         SIGN VARCHAR2(42 BYTE)
    )
    PARTITION BY RANGE(PROD_DETAILS)
    (
    PARTITION MAX_VALUE VALUES LESS THAN (MAXVALUE)
    );
    4. Then you populate the partitioned table by exchange. It now has the same data including the numeric data.
    ALTER TABLE drop_it_p EXCHANGE PARTITION MAX_VALUE WITH TABLE drop_it WITH VALIDATION;     
    5. Then you split the one MAXVALUE partition into two partitions. One with data < 'B%' and one with the remaining data that sorts higher based on your character set.
    ALTER TABLE DROP_IT_P 
      SPLIT PARTITION MAX_VALUE AT ('B%') 
      INTO (PARTITION p_a, 
            PARTITION MAX_VALUE);     
    The split on 'B%' when creating partition p_a is equivalent to you 'WITH VALUES < 'B%'. Since PROD_DETAILS is a VARCHAR2 datatype that 'LESS THAN' comparison uses the character order based on your database character set and most, if not all, character sets have characters that sort lower than the uppercase alphabetic characters.

    For example in the ASCII character set an uppercase 'A' is decimal 65 so 64 other characters (including the digitis 0-9) sort lower than 'A'.
    http://www.asciitable.com/

    As the doc you cited shows
    >
    •All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.
    >
    That 'first' partition has no lower bound so ALL data, including digits, that sort less than 'B%' will be in that partition.
    >
    please help me eliminate these rows
    >
    Either don't select the data to begin with or remove it using a simple DELETE query. Also you can do the case conversion when you select the data.
    create table drop_it as select upper(prod_details) prod_details, sign from mv_prod_search_det2 where upper(prod_details >= 'A';
    Before you do that you should make sure you define the actual business rule you want to use to define the data you really want to keep and exclude.

    Because most, if not all, character sets also have characters that sort HIGHER than the alphabetic characters. That ASCII table shows five of them. If you don't filter them out you will get data where the values start with those characters.

    Even if you do filter them out there is nothing in what you posted that would prevent a user from inserting that data back into the table.

    And, of course, there are characters that sort BETWEEN the lower and upper case alphabetics.

    You need to determine what the allowable characters are in the PROD_DETAILS column and add code (e.g. check constraint or trigger) to make sure users can't enter data that includes those characters.
  • 3. Re: partitioning(range) a table values less than 'A'
    949210 Newbie
    Currently Being Moderated
    >
    DIGITS (0,1,2,3,4,5,6,7,8,9) have ASCII values less than "A"
    CHR(41) = A
    while the numeric digits have values 30 - 39 inclusive.
    thanks sb92075
    yes i am aware of ascii values and that it is doing as expected by including rows that start with numbers
    but why are rows that start with W,I,M included
    and chr(65)=A so a row if starts with@ will move to the partition with values less than A
    chr(64)=@
    I am trying to have only the rows that start with the alphabet mentioned in the partition clause
    also the table from which it is being copied is all capital
    this thread is related to tuning regexp_like by author 946207
    and partitioning a table by 946207

    Edited by: 946207 on Dec 2, 2012 2:02 AM
  • 4. Re: partitioning(range) a table values less than 'A'
    rp0428 Guru
    Currently Being Moderated
    >
    yes i am aware of ascii values and that it is doing as expected by including rows that start with numbers
    but why are rows that start with W,I,M included
    >
    You haven't shown that rows that start with W,I,M ARE included and since you havent' provided any data at all or any INSERT statements or told us what character set you are using we have no way to try to reproduce your results.
    >
    and chr(65)=A so a row if starts with@ will move to the partition with values less than A
    chr(64)=@
    >
    Yes - you just said 'i am aware . . .' above. And as I said in my reply above EVERYTHING beginning with a character lower in the sort order than 'B' FOR YOUR CHARACTER SET will be in your 'p_a' partition.
    >
    I am trying to have only the rows that start with the alphabet mentioned in the partition clause
    also the table from which it is being copied is all capital
    >
    Then you need to define the partition for values < 'A' and those rows will go to that partition. If you need a partition for every letter of the alphabet you need to define a VALUES LESS THAN '?' (where ? is the letter) partition for each of them.

    You also need to find the first character higher than a 'Z' for your characterset (for ascii it is the '['): VALUES LESS THAN '['.
    >
    this thread is related to tuning regexp_like by author 946207
    and partitioning a table by 946207
    >
    That information is useless unless you provide the links. The only reason the link you provided in your other thread didn't work is because you had an extra character at the end. If you remove it the link works.

    This code works just fine for me.
    CREATE TABLE DROP_IT_P(
    PROD_DETAILS VARCHAR2(1000 BYTE), 
        SIGN VARCHAR2(42 BYTE)
    )
    PARTITION BY RANGE(PROD_DETAILS)
    (
    PARTITION MAX_VALUE VALUES LESS THAN (MAXVALUE)
    );
    
    insert into drop_it_p (prod_details) values ('W')
    
    insert into drop_it_p (prod_details) values ('A')
    
    insert into drop_it_p (prod_details) values ('a')
    
    insert into drop_it_p (prod_details) values ('V')
    
    insert into drop_it_p (prod_details) values ('I')
    
    insert into drop_it_p (prod_details) values (1)
    
    ALTER TABLE DROP_IT_P 
      SPLIT PARTITION MAX_VALUE AT ('B%') 
      INTO (PARTITION p_a, 
            PARTITION MAX_VALUE);    
    
    select * from drop_it_p partition(p_a);
    
    PROD_DETAILS,SIGN
    A,
    1,
    
    select * from drop_it_p partition(p_a) where prod_details not like 'A%'    
    
    PROD_DETAILS,SIGN
    1,
  • 5. Re: partitioning(range) a table values less than 'A'
    949210 Newbie
    Currently Being Moderated
    >
    and chr(65)=A so a row if starts with@ will move to the partition with values less than A
    chr(64)=@
    >
    Yes - you just said 'i am aware . . .' above. And as I said in my reply above EVERYTHING beginning with a character lower in the sort order than 'B' FOR YOUR CHARACTER SET will be in your 'p_a' partition.
    rp0428
    what i understand is that W(capital W) is after A(according to ascii)
    that is @ will be included in the partition with values less than A(according to ascii)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points