Examples of Partition Methods and Keys for Oracle EBS Tables

This is not an exhaustive list, just an example of some of partitioning designs that may work well for some customers. There are many other examples that are not covered here.

There could be reasons why the examples that are covered are not suitable in all cases. Each customer should investigate, determine and test their own partitioning strategies.

There may also be additional tables that can be added to each case (for the same partition key) for some customers. They could have been omitted from the list below because (in the cases we examined) the volumes were too low or there were too many null values or there is not significant access via the partition key.

Note that the partitioning design should be checked for likely future business change. For example, is there likely to be a large increase in ORG_IDs or SET_OF_BOOKS_IDs as a system is rolled out to different geographies or as new businesses are acquired.

Table name(s)	Partition Method	Partition Key	Possible Partition Benefits	Discussion
AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL Possibly AP_LIABILITY_BALANCE, AP_PAYMENT_SCHEDULES_ALL, AP_INVOICE_PAYMENTS_ALL	HASH	INVOICE_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL Possibly AP_LIABILITY_BALANCE, AP_PAYMENT_SCHEDULES_ALL, AP_INVOICE_PAYMENTS_ALL	RANGE	INVOICE_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent invoices will be in the same partition, this method cannot be used for Distribution of Workload.
AP_AE_LINES_ALL, AP_AE_HEADERS_ALL	HASH	AE_HEADER_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
AP_AE_LINES_ALL, AP_AE_HEADERS_ALL, AP_LIABILITY_BALANCE (if AE_HEADER_ID is only null on a few rows)	RANGE	AE_HEADER_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent AE_HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
ZX_LINES_DET_FACTORS, ZX_LINES	HASH	TRX_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
ZX_LINES_DET_FACTORS, ZX_LINES	RANGE	TRX_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent TRX_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
MTL_MATERIAL_TRANSACTIONS MTL_CST_ACTUAL_COST_DETAILS MTL_TRANSACTION_ACCOUNTS	HASH	TRANSACTION_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
MTL_MATERIAL_TRANSACTIONS MTL_CST_ACTUAL_COST_DETAILS MTL_TRANSACTION_ACCOUNTS	RANGE	TRANSACTION_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent TRANSACTION_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
MTL_MATERIAL_TRANSACTIONS MTL_TXN_REQUEST_LINES MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_TL MTL_ITEM_CATEGORIES MTL_DEMAND	HASH	INVENTORY_ITEM_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. High number of distinct keys and even distribution.
MTL_MATERIAL_TRANSACTIONS MTL_TXN_REQUEST_LINES MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_TL MTL_ITEM_CATEGORIES MTL_DEMAND	RANGE	INVENTORY_ITEM_ID	Distribution of Workload Pruning and Joining DB Manageability	Because INVENTORY_ITEM_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons
MTL_TXN_REQUEST_LINES, MTL_TXN_REQUEST_HEADERS	HASH	HEADER_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. High number of distinct keys and even distribution.
MTL_TXN_REQUEST_LINES, MTL_TXN_REQUEST_HEADERS	RANGE	HEADER_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
PAY_ELEMENT_ENTRIES_F PER_ALL_ASSIGNMENTS_F PAY_ASSIGNMENT_ACTIONS PER_PAY_PROPOSALS possibly PAY_PERSONAL_PAYMENT_METHODS_F PAY_PROCESS_EVENTS PAY_US_EMP_FED_TAX_RULES_F and other PAY_US_EMP tables	HASH	ASSIGNMENT_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. High number of distinct keys and even distribution.
PAY_ELEMENT_ENTRIES_F PER_ALL_ASSIGNMENTS_F PAY_ASSIGNMENT_ACTIONS PER_PAY_PROPOSALS possibly PAY_PERSONAL_PAYMENT_METHODS_F PAY_PROCESS_EVENTS PAY_US_EMP_FED_TAX_RULES_F and other PAY_US_EMP tables	RANGE	ASSIGNMENT_ID	Distribution of Workload Pruning and Joining DB Manageability	Because ASSIGNMENT_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons
PAY_RUN_RESULTS PAY_RUN_RESULT_VALUES PAY_COSTS	HASH	RUN_RESULT_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
PAY_RUN_RESULTS PAY_RUN_RESULT_VALUES PAY_COSTS	RANGE	RUN_RESULT_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent RUN_RESULT_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
PAY_ELEMENT_ENTRIES_F PAY_ELEMENT_ENTRY_VALUES_F	HASH	ELEMENT_ENTRY_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
PAY_ELEMENT_ENTRIES_F PAY_ELEMENT_ENTRY_VALUES_F	RANGE	ELEMENT_ENTRY_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent ELEMENT_ENTRY_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_LINE_SALESREPS_ALL	HASH	CUSTOMER_TRX_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_LINE_SALESREPS_ALL	RANGE	CUSTOMER_TRX_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent CUSTOMER_TRX_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
OE_ORDER_LINES_ALL, OE_ORDER_HEADERS_ALL, OE_SALES_CREDITS	HASH	HEADER_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
OE_ORDER_LINES_ALL, OE_ORDER_HEADERS_ALL, OE_SALES_CREDITS	RANGE	HEADER_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
GL_BALANCES GL_JE_HEADERS Possibly GL_JE_LINES	LIST or RANGE	PERIOD_NAME	Pruning and Joining DB Manageability Lifecycle	Used in significant number of filters and joins. Because new / most recent PERIOD_NAMEs will be in the same partition, this method cannot be used for Distribution of Workload. The HASH method cannot be used for distribution of workload either, because all new/recent rows will have the same PERIOD_NAME.
GL_BALANCES GL_JE_LINES GL_BC_PACKETS	HASH	CODE_COMBINATION_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. High number of distinct keys and even distribution.
GL_BALANCES GL_JE_LINES GL_BC_PACKETS	RANGE	CODE_COMBINATION_ID	Distribution of Workload Pruning and Joining DB Manageability	Because CODE_COMBINATION_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons
GL_JE_HEADERS GL_JE_LINES GL_IMPORT_REFERENCES GL_JE_SEGMENT_VALUES	HASH	JE_HEADER_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
GL_JE_HEADERS GL_JE_LINES GL_IMPORT_REFERENCES GL_JE_SEGMENT_VALUES	RANGE	JE_HEADER_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent JE_HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
WIP_PERIOD_BALANCES WIP_TRANSACTION_ACCOUNTS WIP_TRANSACTIONS WIP_ENTITIES WIP_FLOW_SCHEDULES	HASH	WIP_ENTITY_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. High number of distinct keys and even distribution.
WIP_PERIOD_BALANCES WIP_TRANSACTION_ACCOUNTS WIP_TRANSACTIONS WIP_ENTITIES WIP_FLOW_SCHEDULES	RANGE	WIP_ENTITY_ID	Distribution of Workload Pruning and Joining DB Manageability	Because WIP_ENTITY_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons
PO_HEADERS_ALL PO_LINES_ALL PO_RELEASES_ALL PO_LINE_LOCATIONS_ALL PO_DISTRIBUTIONS_ALL RCV_TRANSACTIONS RCV_ACCOUNTING_EVENTS	HASH	PO_HEADER_ID	Distribution of Workload Pruning and Joining	Used in significant number of filters and joins. Very high number of distinct keys and even distribution.
PO_HEADERS_ALL PO_LINES_ALL PO_RELEASES_ALL PO_LINE_LOCATIONS_ALL PO_DISTRIBUTIONS_ALL RCV_TRANSACTIONS RCV_ACCOUNTING_EVENTS	RANGE	PO_HEADER_ID	Pruning and Joining DB Manageability Lifecycle	Because new / most recent PO_HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload.
Tables in many modules	HASH	ORG_ID or SET_OF_BOOKS_ID / LEDGER_ID	Distribution of Workload Pruning and Joining	ORG_ID or SET_OF_BOOKS_ID/LEDGER_ID are present on many tables across Oracle E-Business Suite. They are often used as a predicate (ORG_ID almost universally in Multi-Org views ). Concurrent programs often only process data for single ORG_IDs or SET_OF_BOOKS_IDs. Used in significant number of filters and joins. Only feasible if there are a large number of distinct values (>100) and a relatively even distribution.
Tables in many modules	RANGE	ORG_ID or SET_OF_BOOKS_ID / LEDGER_ID	Distribution of Workload Pruning and Joining DB Manageability	Only feasible if there is a relatively even distribution.
Tables in many modules	LIST	ORG_ID or SET_OF_BOOKS_ID LEDGER_ID	Distribution of Workload Pruning and Joining DB Manageability	Only feasible if there are relatively few values (< 100) being split up into a few list partitions (<20)

Gokul kumar

Table name(s)

Partition Method

Partition Key

Possible Partition Benefits

Discussion

AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL

HASH

INVOICE_ID

Distribution of Workload

Used in significant number of filters and joins.

AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL

RANGE

INVOICE_ID

Pruning and Joining

Because new / most recent invoices will be in the same partition, this method cannot be used for Distribution of Workload.

AP_AE_LINES_ALL, AP_AE_HEADERS_ALL

HASH

AE_HEADER_ID

Distribution of Workload

Used in significant number of filters and joins.

AP_AE_LINES_ALL, AP_AE_HEADERS_ALL, AP_LIABILITY_BALANCE (if AE_HEADER_ID is only null on a few rows)

RANGE

AE_HEADER_ID

Pruning and Joining

Because new / most recent AE_HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload.

ZX_LINES_DET_FACTORS, ZX_LINES

HASH

TRX_ID

Distribution of Workload

Used in significant number of filters and joins.

ZX_LINES_DET_FACTORS, ZX_LINES

RANGE

TRX_ID

Pruning and Joining

Because new / most recent TRX_IDs will be in the same partition, this method cannot be used for Distribution of Workload.

MTL_MATERIAL_TRANSACTIONS

HASH

TRANSACTION_ID

Distribution of Workload

Used in significant number of filters and joins.

MTL_MATERIAL_TRANSACTIONS

RANGE

TRANSACTION_ID

Pruning and Joining

Because new / most recent TRANSACTION_IDs will be in the same partition, this method cannot be used for Distribution of Workload.

MTL_MATERIAL_TRANSACTIONS MTL_TXN_REQUEST_LINES MTL_SYSTEM_ITEMS_B

HASH

INVENTORY_ITEM_ID

Distribution of Workload

Used in significant number of filters and joins.

MTL_MATERIAL_TRANSACTIONS MTL_TXN_REQUEST_LINES MTL_SYSTEM_ITEMS_B

RANGE

INVENTORY_ITEM_ID

Distribution of Workload

Because INVENTORY_ITEM_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons

MTL_TXN_REQUEST_LINES, MTL_TXN_REQUEST_HEADERS

HASH

HEADER_ID

Distribution of Workload

Used in significant number of filters and joins.

MTL_TXN_REQUEST_LINES, MTL_TXN_REQUEST_HEADERS

RANGE

HEADER_ID

Pruning and Joining

Because new / most recent HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload.

PAY_ELEMENT_ENTRIES_F

HASH

ASSIGNMENT_ID

Distribution of Workload

Used in significant number of filters and joins.

PAY_ELEMENT_ENTRIES_F

RANGE

ASSIGNMENT_ID

Distribution of Workload

Because ASSIGNMENT_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons

PAY_RUN_RESULTS

HASH

RUN_RESULT_ID

Distribution of Workload

Used in significant number of filters and joins.