12 Replies Latest reply: Feb 18, 2013 2:22 AM by Zoltan Kecskemethy RSS

    Range Partitioning on Varchar2 column???

    User505978 - Oracle
      We hava table and it has a date column and its type is varchar2.

      This column's format is '16021013' ('ddmmyyyy').


      We want to make range partition on this column. What will be the best way?

      do you think virtal column partitioning will be efficient?
        • 1. Re: Range Partitioning on Varchar2 column???
          asahide
          Hi,
          We want to make range partition on this column. What will be the best way?
          do you think virtal column partitioning will be efficient?
          If you have a large number of insert, I do not recommend virtual column partitioning.
          But if not, I think that You can use vc-partitioning.

          Regards,
          • 2. Re: Range Partitioning on Varchar2 column???
            User505978 - Oracle
            what is the alternative ways?
            • 3. Re: Range Partitioning on Varchar2 column???
              rp0428
              >
              If you have a large number of insert, I do not recommend virtual column partitioning.
              But if not, I think that You can use vc-partitioning.
              >
              Why not? There aren't any issues with virtual column partitioning for large numbers of inserts that I am aware of.

              Please post documentation that supports that statement.
              • 4. Re: Range Partitioning on Varchar2 column???
                rp0428
                >
                We hava table and it has a date column and its type is varchar2.

                This column's format is '16021013' ('ddmmyyyy').
                >
                The first thing you should do is to fix the SERIOUS flaw you have in your data model. You should NEVER store date data in character datatype columns. Always use the appropriate data type for the data being stored.

                Your posted statement is a perfect example of this. Do you really have data for year '1013' in your database? It's possible, of course, but more likely that value is a mistake and Oracle can't find mistakes like that when you use the wrong datatype.

                Modify the table to use a DATE datatype for that data.
                >
                We want to make range partition on this column. What will be the best way?
                >
                Hard to say. It depends on why you are partitioning the table to begin with and what types of queries and query predicates you use when you query it.

                You need to provide the information you used that caused you to decide that the table even needs to be partitioned.
                >
                do you think virtal column partitioning will be efficient?
                >
                No way to tell. If you have queries that mostly do full table scans and you don't license the PARALLEL option your performance could be degraded by partioning the table to begin with.

                Using a virtual column will not impact your performance assuming that the column is the appropriate one to partition by base on your typical queries and predicates used.

                But you need to fix that datatype issue. And if you can't actually add the proper column to the table and then fix the data then you should add a virtual column on that data value and turn it into an actual DATE.
                CREATE TABLE mytest
                (
                    BAD_DATE_COLUMN VARCHAR2(8),
                    ACCOUNTID       NUMBER,
                    GOOD_DATE_COLUMN DATE GENERATED ALWAYS AS (TO_DATE(BAD_DATE_COLUMN, 'DDMMYYYY')) VIRTUAL
                )
                PARTITION BY RANGE (GOOD_DATE_COLUMN) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
                ( 
                    PARTITION PRE_2012 VALUES LESS THAN (TO_DATE('1-1-2012', 'DD-MM-YYYY'))
                );
                
                -- the next insert will succeed
                INSERT INTO MYTEST (BAD_DATE_COLUMN, ACCOUNTID) VALUES ('16021013', 1);
                
                -- this insert fails because their is garbage in the bad 'date' column
                INSERT INTO MYTEST (BAD_DATE_COLUMN, ACCOUNTID) VALUES ('ABC', 2)
                
                ORA-01858: a non-numeric character was found where a numeric was expected
                That virtual column will not only partition the table but will prevent you from entering bad data into your bad datatype column.
                • 5. Re: Range Partitioning on Varchar2 column???
                  User505978 - Oracle
                  @rp0428

                  first of all, thank you for your helpful answer

                  This table is being used for some complex queries on day, but after midnight, while end of day process is being working, it has huge bulk inserts. Maybe millions of datas are being inserted to this table.

                  I have also doubts about virtual columns while bulk inserts are coming to this table.Already we are trying to shorten end of day period. I didnt test yet, but in my opinion, virtual column can also have effect in bad way...just idea....

                  Edited by: 983327 on Feb 16, 2013 3:54 PM
                  • 6. Re: Range Partitioning on Varchar2 column???
                    rp0428
                    >
                    This table is being used for some complex queries on day, but after midnight, while end of day process is being working, it has huge bulk inserts. Maybe millions of datas are being inserted to this table.
                    >
                    So what? That may sound harsh but nothing in what you said provides any justification for partitioning the table. And you haven't ANY information about the current performance or ANY reason to think that the current performance needs to be improved or that partitioning might improve it.

                    As I said above you need to provide information about why you are partitioning the table to begin with and what types of queries and query predicates you use when you query it.

                    Everyone has complex queries. Without knowing detailed information about those queries and the predicates used there isn't any way to know if the table should be partitioned and, if so, what columns it should be partitioned on.

                    Queries that currently do full table scans may still do full table scans if those queries do not include filter predicates that would allow Oracle to prune partitions and only scan a subset of the data.

                    A predicate of "WHERE myColumn = 'x'" won't help with partition pruning unless partitioning is on the 'myColumn' column. And if a large number of the rows have a value of 'x' an index won't prevent a full scan of that data.

                    We need to know the row count for the table and for each of the filter predicates. For the above snippet we would want to know how many rows have a value of 'x' for 'myColumn' and how many distinct values of 'myColumn' are there?
                    >
                    I have also doubts about virtual columns while bulk inserts are coming to this table.Already we are trying to shorten end of day period. I didnt test yet, but in my opinion, virtual column can also have effect in bad way...just idea....
                    >
                    Unless you can post something to support that statement it is nothing but conjecture. You need to use facts. If you don't have them you need to gather them.

                    I already said above
                    >
                    Using a virtual column will not impact your performance assuming that the column is the appropriate one to partition by base on your typical queries and predicates used.
                    >
                    A 'virtual column' is just code; there is nothing to write to the database. All Oracle has to do is make sure that data in a DML statement (e.g. an insert or update) can be used to produce a valid value for the virtual column.
                    • 7. Re: Range Partitioning on Varchar2 column???
                      User505978 - Oracle
                      my friend,

                      I'm not a new DBA:-) You can be sure. I asked only about range partitioning trick with varchar2 column because we did our examination about this table. We will need to archive this table quarter by quarter to the other archive database. Then, off course, nearly all queries are coming firstly by using this date column and they can have different filtering inside "where" conditions. Already, this table has index on this column but with huge number of data, index performance is not enough for us. This is a 7x24 banking system and we are lately joined to this project. Because of this, we cant change everything like changing data type of that column after this moment.

                      Now, our only aim is how to make range partitioning this varchar2 date column.
                      • 8. Re: Range Partitioning on Varchar2 column???
                        Zoltan Kecskemethy
                        I would consider to use HASH partition on a VARCHAR2 data type. This would help the query performance but the archive procedure could be slow...

                        Or HASH(to_date(yourcolumn))...

                        Or composite RANGE-HASH

                        Or composite RANGE-RANGE (here I would extract years from string for main partition and quarter for sub-partition)

                        I'm just brainstorming tho so not sure if you can implement this and you have high enough db version...

                        Edited by: Zoltan Kecskemethy on Feb 17, 2013 2:10 PM
                        more ideas addded
                        • 9. Re: Range Partitioning on Varchar2 column???
                          User505978 - Oracle
                          while making partitioning,

                          is this possible?

                          HASH(to_date(yourcolumn))...

                          for example, i tried this but it didnt work

                          create table EMP3
                          (
                          employee_id NUMBER(6),
                          first_name VARCHAR2(20),
                          last_name VARCHAR2(25) not null,
                          email VARCHAR2(25) not null,
                          phone_number VARCHAR2(20),
                          hire_date VARCHAR2(20),
                          job_id VARCHAR2(10) not null,
                          salary NUMBER(8,2),
                          commission_pct NUMBER(2,2),
                          manager_id NUMBER(6),
                          department_id NUMBER(4)
                          )
                          ) tablespace users
                          partition by range (to_date(hire_date, 'dd.mm.yyyy' ))
                          (
                          partition january values less than hire_date tablespace users,
                          partition february values less than (to_date('01.03.2013', 'dd.mm.yyyy')) tablespace users,
                          .
                          .
                          .

                          we can use function when declaring partition mode?

                          Edited by: 983327 on Feb 17, 2013 9:48 AM

                          Edited by: 983327 on Feb 17, 2013 9:49 AM
                          • 10. Re: Range Partitioning on Varchar2 column???
                            sb92075
                            983327 wrote:
                            while making partitioning,

                            is this possible?

                            HASH(to_date(yourcolumn))...

                            for example, i tried this but it didnt work

                            create table EMP3
                            (
                            employee_id NUMBER(6),
                            first_name VARCHAR2(20),
                            last_name VARCHAR2(25) not null,
                            email VARCHAR2(25) not null,
                            phone_number VARCHAR2(20),
                            hire_date VARCHAR2(20),
                            storing any DATE as a VARCHAR2 is a bug waiting to bite folks into the future.

                            ALWAYS use DATE datatype to store dates.
                            • 11. Re: Range Partitioning on Varchar2 column???
                              rp0428
                              >
                              I'm not a new DBA:-) You can be sure. I asked only about range partitioning trick with varchar2 column because we did our examination about this table. We will need to archive this table quarter by quarter to the other archive database. Then, off course, nearly all queries are coming firstly by using this date column and they can have different filtering inside "where" conditions. Already, this table has index on this column but with huge number of data, index performance is not enough for us. This is a 7x24 banking system and we are lately joined to this project. Because of this, we cant change everything like changing data type of that column after this moment.
                              >
                              You are taking things way too personally. No one said anything about whether you were, or were not, a DBA or made any other comments about your skill or abilities.

                              What we ask was for you to tell us WHAT the problem was that you were trying to solve and WHY you thought partitioning would solve it.

                              And now, after several generic posts, you have finally provided that information. We can only comment based on the information that you post. We can't guess as to what types of queries you use or what kinds of predicates you use in those queries. But we need to know that information in order to provide the best advice.

                              Next time you post put the important information in your original question:

                              1. A table column is VARCHAR2 but contains a date value. We are unable to change the datatype of this column.
                              2. We need to archive data quarterly based on this date value
                              3. Nearly all queries use this date value and then also may have additional filter conditions
                              4. This date column is indexed but we would like to improve the performance beyond what this index can give us.

                              The above is a summary that includes all important information that is needed to know how best to help you.

                              And I made a pretty good guess since two replies ago I provided you with example code that shows just how to partition the table.
                              >
                              Now, our only aim is how to make range partitioning this varchar2 date column.
                              >
                              As I showed you in my example code earlier you can add a virtual column to the table and partition on it. My example code creates a monthly partitioned table that allows you to archive by month or archive every three months to archive by quarter.

                              You can modify that example to use quarterly partitions if you want but I would recommend that you use standard monthly partitions since they will satisfy the widest range of predicates.
                              • 12. Re: Range Partitioning on Varchar2 column???
                                Zoltan Kecskemethy
                                983327 wrote:
                                while making partitioning,

                                is this possible?

                                HASH(to_date(yourcolumn))...
                                ...
                                >
                                we can use function when declaring partition mode?
                                No sorry. This was a brainstorming idea only. When I tried it did not work for me either.
                                SQL> CREATE TABLE hash_part_test (
                                 hpt_id NUMBER(12),
                                 hpt_date_str VARCHAR2(8),
                                 CONSTRAINT hpt_id PRIMARY KEY (hpt_id) USING INDEX TABLESPACE users
                                )
                                PARTITION BY HASH (to_date(hpt_date_str,'DDMMYYYY'))
                                PARTITIONS 4
                                STORE IN (USERS,USERS,USERS,USERS);  
                                PARTITION BY HASH (to_date(hpt_date_str,'DDMMYYYY'))
                                                          *
                                ERROR at line 6:
                                ORA-00907: missing right parenthesis
                                But when added in a virtual column - as rp0428 suggested - worked.
                                SQL> CREATE TABLE hash_part_test (
                                 hpt_id NUMBER(12),
                                 hpt_date_str VARCHAR2(8),
                                 hpt_date DATE GENERATED ALWAYS AS (TO_DATE(hpt_date_str, 'DDMMYYYY')) VIRTUAL,
                                 CONSTRAINT hpt_id PRIMARY KEY (hpt_id) USING INDEX TABLESPACE users
                                )
                                PARTITION BY HASH (hpt_date)
                                PARTITIONS 4
                                STORE IN (users,users,users,users);
                                
                                Table created.
                                Also have to note that it is not really wise to create a date data type virtual column for a HASH partition when HASH would work similarly for a VARCHAR2 column.

                                Well I just wanted to bring into the picture HASH partition type as you can easily split your data to get a bit better performance and it can use VARCHAR2 data type for this just fine.