Browse Prior Art Database

%cc% Method and Apparatus of Materialized Table Optimization in SaaS Environment

IP.com Disclosure Number: IPCOM000198079D
Publication Date: 2010-Jul-26
Document File: 3 page(s) / 89K

Publishing Venue

The IP.com Prior Art Database

Abstract

MQT stands for Materialized Query Table, it is a standard technique in data warehousing, also known as aggregate tables and aggregate (re-)routing, or alternatively, as materialized views. The key idea is to cache query result and optimize query automatically and transparently to improve performance. In SaaS environment, the traditional MQT creation approach has following problems: 1) Redundant data in MQT without considering isolation and independence of tenants’ data. It results in database space waste. 2) Some tenants' queries are always matched, and some tenants' queries are seldomly matched. It results in unsatisfied tenant SLA, and also is unfair among tenants. 3) During MQT maintenance, all queries will be directed to base table. It means a MQT maintenance caused by a tenant’s data change will effect many other tenants’query.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 57% of the total text.

Page 1 of 3

%cc% Method and Apparatus of Materialized Table Optimization in SaaS Environment

The MQT generation method is described as below steps:

 Sql set
of Tenant A

 Sql set
of Tenant B

 Sql set
of Tenant B

0. Collect user queries and group by tenants, for example, through a jdbc wrapper.

Sql analyzer, like DB2 advisor

Sql analyzer, like DB2 advisor

Sql analyzer, like DB2 advisor

1. Feed tenant grouped query to sql analyzer, to generate tenant specific MQTs, for example, use DB2 Advisor.

MQT for tenant A MQT for tenant C

MQT for tenant B

MQT 1 for tenant
A and C MQT 2 for tenant B

2. Merge same or similar MQT among tenants to create new MQT, in order to reduce

MQT amount and overhead

MQT 1 for tenant
A and C MQT 2 for tenant B

3. Create MQT and staging tables, using system maintained, and refresh deferred

4. Update MQT meta data,

MQT meta data

In step1, tenant specific MQTs are generated,

we illustrated it using the following scenario.

There is sales service that has a SalesOrder table including sales data, and it provides a stat function to aggregate sales data. Three tenants (A, B, C) have different query patterns, so each has a recommended MQT sql.

S ales O rder

ID : Longt
Cus tom erID : Long P artnerID : Long Location : S tring CreateDate : Date TotalP ric e : Double Creator : S tring
S tatus : Integer

Tenant A usually uses the query (total 100 tim es):
Select locatio n, partnerid , su m (totalprice) from S alesO rder W here partnerid =? and tenan tid ='A'

G rou p b y location, p artnerid

Tenant B usually uses the query (total 200 tim es):
Select m o nth (C reateD ate), su m (to talprice) fro m S alesO rder W here tenan tid ='B '

G rou p b y m on th(createD ate)

Tenant C usually uses the query (total 500 tim es): Select locatio n, sum (totalprice) fro m SalesO rder W here tenan tid ='C '

G rou p b y location, p artnerid

,

In step2, if there are several tenants' recommended MQTs that have the same or similar sql (

justified

by analyzing sql statement, the 'select', 'from', 'group' clauses), for example, the tenant A and C in

1

[This page contains 24 pictures or other non-text objects]

Page 2 of 3

above scenario. These MQTs will be merged in order to reduce total MQT amount and database overhead. Then MQT commands are generated, a sample command could be as follo...