This discussion is archived
6 Replies Latest reply: Jul 18, 2012 11:49 PM by 787349 RSS

Partition Data Problem

787349 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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
    787349 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    787349 Newbie
    Currently Being Moderated
    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.

Legend

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