Hi Tom,

We are considering to create a partition table in Oracle 19c DB
our partition key column is INSERT_DATE, INTERVAL DAY Partition
we have message_id column which is unique key, using this we will have update on row. it is a sequence.

Table def:
msg_id number,
msg_Status varchar2(100),
msg_resort varchar2(100).
.....

Question1:
we have a select statement on this table. How can i limit below query to scan only one partition. We will have 7 days of data, i.e 7 partitions.

select * from tab1 where msg_status=:status and msg_resort=:2 order by msg_id ;

Question2:
We will update status using msg_id.

update tab1 set msg_Status=:1 where msg_id=:2;

In above two queries , we are not using partition key column in query. for select or for update. How is partitioning helpful in above approach.

What kind of indexes are helpful if we still want to do partition?
Is day wise partitions are better or weekly partitions are better when we run select statement using non partition key columns.

Can you share your thoughts.