Hi ,
I am creating a table A with partitioning p1 on column txn_date.
Case One:
Now i create a index on txn_date mentioning keyword LOCAL, which will be a loacl prefixed index as the index is been created on partitioned key txn_date.
And if the same key (Txn_date) is used in where clause ,the partition pruning will be used.
Case Two:
Now i create a index on other column txn_id mentioning keyword LOCAL, which will be a loacl non prefixed index as the index is created on other column which is not a partitioned key.
And if the Txn_id (Non prefixed index) is used in where clause ,it will look into the partition p1 and use it as normal index , the partition pruning is not used.
Case Three:
If the Txn_date (prefixed index) and Txn_id (Non prefixed index) is used in where clause ,it will look into the partition p1 and whether the partition pruning will be used here.?
Also please tell is my understanding correct with respect to case one and two.