Hi Gurus,
I am going through the document below in partitions.
Partitioning Concepts
Please bear with me if I am wrong or mistaken.My questions are :
1) Partitions itself helps faster query execution as it decomposes large tables and with partition key, tells oracle to scan particular partition and prune others.
If so why do we need to maintain index additionally?Wouldn't optimizer just use partition and fetch rows faster ?There is a cost for index too.
2) As per this document and my understanding, local partition Index are partitioned using same partition key and hence it has same range of data.This enables Oracle to automatically keep the index partitions in sync with table partitions.And since partitions are independent on each other and they can be worked around independently during data base maintenance or recovery, this ensures greater availability.(Hence popular in DSS/or Ware housing where availability matters a lot)
Now coming to Global partitioned index, each partition may have rows that belong to different table partitions.I am confused on this.
How would this benefit in performance?Global partitioned index is more popular in OLTP environments.Can't OLTP environment have local partitioned Index ?
Could you please share some light on this?
Thanks