-
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
-
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
-
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.
Jeff Hunter
-
Actually, I prefer COMPUTE.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|