Browse Prior Art Database

A method of query Optimization with column-based Materialized Query Table

IP.com Disclosure Number: IPCOM000246514D
Publication Date: 2016-Jun-15
Document File: 5 page(s) / 120K

Publishing Venue

The IP.com Prior Art Database

Abstract

The disclosure propose a idea about create the Materialized Query Table stored as a columnar table, we define new Data Definition Language (DDL) to define Materialized Query Table with row-based or column-based table ,alter the existing Materialized Query Table and Database engine process Materialized Query Table during runtime and implement data sync. It will give the better query performance, specially for Materialized Query Table with big size, save the space storage,transparent for application and users , and faster Joint between columnar and row-based tables.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 54% of the total text.

Page 01 of 5

A method of query Optimization with column-based Materialized Query Table

Background:

Materialized Query Table is generated to optimize query performance in On-Line Analytical Processing(OLAP) system for the reason that it can reduce the need for expensive joins, sorts, and aggregation of base data. However, as data grows, it becomes a huge table, which mayheavily impact the query optimization effect, besides, it also costs extra storage space.

In a typical scenario, Telecom or bank usually has big data volume of transaction records every day, assuming 200,000,000 records per day. Based on these daily records, kinds of application can summarize and analyze customer's behavior, such as monthly records, although considering Materialized Query Table for query optimization, the table size is too large.

Fact table: 200,000,000 records

Materialized Query Table:10,000,000 records

Problem:
1. Currently, there's only one way to store Materialized Query Table, that is , based on row-organized table.

2. When the table size of Materialized Query Table becomes too large, query performance is not good enough.

3. Large Materialized Query Table consumes storage space.

The question is how to improve Materialized Query Table to get the best performance and save the space storage when the data is huge.

Main idea:
Create a Materialized Query Table stored as a columnar table
Claim:
1. New Data Definition Language (DDL) to define Materialized Query Table with row-based or column-based table.
2. New DDL to alte...