The current definition of sales_table does not use row partitioning.
CREATE TABLE sale_stable ( product_code CHARACTER(8), sales_date DATE, agent_id CHARACTER(8), quantity_sold INTEGER, product_description VARCHAR(50)) PRIMARY INDEX (product_code,sales_date,agent_id);
The DBA has been told to speed up the ad hoc queries and agent analysis queries. The DBA considers two possible optimizations, neither of which uses row partitioning.
- Define either a UPI or a join index on the transaction date column.
The DBA then sets up tests for both scenarios. Unfortunately, the EXPLAIN reports show that the optimizer finds neither index to be selective enough to improve performance over a full-table scan, and does not use them.
- Split the table into 25 separate tables, each containing transactions for a calendar month, and then define a view that UNIONs all the tables. This view is intended to be used by the applications that analyze 24 months of sales history.
After analysis, the DBA concludes that this solution may speed up the targeted queries, but adds complexity for end users, who now have to understand the view structure and change the table names in their queries, code more complicated UNION statements, and select appropriate date and product code ranges.
The requirement to know the table name also applies to the short-running single-AMP queries that specify primary index values. This proposed solution also complicates the nightly load jobs, especially in the first days of a month when a small number of transactions are from the prior month, complicating the successful archive strategy. The DBA ultimately rejects this alternative as too complicated and error-prone.
With these negative results, the DBA next considers redefining the sales table with row partitioning.