Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-01 |
|
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". |
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
.
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
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)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
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)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
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
.
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
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:
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
|
![]() Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|