The following example demonstrates an index installation, both MDK and ASK, on the partitioned ORDERS table. The indexes are installed on each individual partition, NOT the UNION table.

This same index installation can be used on a non-partitioned table.

MDK Indexes

ORDER_NO
CUSTOMER_NO
ORDER_DATE
STATUS
PRODUCT_NO
QUANTITY
UNIT_PRICE

MDK Composite Indexes

PRODUCTNO_STATUS
PRODUCT_NO
STATUS

PRODUCTNO_QUANTITY
QUANTITY
PRODUCT_NO

ASK and Aggregation Indexes

PROD_STAT_AG!
PRODUCT_NO
STATUS
QUANTITY
UNIT_PRICE

PROD_CUST_STAT_AG!
PRODUCT_NO
CUSTOMER_NO
STATUS
QUANTITY
UNIT_PRICE

The index installation is not limited to these examples. However, these are sufficient to demonstrate how they work together and with the partitioned table.

With this installation, the following queries will be fully optimized.

SELECT CUSTOMER_NO, PRODUCT_NO
FROM ORDERS
WHERE PRODUCT_NO IN ('H672', 'H100', 'HL6P')
AND STATUS = 'BACK'
AND QUANTITY > 10

SELECT PRODUCT_NO, (QUANTITY * UNIT_PRICE) AS 'Total'
FROM ORDERS
WHERE PRODUCT_NO = 'H*'
AND

OmniAccess Partitioning Report
Database: Table: Approach:	Star: Orders: 200 rows split by PRODUCT_NO into 5 equal partitions
Partition Value -----------------	Cardinality -------------	Valid -------	Total % ---------	Valid % ---------
ORDERS01 H672 H100 A940 HL6P MPOP	40 9 9 8 8 6		20.000% 4.500% 4.500% 4.000% 4.000% 4.000%	20.000% 0.000% 0.000% 0.000% 0.000% 0.000%
ORDERS02 G523 DM20 P900 G520 IC9S T750	39 7 7 7 7 6 5		19.500% 3.500% 3.500% 3.500% 3.500% 3.000% 2.500%	20.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000%
ORDERS03 B290 B390 B455 P225 HO30 H400 P224 B190 BRSP	40 5 5 5 5 5 5 4 4 2		20.000% 2.500% 2.500% 2.500% 2.500% 2.500% 2.500% 2.000% 2.000% 1.000%	20.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000%
ORDERS04 H736 N820 N972 PH65 S100 OSER HPMP EDMP DM23 C478 3MTP GEHE	40 4 4 4 4 4 3 3 3 3 3 3 2		20.000% 2.000% 2.000% 2.000% 2.000% 2.000% 1.500% 1.500% 1.500% 1.500% 1.500% 1.500% 1.000%	20.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000%
ORDERS05 P160 L722 HLTC L433 3SAD MTAC 3DST PBSP SCFP CGPP BTCT SSST SWFL AEWP OSTC AQPP GTLC 3RGS 3P23 BMLF PDMP PGMP BMED XPCP S25P SCDR ACPC ADLB AAWP GUOC MMRB	41 3 3 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1		20.500% 1.500% 1.500% 1.000% 1.000% 1.000% 1.000% 1.000% 1.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000%	20.500% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000%
----------------- Total	------------- 200	-------	--------- 100.00%	--------- 100.00%
End of report
---------------- End of file --------------

> view ordenv.src

table	"ORDERS"
as	"select * from ORDERS01 union all select * from ORDERS02 union all select * from ORDERS03 union all select * from ORDERS04 union all select * from ORDERS05"
column "ACCT" column "PRODUCT_NO" column "ORDER_DATE" column "STATUS" column "TAX_STATE" column "SOURCE" column "PMT_METHOD" column "DISCOUNT" column "QUANTITY" column "SALES_TAX" column "AMOUNT" column "TOTAL"	datatype INTEGER datatype CHARACTER datatype OMNIDEX DATE datatype CHARACTER datatype CHARACTER datatype INTEGER datatype INTEGER datatype INTEGER datatype INTEGER datatype FLOAT datatype FLOAT datatype FLOAT	length 4 length 4 length 4 format YYYYMMDD length 2 length 2 length 1 length 1 length 1 length 1 length 4 length 4 length 4
table physical partition by	"ORDERS01" "dat\orders01.dat" "PRODUCT_NO in ('H672','H100','A940','HL6P','MPOP')"
foreign "ACCT" foreign "PRODUCT_NO" foreign "ORDER_DATE" foreign "STATUS" foreign "TAX_STATE" foreign "SOURCE" foreign "PMT_METHOD"	references "PROSPECTS" ("ACCT") references "PRODUCTS" ("PRODUCT_NO") references "DATES" ("DT") references "STATUSES" ("STATUS") references "STATES" ("STATE") references "SOURCES" ("SOURCE") references "PMT_METHODS" ("PMT_METHOD")
table physical partition by	"ORDERS02" "dat\orders02.dat" "PRODUCT_NO in ('G523','DM20','P900','G520','IC9S','T750')"
foreign "ACCT" foreign "PRODUCT_NO" foreign "ORDER_DATE" foreign "STATUS" foreign "TAX_STATE" foreign "SOURCE" foreign "PMT_METHOD"	references "PROSPECTS" ("ACCT") references "PRODUCTS" ("PRODUCT_NO") references "DATES" ("DT") references "STATUSES" ("STATUS") references "STATES" ("STATE") references "SOURCES" ("SOURCE") references "PMT_METHODS" ("PMT_METHOD")
table physical partition by	"ORDERS03" "dat\orders03.dat" "PRODUCT_NO in ('B290','B390','B455','P225', 'HO30','H400','P224','B190','BRSP')"
foreign "ACCT" foreign "PRODUCT_NO" foreign "ORDER_DATE" foreign "STATUS" foreign "TAX_STATE" foreign "SOURCE" foreign "PMT_METHOD"	references "PROSPECTS" ("ACCT") references "PRODUCTS" ("PRODUCT_NO") references "DATES" ("DT") references "STATUSES" ("STATUS") references "STATES" ("STATE") references "SOURCES" ("SOURCE") references "PMT_METHODS" ("PMT_METHOD")
table physical partition by	"ORDERS04" "dat\orders04.dat" "PRODUCT_NO in ('H736','N820','N972','PH65','S100','OSER', 'HPMP','EDMP','DM23','C478','3MTP','GEHE')"
foreign "ACCT" foreign "PRODUCT_NO" foreign "ORDER_DATE" foreign "STATUS" foreign "TAX_STATE" foreign "SOURCE" foreign "PMT_METHOD"	references "PROSPECTS" ("ACCT") references "PRODUCTS" ("PRODUCT_NO") references "DATES" ("DT") references "STATUSES" ("STATUS") references "STATES" ("STATE") references "SOURCES" ("SOURCE") references "PMT_METHODS" ("PMT_METHOD")
table physical partition by	"ORDERS05" "dat\orders05.dat" "PRODUCT_NO in ('P160','L722','HLTC','L433','3SAD','MTAC','3DST', 'PBSP','SCFP','CGPP','BTCT','SSST','SWFL','AEWP', 'OSTC','AQPP','GTLC', '3RGS','3P23','BMLF','PDMP', 'PGMP','BMED','XPCP','S25P','SCDR','ACPC','ADLB', 'AAWP','GUOC','MMRB')"
foreign "ACCT" foreign "PRODUCT_NO" foreign "ORDER_DATE" foreign "STATUS" foreign "TAX_STATE" foreign "SOURCE" foreign "PMT_METHOD"	references "PROSPECTS" ("ACCT") references "PRODUCTS" ("PRODUCT_NO") references "DATES" ("DT") references "STATUSES" ("STATUS") references "STATES" ("STATE") references "SOURCES" ("SOURCE") references "PMT_METHODS" ("PMT_METHOD")

Top