num_rows is empty in user_tab_partitions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: num_rows is empty in user_tab_partitions

  1. #1
    Join Date
    May 2001
    Posts
    285
    Hi,

    As part of performance tuning, I created a partitioned table and inserted about 12 million rows to this table by using

    insert * into tbl_part select * from tbl_old;

    The new table has 12 different partitions based on calldate.

    2 questions:

    1. when I tried to run a query against this new table, a full table scan still occurs instead of using just that partition. Why?
    note: that query's where clause does use calldate as a range.

    2. When I look at user_tab_partitions, the num_rows are null for each partition of the newly created partition table, while I was expecting to see all different values based on the number of records in that partition. Did I miss anything?

    Thanks a lot!

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well have you analyzed the partition? I guess not

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by elaine3839
    1. when I tried to run a query against this new table, a full table scan still occurs instead of using just that partition. Why?
    note: that query's where clause does use calldate as a range.
    Is this what you have partitioned on?


    2. When I look at user_tab_partitions, the num_rows are null for each partition of the newly created partition table, while I was expecting to see all different values based on the number of records in that partition. Did I miss anything?
    The num_rows only shows up after you have analyze the table.

    Also, if you didn't analyze the table AFTER you put data into it, your indexes will probably not be used. Hence, maybe problem in #1 also.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    May 2001
    Posts
    285
    So do I need to analyze every single partition to get the num_rows?

    Should the syntax look something like:


    Analyze table call_part(part_1) estimate statistics

    OR should I use caculate statistics instead?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    If you're going to do every partition, just do the whole table. You can use ESTIMATE, but I prefer to use COMUTE.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Actually, I prefer COMPUTE.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width