At a given branch (99-22-11), select all customers who are likely to retire soon (i.e. male > 63, female > 58), and who have insurance with the bank. The SQL for this is:
SELECT P_TYPE, C#, SEX, LASTNAME, ADDR_L1, ADDR_L2, CITY, POSTCODE
FROM INSURANCE, CUSTOMER
WHERE CUSTOMER.BRANCH ='99-22-11' AND
CUSTOMER.BRANCH = INSURANCE.BRANCH AND
CUSTOMER.C# = INSURANCE.C# AND
((SEX = 'm' AND AGE BETWEEN 64 AND 65) OR
(SEX = 'f' AND AGE BETWEEN 59 AND 60))Figure 5 shows a logical query tree for this, figure 7 shows an access plan, figure 8 indicates the flow of data around the machine, and the actual table usage is shown in figure 3. The reader can see that the operations Project and Select are done on the Selection engines (refer to VOLUME_ACCESS and VA_DT_COLS).
Figure 4. Sub-partitioning of OLTP partitions
Figure 5. Query tree for example query
Figure 6. DSDL partitioning specification
The partitioning specification for the first seven disks only is shown. The reader should be able to generalise for the other disk partitions.
STORAGE AREA cust_store PAGE SIZE IS 1024 BYTES SIZE IS 10000 PAGES PARTITIONED INTO 18
IF branch BETWEEN '99-00-00' AND '99-33-99' AND SEX = 'm' AND AGE < 25
THEN PLACEMENT SERIAL WITHIN PARTITION 1
IF branch BETWEEN '99-00-00' AND '99-33-99'
AND SEX = 'm' AND AGE > = 25 AND AGE < = 45
THEN PLACEMENT SERIAL WITHIN PARTITION 2
IF branch BETWEEN '99-00-00' AND '99-33-99' AND SEX = 'm' AND AGE > 45
THEN PLACEMENT SERIAL WITHIN PARTITION 3
IF branch BETWEEN '99-00-00' AND '99-33-99' AND SEX = 'f' AND AGE < 25
THEN PLACEMENT SERIAL WITHIN PARTITION 4
IF branch BETWEEN '99-00-00' AND '99-33-99'
AND SEX = 'f' AND AGE > = 25 AND AGE < = 45
THEN PLACEMENT SERIAL WITHIN PARTITION 5
IF branch BETWEEN '99-00-00' AND '99-33-99' AND SEX = 'f' AND AGE > 45
THEN PLACEMENT SERIAL WITHIN PARTITION 6
IF branch BETWEEN '99-34-00' AND '99-66-99' AND SEX = 'm' AND AGE < 25
THEN PLACEMENT SERIAL WITHIN PARTITION 7
The partitions are then allocated to physical disks using the ALLOCATE specification.
The INSURANCE table is also partitioned, but only three partitions corresponding to the three logical branch partitions are created. Each of these is then assigned to a separate disk.
STORAGE AREA insure_store PAGE SIZE 15 1024 BYTES SIZE IS 10000 PAGES PARTITIONED INTO 3
IF branch BETWEEN '99-00-00' AND '99-33-99' THEN PLACEMENT SERIAL WITHIN PARTITION 1
IF branch BETWEEN '99-34-00' AND '99-66-99' THEN PLACEMENT SERIAL WITHIN PARTITION 2
IF branch BETWEEN '99-67-00' AND '99-99-99' THEN PLACEMENT SERIAL WITHIN PARTITION 3
Note that the access plan (figure 7) is based on the partitioning of figure 4. For this reason, the SELECT predicates superficially appear incomplete. This is not the case, as the partitioning forces an implicit predicate. e.g. cust_st[3] (customer_store 3, on logical column 5) contains only rows for male customers.
Figure 7. Access plan for example query
Figure 8. Data flow for example query
The reader can see from this example that by means of suitable partitioning, it is possible to reduce greatly the amount of data that must be scanned in order to answer an MIS query.