Partioning

[shiala_100]

HI,
We have a table partitioned on a date but unfortunately when the data was loaded into that table the field was inserted with SYSDATE and hance all the data are put in one partition (depending on the sysdate ) . Now i can identify the data thru a number which will be there in a status table . But alread the table has Millions of data (say one partition) now instead of going thru the full scan (partition scan) i want to restrict the one and only partition . How do i specify the partition name in my sql statement and i want to index that partition alone as its one -time job. So is there any way that can be done?


Urgent .
Please help

Regards
Shyla

[sambavan]

http://oradoc.photo.net/ora817/DOC/s...rtiti.htm#6562

To query directly from partiotions

   SELECT *
    FROM employee PARTITION (partition_name)
    WHERE condition;

Sam

[shiala_100]

HI ,
When i specify count(1) from partition
say
select count(1) from acct_stmt PARTITION (OCT2000)
/
it uses the full table scan of the table and not the partition alone .

Does the aggregate functions use the full table scan inspite of the partitions

Regards
Shyla

[shiala_100]

Hi everyone,
Generally when i do an insert into a table it happens fast say for 2 lakh record it takes three mins. Whereas when i do an update it takes more than 30 minutes . the PCTFREE is 20 and the PCTUSED is 80 for that table and partitioned.
Can anyone suggest me what could be the reason for that?


Thanks in advance

Shyla