5 Replies Latest reply: Dec 2, 2012 4:03 AM by 949210 RSS

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

    949210
      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
          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
            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
              >
              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
                >
                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
                  >
                  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)