6 Replies Latest reply on Jul 19, 2012 6:49 AM by Burasami

    Partition Data Problem

    Burasami
      Hi All,

      I have created sample new table with partition
      SET DEFINE OFF;
      CREATE TABLE T1
      (
        ID        NUMBER,
        RUN_DATE  DATE
      )
      PARTITION BY RANGE(RUN_DATE)
      (
       PARTITION PART1 VALUES LESS THAN (to_date('05-07-2012','fmDDfm-fmMMfm-YYYY')),
       PARTITION PART2 VALUES LESS THAN (to_date('10-07-2012','fmDDfm-fmMMfm-YYYY')),
       PARTITION PART3 VALUES LESS THAN (to_date('15-07-2012','fmDDfm-fmMMfm-YYYY')),
       PARTITION PART4 VALUES LESS THAN (to_date('20-07-2012','fmDDfm-fmMMfm-YYYY')),
       PARTITION PART5 VALUES LESS THAN (to_date('25-07-2012','fmDDfm-fmMMfm-YYYY')),
       PARTITION PART6 VALUES LESS THAN (MAXVALUE));
      
      
      ALTER TABLE T1 ADD (CONSTRAINT T1_ID_PK PRIMARY KEY (ID));
      when I tried to view the partition data's it show only 5 rows which was inserted by long back( by yesterday evening At that time rows doesn't appeared in the partition )
      Today I can view those 5 row's in the different partition.

      TABLE  PART_NAME            HIGH_VALUE                          NUM_ROWS     
      -------- --------------------          ------------------                     --------------------------
      
      T1     PART1     TO_DATE(' 2012-07-05 00:00:00', 'SYYYY-MM-DD HH24:M     2
      T1     PART2     TO_DATE(' 2012-07-10 00:00:00', 'SYYYY-MM-DD HH24:M     0
      T1     PART3     TO_DATE(' 2012-07-15 00:00:00', 'SYYYY-MM-DD HH24:M     1
      T1     PART4     TO_DATE(' 2012-07-20 00:00:00', 'SYYYY-MM-DD HH24:M     0
      T1     PART5     TO_DATE(' 2012-07-25 00:00:00', 'SYYYY-MM-DD HH24:M     1
      T1     PART7     TO_DATE(' 2012-07-30 00:00:00', 'SYYYY-MM-DD HH24:M     0
      T1     PART6     MAXVALUE     0
      Today I inserted the rows as follows
      insert into t1 ( select TEST_SEQ.nextval, trunc(sysdate)-18 from all_objects); --with different dates
      commit;
      
      select distinct run_date, count(*) as no_row  from t1 group by run_date
      
      Run_date            ID
      ------------------  -------------
      11-06-2012     1
      04-07-2012     40720
      01-07-2012     40719
      09-07-2012     40719
      11-07-2012     40720
      19-07-2012     40719
      24-07-2012     40720
      29-07-2012     40719
      But still partition list show the same data's.. So I don't know what happen to newly inserted data's .. I am wondering why data's not appearing at once in the partition list..

      Any suggestions? Idea's..? how to trace the data's.. why its not getting into the partition list..

      Thanks & Regards
      Sami.
        • 1. Re: Partition Data Problem
          sb92075
          consider doing COMMIT

          Handle:     Sami
          Status Level:     Newbie (5)
          Registered:     Jul 26, 2010
          Total Posts:     156
          Total Questions:     45 (30 unresolved)

          why do you waste your time here when you rarely get answers to your questions?
          • 2. Re: Partition Data Problem
            rp0428
            >
            Today I can view those 5 row's in the different partition.
            >
            Really? Then why is the total count of NUM_ROWS only 4?
            >
            Today I inserted the rows as follows

            insert into t1 ( select TEST_SEQ.nextval, trunc(sysdate)-18 from all_objects); --with different dates
            commit;

            select distinct run_date, count(*) as no_row from t1 group by run_date

            Run_date ID
            ------------------ -------------
            11-06-2012     1
            04-07-2012     40720
            01-07-2012     40719
            09-07-2012     40719
            11-07-2012     40720
            19-07-2012     40719
            24-07-2012     40720
            29-07-2012     40719
            >
            Again - really? You show a query with the alias NO_ROW for the count(*) column but the results you post show ID for the second column header and values of 1, 40719 and 40720 for the data values. So there are 40720 records that have a date of 04-07-2012?

            Why don't you post the actual DDL and DML that you are using instead of bits and pieces from who knows where?
            • 3. Re: Partition Data Problem
              Vivek L
              Don't you have to analyze your table for getting correct statistics in tabpartitions ?
              http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2098.htm
              ALL_TAB_PARTITIONS provides partition-level partitioning information, partition storage parameters, and partition statistics collected by ANALYZE statements for partitions accessible to the current user.
              • 4. Re: Partition Data Problem
                Burasami
                Hi All,
                Really? Then why is the total count of NUM_ROWS only 4?
                Am using toad 10.5 When I browse the the table and view partition data's it show me 4 records..
                Part name      High value                           High value length    tablespace compressed                             Row_num
                
                PART1     TO_DATE(' 2012-07-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')     83     USERS     DISABLED     2     5     0     18-07-2012 10:01:46 PM     0     0
                PART2     TO_DATE(' 2012-07-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')     83     USERS     DISABLED     0     0     0     18-07-2012 10:01:46 PM     0     0
                PART3     TO_DATE(' 2012-07-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')     83     USERS     DISABLED     1     5     0     18-07-2012 10:01:46 PM     0     0
                PART4     TO_DATE(' 2012-07-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')     83     USERS     DISABLED     0     0     0     18-07-2012 10:01:46 PM     0     0
                PART5     TO_DATE(' 2012-07-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')     83     USERS     DISABLED     1     5     0     18-07-2012 10:01:46 PM     0     0
                PART7     TO_DATE(' 2012-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')     83     USERS     DISABLED     0     0     0     18-07-2012 10:01:46 PM     0     0
                PART6     MAXVALUE     8     USERS     DISABLED     0     0     0     18-07-2012 10:01:46 PM     0     0
                when I use the following SQL it gives 5 records
                SELECT table_name,partition_name,high_value,num_rows FROM user_tab_partitions;
                
                table part_name    High_value                                          num_rows
                -----   -------------     --------------                                          -----------------  
                T1     PART1     TO_DATE(' 2012-07-05 00:00:00', 'SYYYY-MM-DD HH24:M     2
                T1     PART2     TO_DATE(' 2012-07-10 00:00:00', 'SYYYY-MM-DD HH24:M     0
                T1     PART3     TO_DATE(' 2012-07-15 00:00:00', 'SYYYY-MM-DD HH24:M     1
                T1     PART4     TO_DATE(' 2012-07-20 00:00:00', 'SYYYY-MM-DD HH24:M     0
                T1     PART5     TO_DATE(' 2012-07-25 00:00:00', 'SYYYY-MM-DD HH24:M     1
                T1     PART7     TO_DATE(' 2012-07-30 00:00:00', 'SYYYY-MM-DD HH24:M     0
                T1     PART6     MAXVALUE     0
                You show a query with the alias NO_ROW for the count(*) column but the results you post show ID for the second column
                
                Mistakenly given wrong column name. 
                select distinct run_date, count(*) as no_row  from t1 group by run_date
                
                RUN_DATE NO_ROW
                
                11-JUN-12     1
                04-JUL-12     40720
                01-JUL-12     40719
                09-JUL-12     40719
                11-JUL-12     40720
                19-JUL-12     40719
                24-JUL-12     40720
                29-JUL-12     40719
                So there are 40720 records that have a date of 04-07-2012?
                Yes


                Thanks & Regards
                Sami
                • 5. Re: Partition Data Problem
                  rp0428
                  Well the rows in the system tables (where you get 5) show the results after statistics are computed on the table.

                  So if you never update the stats you will always get 5.
                  • 6. Re: Partition Data Problem
                    Burasami
                    Hi All,
                    Well the rows in the system tables (where you get 5) show the results after statistics are computed on the table. 
                    
                    So if you never update the stats you will always get 5.
                    Yes your right...

                    After running the GATHER_TABLE_STATS procedure manually the rows get updated in partition list..
                    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', CASCADE => TRUE);
                    Yesterday I didn't ran GATHER_TABLE_STATS but today morning I can able visible the those 5 rows in the partition list..(Means that oracle gather stats automatically at 18-07-2012 10:30 PM )

                    Any way thanks for your suggestion and help..

                    Finally I got resolved..

                    Thanks & Regards
                    Sami.