So what, you may wonder, happens to the indexes on partitioned tables? The answer is that you have to choose whether each index will stay intact (referred to as a global index), or be split into pieces corresponding to the table partitions (referred to as a local index). Furthermore, with a global index, you can choose to partition the index in a different manner than the table was partitioned. When you throw the fact that you can partition both b-tree and bit-map indexes into the mix, things can become overwhelming. When you issue a SELECT, UPDATE, or DELETE statement against a partitioned table, the optimizer can take several routes to locate the target rows:
Use a global index, if one is available and its columns are referenced in the SQL statement, to find the target rows across one or more partitions.
Search a local index on every partition to identify whether any particular partition contains target rows.
Define a subset of the partitions that might contain target rows, and then access local indexes on those partitions.