num_rows is empty in user_tab_partitions

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!

well have you analyzed the partition? I guess not

Quote:

---

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?

Quote:

---

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.

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?

If you're going to do every partition, just do the whole table. You can use ESTIMATE, but I prefer to use COMUTE.

Actually, I prefer COMPUTE.