I have to created a table and partitioned it at on entry date and added a local partitioned index .
Now I used a query to fetch "num_rows" from user_tab_partitions to find out number of rows in each partition.
Getting this num_rows value as null , wonder why ?
After looking at Explain Plan after quering (select * from my_table1 where entry_date = '01-jan-2015';)
to find out whether it actually partitioned the table and its data is in different partitions , I interpreted it indeed did since Query plan had a row as Partition_range(Single ).
My Question is :
a) Is the data actually partitioned (have I misinterpreted the Explain plan)
b) Why is the num_rows column null in the Query (Pasted below)
c) Also additionally what difference it would have been If I had created a Global Index instead of Local Index in my case?
Following is the code Snippet:
----------------------------------------------------------------------------------------------
create table my_table1
(
roll_no number constraint my_table1_pk primary key,
entry_date date
)
partition by range(entry_date)
(
PARTITION data_p1 VALUES LESS THAN (TO_DATE('31-12-2014', 'DD-MM-YYYY')),
PARTITION data_p2 VALUES LESS THAN (MAXVALUE)
);
create unique index my_table1_indx on my_table1(entry_date) local;
----------------------------------------------------------------------------------------------
I now insert two rows:
insert into my_table1 values(1,to_date('01-01-2015','dd-mm-yyyy'));
insert into my_table1 values(2,to_date('01-02-2015','dd-mm-yyyy'));
----------------------------------------------------------------------------------------------
These have been inserted successfully Now using the below Query shows num_rows column as null.I dont know why ?
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
where table_name = 'MY_TABLE1'
ORDER BY table_name, partition_name;
----------------------------------------------------------------------------------------------