Can you please elucidate on the following.
"If you cannot, or have not, defined a secondary index on the primary index, then having fewer partitions is better than having more partitions, whether achieved by means of the table definition itself or by row partition elimination during query processing."
"Delete operations can be nearly instantaneous when the partitioning column set matches the retention policy, there is no secondary index defined on the partitioning column set, and the delete is the last statement in the transaction."
"You can delete all of the rows in a partition if you want to do so. In this case, there is no journaling of rows if no secondary index is defined on the partitioning column set."
How directly does the secondary index affects the number of partitions.
Secondary indexes are used sometimes to improve access over a PPI table. When you can not define a SI on PI, for example in case of NUPI, if your query contains condition on partition columns then the PI access will be much faster, else the PI access will analyse all the partitions on an amp. In that case if you define a SI on the column, it will improve the perfomance by dirctly going to the exact partition.
But in general I dont think there is a relation between a SI and PPI in terms of number of partitions.
The above statements are the choices you have when in your design consideration. I have not tested this but it is proven as disadvantages of PPI.
Yes , when partitioning column set matches the retention timeframe , then it is instantaneous.
As you know that tables having SIs always have subtables.
Also with the coming of column partition, we are having more options to play with data.
Maybe you can try to test with data for below scenarios and put lesser partitions. I can't do since I am in production: