Doubts/Questions related to list partition on existing table with existing indexes in Oracle

Question

We want to partition two existing tables (INVOICE_ITEMS and INVOICE_ITEMS_EVENTS). These two existing tables already have indexes on it (as given in the image below).

INVOICE_ITEMS (existing indexes before table partition)

INVOICE_ITEMS_EVENTS (existing indexes before table partition)

• Do we need to drop these existing indexes and create partition indexes? Local or Global?
• What happens if we keep existing indexes instead of creating partition indexes?

We have many views which are using these tables (INVOICE_ITEMS and INVOICE_ITEMS_EVENTS). And these tables are joined using item_obj_3 and itemtype columns in the both of the tables.

Below is an example view which is using the above two tables.

CREATE OR REPLACE VIEW "XWDDSB128"."V_INVOICE_GETUSAGE" ("DOCUMENTID", "DEVICE_ID", "END_T", "IMPACT_CATEGORY", "RECORD_TYPE", "USAGE_CLASS", "DESTINATION", "CALLING_NUMBER", "CALLING_FROM", "CALLED_TO", "DURATION", "LOCATION", "VOLUME", "AMOUNT", "PROD_NAME", "EVENT_OBJ_2", "EVENT_OBJ", "CRBTINFO_EVENT_TYPE", "BUCKET_ID", "BALANCE_AMOUNT", "CHANNEL", "PRODUCT_NAME", "TYPE_STR", "BALANCE_TYPE", "BALANCE_OPTION", "PURCHASE_START_T", "RESOURCE_NAME", "AAC_ACCESS", "EVENT_TYPE") AS 
  SELECT            boi.DOCUMENTID,
                     boi.device_id ,
                     bie.end_t ,
                     impact_category ,
                     bie.vfqbalance_RECORD_TYPE record_type  ,
                     bie.telcoinfo_USAGE_CLASS usage_class  ,
                     bie.vfqbalance_LOCATION destination  ,
                     bie.vfqbalance_CALLING_NUMBER CALLING_NUMBER,
                     bie.vfqbalance_CALLING_FROM CALLING_FROM,
                     bie.vfqbalance_CALLED_TO CALLED_TO,
                     bie.vfqbalance_DURATION DURATION,
                     bie.vfqbalance_LOC_AREA_CODE location  ,
                     NVL(bie.gsminfoNUMBER, 0) volume  ,
                     NVL(bie.evttotal_amount, 0) amount  ,
                     bie.crbtinfo_prod_name prod_name ,
                     bie.EVENT_OBJ_2 ,
                     bie.event_obj,
                     bie.crbtinfo_EVENT_TYPE ,
                     bie.vfqbalance_VFQ_BUCKET_ID bucket_id  ,
                     bie.vfqbalance_AMOUNT balance_amount  ,
                     bie.crbtinfo_CHANNEL CHANNEL ,
                     bie.crbtinfo_PRODUCT_NAME PRODUCT_NAME ,
                     boi.TYPE_STR ,
                     bie.vfqbalance_TYPE balance_type  ,
                     bie.vfqbalance_OPTIONS balance_option  ,
                     bie.vfqbalance_purchase_start_t purchase_start_t ,
                     bie.vfqbalance_resource_name resource_name ,
                     boi.AAC_ACCESS,
                     bie.CRBTINFO_EVENT_TYPE event_type
        FROM (SELECT * FROM invoice_items WHERE ( ITEMTYPE = 'AR_ITEMS' OR ITEMTYPE = 'SUB_ITEMS')) boi
               JOIN invoice_items_events bie   ON  boi.item_obj_3 = bie.item_obj_3 AND boi.ITEMTYPE = bie.ITEMTYPE  
               and bie.vfqbalance_RECORD_TYPE is not null;

If we convert the tables into list partitioned tables , by adding a partition column named schema_name in both the tables, then, how we can rewrite the above view so that it query only partitioned tables?

Answer 1

On a partitioned table, all indexes will either by local or global. There is no third option of "neither". A global index is a non-partitioned index on a partitioned table. A local index is a partitioned index that shares the partitioning scheme of the table (so every table partition has its own set of index segments).

I presume that a partitioning column named schema_name suggests a very limited number of partitions. If this is true, local indexes would not harm performance noticeably even when the partitioning key is not used as an access predicate (which it appears from your question that this generally would not be the case).

Making the indexes local would cause a noticeable performance penalty if you do (1) lots of high-frequency, (2) low-cardinality (e.g. single-row) seeks (3) without partition pruning (4) and there are many partitions (as it has to seek in each one separately). All four of these factors need to be in place to make local indexes problematic: even if you have many partitions, if your predicates are not very selective the cost of fetching by ROWID from the table segment is far greater than the cost of walking the index segments, so again the penalty becomes inconsequential. And even if they were selective, if such seeks were relatively few and infrequent, again, not an overly large concern. Lastly, even if they were high-frequency, if the partitioning key is used as an access predicate and so prune partitions, there is no penalty. All four conditions have to be met for local indexes to be a problem.

But, if the indexes were local, you would have the benefit of being able to truncate, compress, move, drop, etc.. any of the list partitions separately without having to rebuild the entire index. You could do an online partition move with the update indexes option and have it complete in a reasonable amount of time (unlike with global indexes). There are numerous management benefits to local indexes. In fact, in our warehouses I generally prohibit the use of global indexes because of how they complicate management.

I cannot advise on changes to the view; views are queries that are themselves queried, which means additional predicates can be applied to them, totally changing the nature of the overall SQL and how it should work. If it is natural to your data to include the partitioning key (schema_name) in the join, that will likely help, either by pruning in a nested loops join (and so reducing index seeks) or by reducing the size of the inputs to a hash join if you were to apply a predicate to that column from outside the view. So by all means add that if you can.