A
Schemas Used in Performance Examples

The following tables are used in various examples in this book. The statistics are from representative systems.

Note: These schemas are used in examples in Chapter 9, "Using EXPLAIN PLAN".

PER_ALL_PEOPLE_F

This table stores data for employees on the system. For large corporations, it is common to have 10,000 to 30,000 rows in this table. The unique key is a concatenated index, but person_id by itself is quite selective also. Other selective columns are employee_number and full_name.

Indexes on the Table

Unique Index Name           Column Name
------ -------------------- -------------------------
NO     PER_PEOPLE_F_FK1     BUSINESS_GROUP_ID
NO     PER_PEOPLE_F_FK2     PERSON_TYPE_ID
NO     PER_PEOPLE_F_N50     LAST_NAME
NO     PER_PEOPLE_F_N51     EMPLOYEE_NUMBER
NO     PER_PEOPLE_F_N52     APPLICANT_NUMBER
NO     PER_PEOPLE_F_N53     NATIONAL_IDENTIFIER
NO     PER_PEOPLE_F_N54     FULL_NAME
YES    PER_PEOPLE_F_PK      PERSON_ID
                            EFFECTIVE_START_DATE
                            EFFECTIVE_END_DATE

RA_CUSTOMERS

This table has a row for every customer in the system. For large companies, this table has several hundred thousand rows. The primary key is customer_id. Other selective columns are the following:

• Customer_number
• Customer_name
• Orig_system_reference (tracks the customer identifier for customers imported from another system)

Indexes on the Table

Unique Index Name           Column Name
------ -------------------- ------------------------------
NO     RA_CUSTOMERS_N1      CUSTOMER_NAME
NO     RA_CUSTOMERS_N2      CREATION_DATE
NO     RA_CUSTOMERS_N3      CUSTOMER_KEY
NO     RA_CUSTOMERS_N4      JGZZ_FISCAL_CODE
YES    RA_CUSTOMERS_U1      CUSTOMER_ID
YES    RA_CUSTOMERS_U2      ORIG_SYSTEM_REFERENCE
YES    RA_CUSTOMERS_U3      CUSTOMER_NUMBER

SO_HEADERS_ALL / SO_HEADERS

This table has a row for every order on the system. For large companies, it is common to have several million rows in this table. The primary key is header_id, and there is another unique key on (order_number, order_type_id). Other selective columns are the following:

• customer_id (the customer placing the order)
• purchase_order_num (the purchase order for billing)
• original_system_reference (tracks the order identifier for orders imported from other systems)

Indexes on the Table

Unique Index Name           Column Name
------ -------------------- ---------------------------
NO     SO_HEADERS_N1        CUSTOMER_ID
NO     SO_HEADERS_N10       WH_UPDATE_DATE
NO     SO_HEADERS_N2        OPEN_FLAG
NO     SO_HEADERS_N3        PURCHASE_ORDER_NUM
NO     SO_HEADERS_N4        INVOICE_TO_SITE_USE_ID
NO     SO_HEADERS_N5        ORIGINAL_SYSTEM_REFERENCE
NO     SO_HEADERS_N6        S1
NO     SO_HEADERS_N7        S4
NO     SO_HEADERS_N8        S6
NO     SO_HEADERS_N9        ORIGINAL_SYSTEM_REFERENCE
                            ORIGINAL_SYSTEM_SOURCE_CODE
YES    SO_HEADERS_U1        HEADER_ID
YES    SO_HEADERS_U2        ORDER_NUMBER
                            ORDER_TYPE_ID

MTL_SYSTEM_ITEMS

This table is the parts master for so_lines_all. It has a row for every part in every organization. The primary key is inventory_item_id, organization_id.

Indexes on the Table

Unique Index Name           Column Name
------ -------------------- ----------------------------
NO     MTL_SYSTEM_ITEMS_N1  ORGANIZATION_ID
                            SEGMENT1
NO     MTL_SYSTEM_ITEMS_N2  ORGANIZATION_ID
                            DESCRIPTION
NO     MTL_SYSTEM_ITEMS_N3  INVENTORY_ITEM_STATUS_CODE
NO     MTL_SYSTEM_ITEMS_N4  ORGANIZATION_ID
                            AUTO_CREATED_CONFIG_FLAG
NO     MTL_SYSTEM_ITEMS_N5  WH_UPDATE_DATE
NO     MTL_SYSTEM_ITEMS_N6  ITEM_CATALOG_GROUP_ID
                            CATALOG_STATUS_FLAG
NO     MTL_SYSTEM_ITEMS_N7  PRODUCT_FAMILY_ITEM_ID
                            ORGANIZATION_ID
NO     MTL_SYSTEM_ITEMS_N8  SEGMENT1
                            SEGMENT2
                            SEGMENT3
YES    MTL_SYSTEM_ITEMS_U1  INVENTORY_ITEM_ID
                            ORGANIZATION_ID 

SO_LINES_ALL / SO_LINES

This table has a row for every order line on the system. It joins to the so_headers_all table using header_id. Because an order has 10 to 12 lines, this table is 10 to 12 times the rows in so_headers_all. The primary key is line_id. Some other selective columns are the following:

• header_id
• parent_line_id
• service_parent_line_id
• original_system_reference

Indexes on the Table

Unique Index Name           Column Name
------ -------------------- -------------------------
NO     SO_LINES_N1          HEADER_ID
NO     SO_LINES_N10         S5
NO     SO_LINES_N11         S6
NO     SO_LINES_N12         S8
NO     SO_LINES_N13         S9
NO     SO_LINES_N14         S28
NO     SO_LINES_N15         S29
NO     SO_LINES_N16         S30
NO     SO_LINES_N17         PARENT_LINE_ID
NO     SO_LINES_N18         SHIPMENT_SCHEDULE_LINE_ID
NO     SO_LINES_N19         ATO_LINE_ID
NO     SO_LINES_N2          LINK_TO_LINE_ID
NO     SO_LINES_N20         SERVICE_PARENT_LINE_ID
NO     SO_LINES_N21         SHIP_TO_SITE_USE_ID
NO     SO_LINES_N22         SOURCE_LINE_ID
NO     SO_LINES_N23         ORIGINAL_SYSTEM_LINE_REFERENCE
NO     SO_LINES_N24         RETURN_REFERENCE_ID
NO     SO_LINES_N25         S27
NO     SO_LINES_N26         CREDIT_INVOICE_LINE_ID
NO     SO_LINES_N27         S25
NO     SO_LINES_N28         WH_UPDATE_DATE
NO     SO_LINES_N29         DEMAND_STREAM_ID
NO     SO_LINES_N3          OPEN_FLAG
NO     SO_LINES_N4          COMMITMENT_ID
NO     SO_LINES_N5          INVENTORY_ITEM_ID
NO     SO_LINES_N6          REQUEST_ID
NO     SO_LINES_N7          S2
NO     SO_LINES_N8          S3
NO     SO_LINES_N9          S4
YES    SO_LINES_U1          LINE_ID