Example of Transaction Flexfield Indexes

Create indexes on your AutoInvoice Transaction Flexfield columns if you want to query transaction flexfield information in your invoice headers and lines. If you don't use indexes, the validation portions of the Import AutoInvoice process can exhibit slow performance.

Define Indexes

Define non-unique, concatenated indexes on the tables and columns that you use for your Transaction Flexfield header and line information. The tables and columns are described in this table:

Table	Columns
RA_CUSTOMER_TRX_LINES_ALL	interface_line_attribute1-15
RA_CUSTOMER_TRX_ALL	interface_header_attribute1-15
RA_INTERFACE_LINES_ALL	interface_line_attribute1-15
RA_INTERFACE_DISTRIBUTIONS_ALL	interface_line_attribute1-15
RA_INTERFACE_SALESCREDITS_ALL	interface_line_attribute1-15

To determine which indexes you might need to create, query your Line Transaction Flexfield and note each context of this flexfield and, for each context, the segments that are enabled using interface line attribute columns from the RA_INTERFACE_LINES_ALL table.

You can then create non-unique, concatenated indexes for the same interface line attribute columns in the RA_CUSTOMER_TRX_LINES_ALL and RA_INTERFACE_LINES_ALL tables, and for the same interface header attribute columns in the RA_CUSTOMER_TRX_ALL table.

If you're importing sales credit and accounting information, then create indexes for the same interface line attribute columns in the RA_INTERFACE_SALESCREDITS_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL tables.

Transaction Flexfield Details

You have set up a Transaction Flexfield context that uses INTERFACE_LINE_ATTRIBUTE1-3. In addition, you're populating sales credits in the RA_INTERFACE_SALESCREDITS_ALL table.

For best performance, you should create indexes for these four tables:

• RA_CUSTOMER_TRX_ALL

• RA_CUSTOMER_TRX_LINES_ALL

• RA_INTERFACE_LINES_ALL

• RA_INTERFACE_SALESCREDITS_ALL

The indexes that you create should reference the three enabled segments. For example, an index that you create for the RA_CUSTOMER_TRX_LINES_ALL table might look like this:

CREATE UNIQUE INDEX index_name ON RA_CUSTOMER_TRX_LINES_ALL
(INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

Note: The context column in indexes is normally optional. However, if you use multiple active contexts (three or more), then you should include the context column as the first column in your indexes to improve performance.

If you have only one context defined, then you only need to create one index for each of the four tables. However, if you have multiple contexts defined, you may want to create multiple indexes per table. Use the example in the following table to help you decide how to set up your indexes.

This table shows a Line Transaction Flexfield with three contexts:

Flexfield Context	Attribute Columns assigned to Enabled Segments
Context1	Interface_line_attribute1
Context1	Interface_line_attribute2
Context2	Interface_line_attribute1
Context2	Interface_line_attribute2
Context2	Interface_line_attribute3
Context3	Interface_line_attribute3
Context3	Interface_line_attribute9

Context1 has two attribute columns; Context2 has three attribute columns; and Context3 has two attribute columns. Context1 and Context2 share two attribute columns.

Sharing Indexes

Define the combination of indexes that best meets your needs. As indicated in the previous section, you can create three indexes per table, one for each context, or create just two indexes: one for Context3 and another for Context1. In the latter case, Context2 would use the same index as Context1, because Context1 and Context2 have the same first two attribute columns.

In other words, if you're using the same, or similar, attribute columns for two or more contexts, then you can optionally create a single index instead of creating an index for each context.

Use the following syntax for your Create Index Statement:

$ sqlplus AR username/AR password
SQL> CREATE [UNIQUE] INDEX index ON
          {Table (column1, column2, ...)
           |CLUSTER cluster}
           |INITRANS n] [MAXTRANS n]
           [TABLESPACE tablespace]
           [STORAGE storage]
           [PCTFREE n]
           [NOSORT];