How to properly partition a table into partition in Oracle?

Question

Oracle

There is a table in which more than 100 million records.
GoodsList table structure:

ID_DATETIME NUMBER
ID_SUPPLIER NUMBER
ID_CLIENT NUMBER
ID_PRICEITEM NUMVER
COST NUMBER

1. How to properly divide it into partition by date, if most often reports are made for the current month? And is it worth doing, let's say such a partition partition by year, then into subpartition by months .

2. Can the creation of a new partition be made automatic according to the division criterion?

3. If the structure is rebuilt, split this table into two.
To the first GoodsListHead:

ID NUMBER
ID_DATETIME NUMBER
ID_PRODUCER NUMBER
ID_CLIENT NUMBER

And the second GoodsList:

ID_GOODSLISTSHEAD NUMBER
ID_PRICEITEM NUMBER
COST NUMBER

How, then, to organize the partition by date in the GoodsList table?

Answer 1

Regarding question 2 - you can, here is an example of how to do it.
On question 3 - no way, directly in the table there should be a key by which partitioning takes place (because it will be determined by it at the time of inserting / changing a record in which table to physically insert a record)

Answer 2

I did not work with Partitions, so I will not undertake to answer on this topic.
However, if the problems are with the construction of reporting, and you need to speed up the reports for the current month, maybe you should try Materialized Views? Did you think in this direction?

Answer 3

If you have a "final list" in ID_columns, then list is partitioned by it and range by date. In general, partitioning from Oracle is quite expensive 1/4 of the Enterprise license. It might be better to split it into 2 tables (the last month and everything else), transfer the data every month and refer to the view created on both if you need to see everything.