Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

To reuse query result cache for complex query in database

IP.com Disclosure Number: IPCOM000248215D
Publication Date: 2016-Nov-10
Document File: 8 page(s) / 197K

Publishing Venue

The IP.com Prior Art Database

Abstract

This disclosure presents a method to enhance the result cache for database, By analysis the exists result cache and parser the query statement when complex query is executed, and select the one that best property based on the result cache statistical information, then provide advice to influence the optimizer to reuse exists result cache which generate for query statements is not match exactly, to instead of retrieve the data from data block . By this way, database reduced memory usage of the result cache and helps improve response times of queries.

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

Page 01 of 8

To reuse query result cache for complex query in database

Database can benefits from use result cache technology to retrieves the query result from memory instead of executing the queryagain. It will decreasing response time for caching are queries that access a high number of rows but return a small number, such as those in a data warehouse system.

In a real-world situation, different query need different cache area to store the result, we need hold a collection of different results to improve query performance that will consume a large amount of memory. For example: Query A is executed and generate a result cache "RCA" in memory. Another query B(different query statement,the SQL texts is not completely match Query A) search results is a subset of query A, the database will not reuse the cached result "RCA", database still need to get the metadata from data block, there is no good approach to support reuse the result cache for complex query in database.

In this disclosure, a new method to enhance the result cache for database: when a complex query is executed, the SQL parser will disassembling the input query statement into result columns and query conditions. Then compare these values with result cache statistical information that collected when generating result cache. If there is a match result cache, we will provide advice to influence the optimizer to reuse exists result cache instead of retrieve the data from data block. By this way, database reduced memory usage of the result cache and helps improve response times of queries.

1



Page 02 of 8


1). The system architecture:

The present situation:


Database will not reuse the exists result cache when the query statement is not match exactly, (including the query criteria and selected columns) the database engine need to execute the whole process to generate explain and getting the data block from physical disks. It will consume IO and CPU resource.

2



Page 03 of 8

New architecture:


We will provide advice to reuse exists result cache for those query need t...