U.S. patent application number 14/272769 was filed with the patent office on 2014-08-28 for hybrid database table stored as both row and column store.
This patent application is currently assigned to SAP AG. The applicant listed for this patent is Chang Bin Song, Chanho Jeong, Sang Kyun Cha, Juchang Lee, Yong Sik Kwon, Yongsik Yoon. Invention is credited to Chang Bin Song, Chanho Jeong, Sang Kyun Cha, Juchang Lee, Yong Sik Kwon, Yongsik Yoon.
Application Number | 20140244628 14/272769 |
Document ID | / |
Family ID | 47178442 |
Filed Date | 2014-08-28 |
United States Patent
Application |
20140244628 |
Kind Code |
A1 |
Yoon; Yongsik ; et
al. |
August 28, 2014 |
Hybrid Database Table Stored as Both Row and Column Store
Abstract
A hybrid database table is stored as both a row and a column
store. One or more techniques may be employed alone or in
combination to enhance performance of the hybrid table by
regulating access to, and/or the size of, the processing-intensive
column store data. For example during an insert operation, the
column store data may be searched for a uniqueness violation only
after certain filtering and/or boundary conditions have been
considered. In another technique, a hybrid table manager may
control movement of data to the column store based upon
considerations such as frequency of access, or underlying business
logic. In still another technique, querying of the hybrid table may
result in a search of the column store data only after an initial
search of row store data fails to return a result.
Inventors: |
Yoon; Yongsik; (Seocho-ku,
KR) ; Jeong; Chanho; (Seocho-ku, KR) ; Lee;
Juchang; (Seocho-ku, KR) ; Bin Song; Chang;
(Seocho-ku, KR) ; Sik Kwon; Yong; (Seocho-ku,
KR) ; Kyun Cha; Sang; (Seocho-ku, KR) |
|
Applicant: |
Name |
City |
State |
Country |
Type |
Yoon; Yongsik
Jeong; Chanho
Lee; Juchang
Bin Song; Chang
Sik Kwon; Yong
Kyun Cha; Sang |
Seocho-ku
Seocho-ku
Seocho-ku
Seocho-ku
Seocho-ku
Seocho-ku |
|
KR
KR
KR
KR
KR
KR |
|
|
Assignee: |
SAP AG
Walldorf
DE
|
Family ID: |
47178442 |
Appl. No.: |
14/272769 |
Filed: |
May 8, 2014 |
Related U.S. Patent Documents
|
|
|
|
|
|
Application
Number |
Filing Date |
Patent Number |
|
|
13334669 |
Dec 22, 2011 |
8768927 |
|
|
14272769 |
|
|
|
|
Current U.S.
Class: |
707/722 |
Current CPC
Class: |
G06F 16/245 20190101;
G06F 16/22 20190101; G06F 16/278 20190101; G06F 16/2365
20190101 |
Class at
Publication: |
707/722 |
International
Class: |
G06F 17/30 20060101
G06F017/30 |
Claims
1. A computer implemented method comprising: a computer storing a
database on a non-transitory computer readable storage medium, the
database comprising a row-oriented portion comprising data stored
using a row-oriented storage model and a column-oriented portion
comprising data stored using s column-oriented storage model; the
computer receiving a query on the database; the computer making a
first search on the row-oriented portion of the database using the
received query; and the computer selectively making a second search
on the column-oriented portion of the database depending on an
outcome of the first search, wherein the computer makes the second
search on the column-oriented portion of the database in response
to a first outcome of the first search, wherein the computer does
not make the second search column-oriented portion of the database
in response to a second outcome of the first search.
2. The computer implemented method of claim 1 wherein in response
to the computer making the second search on the column-oriented
portion of the database, the computer merges results from the first
search on the row-oriented portion of the database and results from
the second search on the column-oriented portion of the
database.
3. The computer implemented method of claim 1 wherein the first
outcome of the first search is that the first search produces no
results and the second outcome of the first search is that the
first search produces results.
4. The computer implemented method of claim 1 wherein the first
outcome of the first search is that a result of the first search
fall within a range of values associated with the column-oriented
portion of the database and the second outcome of the first search
is that the result of the first search falls outside the range of
values.
5. The computer implemented method of claim 1 further comprising
the computer moving data from the row-oriented portion of the
database to the column-oriented portion of the database, the moving
of data being performed in response to statistics relating to
accesses on the row-oriented portion of the database.
6. The computer implemented method of claim 1 further comprising
the computer moving data from the row-oriented portion of the
database to the column-oriented portion of the database, the moving
of data being performed in response to data stored in the
row-oriented portion of the database meeting criteria defined based
on business conditions.
7. A computer system comprising: a computer; and a data storage
system having stored therein a database comprising a row-oriented
portion comprising data stored using a row-oriented storage model
and a column-oriented portion comprising data stored using s
column-oriented storage model, the data storage system further
having stored therein a software program, which, when executed by
the computer, causes the computer to: receive a query on the
database; make a first search on the row-oriented portion of the
database using the received query; and selectively make a second
search on the column-oriented portion of the database depending on
an outcome of the first search, wherein the computer makes the
second search on the column-oriented portion of the database in
response to a first outcome of the first search, wherein the
computer does not make the second search column-oriented portion of
the database in response to a second outcome of the first
search.
8. The computer system of claim 7 wherein in response to making the
second search on the column-oriented portion of the database, the
software program, which, when executed by the computer, further
causes the computer to merge results from the first search on the
row-oriented portion of the database and results from the second
search on the column-oriented portion of the database.
9. The computer system of claim 7 wherein the first outcome of the
first search is that the first search produces no results and the
second outcome of the first search is that the first search
produces results.
10. The computer system of claim 7 wherein the first outcome of the
first search is that a result of the first search fall within a
range of values associated with the column-oriented portion of the
database and the second outcome of the first search is that the
result of the first search falls outside the range of values.
11. The computer system of claim 7 wherein the software program,
which, when executed by the computer, further causes the computer
to move data from the row-oriented portion of the database to the
column-oriented portion of the database, the moving of data being
performed in response to statistics relating to accesses on the
row-oriented portion of the database.
12. The computer system of claim 7 wherein the software program,
which, when executed by the computer, further causes the computer
to move data from the row-oriented portion of the database to the
column-oriented portion of the database, the moving of data being
performed in response to data stored in the row-oriented portion of
the database meeting criteria defined based on business
conditions.
13. A non-transitory computer readable storage medium embodying a
computer program to cause a computer that executes the computer
program to perform a method, the method comprising: storing a
database on a non-transitory computer readable storage medium, the
database comprising a row-oriented portion comprising data stored
using a row-oriented storage model and a column-oriented portion
comprising data stored using s column-oriented storage model;
receiving a query on the database; performing a first search on the
row-oriented portion of the database using the received query; and
selectively performing a second search on the column-oriented
portion of the database depending on an outcome of the first
search, wherein the second search on the column-oriented portion of
the database is performed in response to a first outcome of the
first search, wherein the second search column-oriented portion of
the database is performed in response to a second outcome of the
first search.
14. The non-transitory computer readable storage medium of claim 13
wherein in response to performing the second search on the
column-oriented portion of the database, the computer merges
results from the first search on the row-oriented portion of the
database and results from the second search on the column-oriented
portion of the database.
15. The non-transitory computer readable storage medium of claim 13
wherein the first outcome of the first search is that the first
search produces no results and the second outcome of the first
search is that the first search produces results.
16. The non-transitory computer readable storage medium of claim 13
wherein the first outcome of the first search is that a result of
the first search fall within a range of values associated with the
column-oriented portion of the database and the second outcome of
the first search is that the result of the first search falls
outside the range of values.
17. The non-transitory computer readable storage medium of claim
13, wherein the method further comprises moving data from the
row-oriented portion of the database to the column-oriented portion
of the database, the moving of data being performed in response to
statistics relating to accesses on the row-oriented portion of the
database.
18. The non-transitory computer readable storage medium of claim 13
wherein the method further comprises moving data from the
row-oriented portion of the database to the column-oriented portion
of the database, the moving of data being performed in response to
data stored in the row-oriented portion of the database meeting
criteria defined based on business conditions.
Description
CROSS REFERENCE TO RELATED APPLICATIONS
[0001] This application is a continuation application and pursuant
to 35 U.S.C. .sctn.120 is entitled to and claims the benefit of
earlier filed application U.S. application Ser. No. 13/334,669,
filed Dec. 22, 2011, the content of which is incorporated herein by
reference in its entirety for all purposes.
BACKGROUND
[0002] The present invention relates to storage of data in
databases, and in particular, to the storage of database
information in a hybrid table format.
[0003] Unless otherwise indicated herein, the approaches described
in this section are not prior art to the claims in this application
and are not admitted to be prior art by inclusion in this
section.
[0004] A database is an electronic filing system that stores data
in a structured way. The primary storage structure in a database is
a table. A database may contain multiple tables and each table may
hold information of a specific type. Database tables store and
organize data in horizontal rows and vertical columns. Rows
typically correspond to real-world entities or relationships that
represent individual records in a table. Columns may denote
specific attributes of those entities or relationships, such as
"name," "address" or "phone number." For example, Company X may
have a database containing a "customer" table listing the names,
addresses and phone numbers of its customers. Each row may
represent a single customer and the columns may represent each
customer's name, address and phone number.
[0005] Databases are generally stored in computer memory that is
one-dimensional. Two-dimensional database tables must therefore be
mapped onto a one-dimensional data structure to be stored within a
database. One mapping approach involves storing a table in a
database row-by-row (i.e., a row-oriented storage model). This
approach keeps information about a single entity together. For
example, row-by-row storage may store all information about a first
customer first, then all information about a second customer and so
on. Alternatively, a table may be stored in a database
column-by-column (i.e., a column-oriented storage model). This
approach keeps like attributes of different entities together. For
example, column-by-column storage may store all customer names
first, then all customer addresses and so on.
[0006] Data must generally be accessed from a table in the same
manner that it was stored. That is, conventional computer storage
techniques require dedicated query operators that can access
specific types of storage models. For example, row query operators
are used to process data stored in a database in row-formatted
storage models and column query operators are used to process data
stored in column-formatted storage models.
[0007] Choosing which storage model to use thus often depends on
how data will be used. Row-oriented storage models are commonly
well-suited for transactional queries. The row-store format
emphasizes the database row as the manipulable element, and is
typically used for On-Line Transaction Processing (OLTP) involving
a relatively large number of on-line transactions (rows), with each
transaction characterized by relatively larger data types
(columns).
[0008] By contrast, column-oriented storage models are generally
well-suited for analytical queries. The column-store format
emphasizes the database column as the manipulable element, and is
typically used for On-Line Analytical Processing (OLAP) of a subset
of a total number of transactions (rows) over a fewer number of
data types (columns) that may include aggregations of basic data
types. A database table in the column-store format is typically
used for interrogation and analysis of the raw data for purposes of
problem-solving and planning that form a part of Business
Intelligence (BI) efforts.
[0009] In summary, row store may be useful for retrieving
individual records having many columns with a primary key
condition. Column store may be useful for performing more complex
functions such as aggregation/join over a relatively small number
of columns.
[0010] Accordingly, conventional query processing schemes are bound
to the underlying storage model of the database being queried. In
reality, however, a database having certain data stored in a
column-formatted storage model may be asked to handle a
transactional query relating to that data, or a database having
certain data stored in a row-formatted storage model may be asked
to handle an analytical query relating to that data. For example, a
database having data stored in a row-formatted storage model may
receive a mixed set of queries requiring transactional and
analytical processing of that data.
[0011] Both the row-store and column-store database table formats
offer various benefits. For example, the row-store format offers
ready scalability for data, as it is expected that more and more
transactions will require storage in additional rows. The row-store
table format is, however, relatively memory intensive for analytic
queries (e.g. aggregation, join) as it scans a table vertically,
incurring cache misses as the data is stored horizontally.
[0012] Conversely, the column-store format offers flexibility in
allowing complex manipulation of data involving table joins and
aggregation, as well as relatively low memory consumption by
allowing compression within data types across multiple entries by
dictionary encoding. The column-store database format, however,
typically does not allow ready manipulation of the same volumes of
data as the row-store table.
[0013] Thus, a row-store table is more effective to serve row-wise
record access such as single record selection with primary key
lookup. A column-store table is better to serve column-wise record
access such as single column aggregation. For row-wise record
access, column-store table becomes memory-intensive, because the
data format is organized vertically in column-store so that cache
misses occur while accessing record values horizontally. For
column-wise record access, row-store table becomes memory-intensive
because the data format is organized horizontally in row-store, so
that cache misses occur while reading specific column values.
[0014] Despite the various advantages of the various database table
types, conventionally a table can generally only be in row- or
column-store at any point in time of a business life cycle.
Accordingly, the present disclosure addresses this and other issues
with systems and methods for implementing a hybrid database table
stored as both a row and a column store.
SUMMARY
[0015] A hybrid database table is stored as both a row and a column
store. One or more techniques may be employed alone or in
combination to enhance performance of the hybrid table by
regulating access to, and/or the size of, the processing-intensive
column store data. For example during an insert operation, the
column store data may be searched for a uniqueness violation only
after certain filtering and/or boundary conditions have been
considered. In another technique, a hybrid table manager may
control movement of data to the column store based upon
considerations such as frequency of access, or underlying business
logic. According to still another technique, querying of the hybrid
table may result in a search of the column store data only after an
initial search of row store data fails to return a result.
[0016] An embodiment of a computer-implemented method comprises,
providing in a non-transitory computer readable storage medium, a
database created in an application level language and comprising
row partitioned store and column partitioned store. A new record is
received to be inserted into the database. A row engine is caused
to insert the new record into the row partitioned store. An
optimization is performed based upon additional information to
check for a uniqueness violation only if there are uniqueness
constraints. A result of processing a query to the database
including the new record, is displayed.
[0017] An embodiment of a non-transitory computer readable storage
medium embodying a computer program for performing a method,
comprises a method of receiving a new record to be inserted into a
database created in an application level language in a
non-transitory storage medium and comprising row partitioned store
and column partitioned store. A row engine to is caused insert the
new record into the row partitioned store. An optimization is
performed based upon additional information to check for a
uniqueness violation only if there are uniqueness constraints. A
result of processing a query to the database including the new
record, is displayed.
[0018] An embodiment of a computer system, comprises, one or more
processors and a software program, executable on said computer
system. The software program is configured to receive a new record
to be inserted into a database created in an application level
language in a non-transitory storage medium and comprising row
partitioned store and column partitioned store. The computer
program is configured to cause a row engine to insert the new
record into the row partitioned store, and to perform an
optimization based upon additional information to check for a
uniqueness violation only if there are uniqueness constraints. The
computer program is configured to display a result of processing a
query to the database including the new record.
[0019] In certain embodiments, the optimization comprises
maintaining min/max values of unique columns in the column
partitioned store with a latch-free structure to minimize
contention.
[0020] According to some embodiments, the optimization comprises
maintaining a filter for unique columns. In particular embodiments,
the filter comprises a Bloom filter. In certain embodiments the
filter is scalable.
[0021] Certain embodiments may further comprise updating or
rehashing when data is moved from the row partitioned store to the
column partitioned store.
[0022] The following detailed description and accompanying drawings
provide a better understanding of the nature and advantages of the
present invention.
BRIEF DESCRIPTION OF THE DRAWINGS
[0023] FIG. 1 shows a simplified schematic view of the structure
and operation of a hybrid table according to an embodiment.
[0024] FIG. 2 shows a simplified schematic view of the insertion of
a new record into an embodiment of a hybrid table.
[0025] FIG. 2A shows a simplified view of a process flow for the
insertion of a new record into an embodiment of a hybrid table.
[0026] FIG. 3A plots performance of a Bloom filter as bits/element
vs. number of hash functions.
[0027] FIG. 3B plots memory size versus number of records, for a
scalable Bloom filter.
[0028] FIG. 4 is a bar chart showing throughput in transactions per
second (TPS) during insert handling with different numbers of
threads.
[0029] FIG. 5 shows a simplified view of controlling data movement
in a hybrid table according to an embodiment.
[0030] FIG. 5A shows a simplified view of a process flow for the
movement of records between row partition store and column
partition store in an embodiment of a hybrid table.
[0031] FIG. 6 shows a simplified schematic view of implementation
of an access-based data movement policy based upon page-level
access statistics management.
[0032] FIG. 7 shows a simplified example of a business-aware data
movement policy according to an embodiment.
[0033] FIG. 8 shows a simplified schematic view of a search
operation on a hybrid database according to an embodiment.
[0034] FIG. 8A shows a simplified view of a process flow for
searching an embodiment of a hybrid table.
[0035] FIG. 9 shows a simplified schematic view of a simple search
conducted on a single hybrid table.
[0036] FIG. 10 is a simplified flow diagram showing the resort to
the column search if necessary, followed by merger of the row
search results and column search results.
[0037] FIG. 11 shows a simplified schematic view of a
conversion-based join operation.
[0038] FIG. 12 shows a simplified schematic view of a native mixed
join operation.
[0039] FIG. 13 shows another view of a native mixed join
operation.
[0040] FIG. 14 illustrates hardware of a special purpose computing
machine which may be configured to implement a hybrid database
table.
[0041] FIG. 15 shows an example of a computer system.
[0042] FIG. 16 plots throughput versus number of connections for
search performance on conventional row and column store tables, as
well as a corresponding hybrid table.
DETAILED DESCRIPTION
[0043] Described herein are techniques for providing a hybrid
database table stored as both a row-store and column-store. Certain
techniques, employed alone or in combination, enhance hybrid table
performance by limiting access to, and/or the size of, the
processing-intensive column store data.
[0044] In the following description, for purposes of explanation,
examples and specific details are set forth in order to provide a
thorough understanding of various embodiments. It will be evident,
however, to one skilled in the art that the present invention as
defined by the claims may include some or all of the features in
these examples alone or in combination with other features
described below, and may further include modifications and
equivalents of the features and concepts described herein.
[0045] FIG. 1 shows a simplified schematic view of a hybrid table
according to an embodiment. The hybrid table 100 comprises a first
data store 102 comprising data stored according to an in-memory row
partition. The hybrid table also comprises a second data store 106
comprising data stored according to an in-memory column partition.
As mentioned above, accessing data in the column store orientation
is more processing-intensive than accessing data in the row store
orientation.
[0046] A query processor 108 is in communication with the hybrid
table to perform row searching and column searching, respectively,
utilizing row engine 107 and column engine 109. The row search
result 110 and the column search result 112 are returned to the
query processor, and a merged query search result 114 is returned
to the user.
[0047] The arrow 122 of FIG. 1 shows the function of inserting a
new record into the hybrid table. As described in detail below,
this insert function can be optimized to enhance performance of the
hybrid table.
[0048] FIG. 1 also shows that data in the hybrid table may be moved
from the relatively update-intensive row partition orientation, to
the relatively read-intensive column partition orientation. Data
manager 120 is responsible for this data movement, which under
certain circumstances may take the form of an asynchronous
transactional bulk data move to the column table.
[0049] The asynchronous data movement is designed to exploit
favorable attributes of both row- and column-partition. From a
performance perspective, it may be desirable to place records that
are recently inserted, frequently updated, and/or searched with
primary key condition, in row-partition. It is desirable to place
records that are not frequently updated but sometimes aggregated,
in column-partition.
[0050] From a memory consumption perspective, it may be desirable
to maintain row partition as small as possible, because as
row-partition is not compressed. Thus, the memory consumed for a
same amount of records is usually larger than for
column-partition.
[0051] Accordingly, embodiments of hybrid tables may seek to avoid
sacrificing OLTP performance (maintaining relevant data in row
store), and to improve aggregation performance (maintaining stable,
aged, for-aggregation data, in column store), while maintaining
reasonable memory consumption (e.g. through row/column boundary
management).
[0052] A hybrid table according to embodiments of the present
invention may offer certain potential benefits. In particular, the
presence of the data stored in the row store orientation offers
fast access. Data stored in the row store orientation is amenable
to single table select with search conditions (e.g. exact
match/range). Thus fast "Simple Select" queries may be used for
access to the row partitioned data store. The row partitioned data
store also affords scalable update and search performance.
[0053] Moreover, the presence of the row partitioned data store
allows concurrent workload handling with a lean stack. Performance
of functions such as insert, update, and delete, as well as simple
searching (exact match/range) are possible with relatively low
consumption of system resources. And, execution in the row engine
may be pipelined.
[0054] Regarding the column store data, memory consumption may be
reduced by dictionary encoding of the column table. The column
table also affords rapid OLAP processing by the column engine.
[0055] Embodiments of the present invention may seek to maintain
performance of the hybrid table, by limiting access to, or the size
of, the processing-intensive column store data. One example of this
can occur during an insert operation, as shown in FIG. 2.
[0056] In particular, FIG. 2 shows a simplified schematic view of
the insertion of a new record into a hybrid table 200. First, a new
record 202 is inserted into unique row store (RS) indexes 204
associated with the in-memory row partition data store 206.
[0057] According to particular embodiments, before resorting to
search each value in the column partitioned data store 208 (a
processing-intensive prospect), in order to check for uniqueness
violations the hybrid table first performs an optimization 209
based on additional information.
[0058] FIG. 2A shows a simplified view of a process flow 250 for
the insertion of a new record into an embodiment of a hybrid table.
A first step 252 comprises providing in a non-transitory computer
readable storage medium, a database created in an application level
language and comprising row partitioned store and column
partitioned store. A second step 252 comprises receiving a new
record to be inserted into the database. A third step 256 comprises
causing a row engine to insert the new record into the row
partitioned store according to a row store index. A fourth step 258
comprises performing an optimization based upon additional
information to check for a uniqueness violation. A fifth step 260
comprises checking uniqueness violation in the column partitioned
store, only if warranted by the optimization. A sixth step 262
comprises displaying a result of processing a query to the database
including the new record.
[0059] A first optimization approach is to maintain min/max values
of unique columns on column partition with latch-free structure to
minimize contention. This approach may possibly result in many
false hits for multi-column unique keys.
[0060] A second optimization approach is to maintain a filter (such
as a Bloom filter) for unique columns. This filtering approach 210,
is beneficial in that it requires updating or rehashing only when
data is moved from the row store orientation to the data store
orientation.
[0061] In particular, a Bloom filter is a data structure to
effectively filter out any element that does not belong to a set.
The Bloom filter comprises a bit vector and a set of hash
functions. If the Bloom filter returns FALSE, then the element is
definitely not a member of the set. If the Bloom filter returns
TRUE, then the element is probably in the set.
[0062] FIG. 3A plots performance of a Bloom filter as bits/element
vs. number of hash functions, with a False Positive Rate (FPR) of
0.1. The FPR depends on the number of hash functions and the number
of element/(size of bit vector).
[0063] In certain embodiments, hybrid table performance during
insert handing may be enhanced through the use of a Bloom filter.
In particular, values in the column partition data store need be
searched only if the Bloom filter returns TRUE.
[0064] Use of a standard Bloom filter for this task may consume
large amounts of memory.
[0065] For example, a standard Bloom filter needs about 60 MB to
keep FPR.ltoreq.0.1 with 100 million records.
[0066] Accordingly, certain embodiments may employ a Scalable Bloom
Filter (SBF) in order to perform optimization of insert handing to
a hybrid table. A SBF is a variant of the Bloom filter that uses
space proportional to the number of stored elements. The query time
for a SBF obeys the following relation:
Query time=O(k log n), where:
[0067] k=number of hash functions; and
[0068] n=number of records.
FIG. 3B plots memory size versus number of records, for a SBF
having a FPR=0.1 and k=3.
[0069] Optimized insert handling utilizing a SBF, can result in
enhanced performance of a hybrid table. This is discussed in
connection with the following example.
EXAMPLE
[0070] A data population of 800,000 records was configured in six
(6) different table types: row oriented (row, A); column oriented
(column, B); hybrid without filtering and all 800,000 records in
row store (C); hybrid without filtering and all 800,000 records in
column store (D); hybrid with SBF and all 800,000 records in row
store (E); and hybrid with SBF and all 800,000 records in column
store (F).
[0071] FIG. 4 is a bar graph showing throughput in transactions per
second (TPS) during insert handling with different numbers of
threads. The performance of a hybrid table can be gauged by
comparison with a table having data stored in a row orientation
(the least processing-intensive format).
[0072] FIG. 4 indicates that without filtering (C, D), the insert
handing performance of the hybrid table is only about 1/3 that of
the row table (A). By contrast where the SBF is used (E, F), the
hybrid table shows similar insert handing performance as a row
table (A), notwithstanding the fact that the populated data of the
hybrid table is located in the column partitioned data store. This
represents a significant enhancement, rendering performance of the
hybrid database table akin to that of the conventional row-store
approach.
[0073] While the above discussion has focused upon the use of a
scalable Bloom filter for optimization of hybrid table performance,
the present invention is not limited to this particular filter
type. Different types of index variations for fast value existence
checking may be employed. Examples include but are not limited to
hash index, b+-tree index, csb+-tree index, and others.
[0074] Another technique which may be employed to enhance
performance of a hybrid table, is to selectively move records to
and from the column partitioned data store in order to control its
size. FIG. 5 shows a simplified view of controlling data movement
in a hybrid table according to an embodiment.
[0075] In particular, a hybrid table manager 500 is responsible for
controlling the movement of data from an in-memory row partition
store 502, to in-memory column partition store 504, according to a
data movement policy. By intelligently partitioning data, selective
data movement may allow fast OLTP processing avoiding column
partition access.
[0076] FIG. 5A shows a simplified view of a process flow 550 for
the movement of records between row partition store and column
partition store in an embodiment of a hybrid table. A first step
552 comprises providing in a non-transitory computer readable
storage medium, a database created in an application level language
and comprising a row partitioned store and a column partitioned
store. A second step 554 comprises providing a data movement
manager configured to execute a data move from the row partitioned
store to the column partitioned store according to a data movement
policy. A third step 556 comprises causing the data movement
manager to automatically move a record from the row partitioned
store to the column partitioned store upon satisfaction of a
condition of the data movement policy. In an optional fourth step
558, a search result of the row partitioned store is returned to
the user.
[0077] One example of a data movement policy is based upon access.
As this data movement policy does not rely upon knowledge of
business rules (see below), it may serve as the default.
[0078] According to an access-based data movement policy, the
hybrid table manager moves some portion of records on the row
partition to the column partition, when the number of records on
row partition exceeds a defined threshold. This access-based data
movement can be based upon statistics maintained for accessed
records, such that when a movement is triggered those records
having a lower frequency of access are moved. Access statistics may
be reset whenever a data movement is finished.
[0079] FIG. 6 shows a simplified schematic view of implementation
of an access-based data movement policy 600 based upon page-level
access statistics management. Each of a plurality of pages 602 in a
row partition store 604 include an ongoing access count 606. Based
upon relatively low numbers of this access count, pages having a
least access frequency can be moved out to the column partition
data store.
[0080] In certain embodiments, frequently accessed records can
possibly be moved out of the column partitioned store due to coarse
granular access statistics management. For example particular some
embodiments could perform page level movement instead of record
level management, where a page includes multiple records, some of
which are frequently accessed whereas others are not. Such an
approach is possible, although an unintended data move may occur
for frequently-accessed records.
[0081] An alternative data movement policy may be based upon
business concepts instead of access frequency. Such a data movement
policy may offer the best results when business semantics are
known.
[0082] According to certain embodiments, business-aware data
movement can take the form of condition-based movement. In such an
approach, records that satisfy certain business conditions can be
moved out to the column partitioned store.
[0083] In other embodiments, business-aware data movement can take
the form of reference-based movement. In such approaches, records
are moved out to the column partitioned store when related records
(in referenced table) are moved out.
[0084] FIG. 7 shows a simplified embodiment of a business-aware
data movement policy 700. In this example, closed sales documents
are moved to the column partitioned data store.
[0085] This in the example of FIG. 7, for the sales document table
VBAK, if the value of GBSTK column is `C`, then it means closed
document. So, the move out condition can be specified as GBSTK=`C`
to move out only closed documents. Closed-document-related records
in SD item (VBAP) table and Document header (VBUK) table, can be
moved out accordingly by specifying a relationship between tables
as move out condition. Here, MANDT and VBELN columns are used to
specify this relationship.
[0086] Performance of a hybrid table may also be enhanced through
optimization of the search function. FIG. 9 shows a simplified
schematic view of a simple search conducted on a single hybrid
table. Specifically, as part of a project a query 900 is posed to
the hybrid table 902. The union 904 of the search results of the
row store (RS) 906 and column store (CS) 908, are returned to the
user.
[0087] According to particular embodiments of the present
invention, information such as boundary conditions can be
referenced to control access to the column stored data, thereby
minimizing column store access for simple queries. FIG. 8 shows a
simplified schematic view of a search operation on a hybrid
database 800 according to an embodiment.
[0088] As described further in detail below, a search 802 can
initially be performed on the in-memory row partitioned data store
804. Only once the result of that initial search 802 has been
returned, is a decision made whether or not to conduct a further,
search 806 of the values stored in the in-memory column partition
808.
[0089] FIG. 8A shows a simplified view of a process flow 850 for
searching an embodiment of a hybrid table. A first step 852
comprises providing in a non-transitory computer readable storage
medium, a database created in an application level language and
comprising a row partitioned store and a column partitioned store.
A second step 854 comprises receiving a query from a user. A third
step 856 comprises searching the row partitioned store based upon
the query. A fourth step 858 comprises performing an optimization
based upon a search of the row partitioned store. A fifth step 860
comprises searching the column partitioned store based upon the
query, only if warranted by the optimization. In sixth step 862, a
search result is returned to the user based on the query.
[0090] If warranted by the optimization, search of the column store
data can occur in the background, transparent to the user. For
example searches involving a primary key (PK) or a unique-key can
be run initially only on the less processing-intensive row store
data. If a result is returned from this initial searching, then
there is no need to resort to the processing-intensive task of also
searching the column store data. The simplified flow diagram of
FIG. 10 shows an embodiment of a hybrid database table search 1000,
with resort to the column search 1002, followed by merger 1004 of
the row search results 1006 and column search results, if
necessary.
[0091] Filtering according to boundary conditions can also be
employed as optimization technique in hybrid table searching. For
example, to control access to the column store data, a column
dictionary can maintain a minimum and maximum value of each column
partition. Searching of the actual column store data could then be
limited to only the partition that satisfies a filter condition
(e.g. a search condition). A boundary of column partition may be
maintained to allow search optimization (limiting column partition
access) if a given filter condition does not require column
partition access.
[0092] FIG. 16 plots throughput (in threads per second--TPS) versus
number of connections, to evaluate performance of a PK exact match
search conducted on conventional row and column store tables, and
also conducted on a corresponding hybrid table according to an
embodiment. FIG. 16 shows that searching a hybrid table with the PK
pruning optimization, results in a throughput for the hybrid table
that is higher than for the conventional column store table.
[0093] More complex searching may be appropriate over multiple
hybrid database tables. In particular, such complex searching may
involve table join operations.
[0094] One technique which may be used for complex searching of
multiple hybrid tables, is a conversion-based join. Such a
conversion-based join is discussed in detail in U.S. patent
application Ser. No. 12/982,673 titled "Processing Database Queries
Using Format Conversion", filed Dec. 30, 2010 and incorporated by
reference in its entirety herein for all purposes.
[0095] FIG. 11 shows a simplified schematic view of a
conversion-based join operation 1100. In particular, this approach
leverages a column join 1102 for a split table. The row table
serves as a partition. This approach employs on-the-fly dictionary
generation, and dictionary caching.
[0096] An alternative technique which may be used for complex
searching of multiple hybrid tables, is a mixed join operation
which accesses data stored in its native row- or column-store
orientation. Such a native mixed join approach is discussed in
detail in U.S. patent application Ser. No. 13/323,530 titled "Mixed
Join of Row and Column Database Tables in Native Orientation" filed
Dec. 12, 2011, and incorporated by reference in its entirety herein
for all purposes.
[0097] FIG. 12 shows a simplified schematic view of a native mixed
join operation 1200. This approach utilizes a column dictionary as
an index. Pruning of the join operation is achieved based upon
filtering using the column dictionary index as a data boundary.
[0098] FIG. 13 shows another view of a native mixed join operation
1300, which uses the column dictionary as an index. First, records
are iterated in the row table. The next step involves column
dictionary lookup for each join condition. In the third step,
matched records are returned.
[0099] By employing column dictionary lookup while iterating the
row table according to a native mixed join approach, no result
materialization is achieved. This also allows pipelined execution
using the row identifier.
[0100] For multi-column join handling, a full key match may be
achieved if a concatenated attribute or an external key exist.
Column-by-column matching is possible.
[0101] FIG. 14 illustrates hardware of a special purpose computing
machine which may be configured to implement a hybrid database
table according to certain embodiments.
[0102] In particular, computer system 1400 comprises a processor
1402 that is in electronic communication with a non-transitory
computer-readable storage medium 1403. This computer-readable
storage medium has stored thereon code 1405 corresponding to the
in-memory row partition. Code 1404 corresponds to an in-memory
column partition, both of which may be accessed as part of the
hybrid table according to embodiments as has been described above.
The system may further comprise code corresponding to the data
movement manager, as has been shown and described above.
[0103] The apparatuses, methods, and techniques described herein
may be implemented as a computer program (software) executing on
one or more computers. The computer program may further be stored
on a non-transitory computer readable medium. The non-transitory
computer readable medium may include instructions for performing
the processes described.
[0104] The computer system may comprise a software server. A number
of software servers together may form a cluster, or logical network
of computer systems programmed with software programs that
communicate with each other and work together to process
requests.
[0105] An example computer system 1510 is illustrated in FIG. 15.
Computer system 1510 includes a bus 1505 or other communication
mechanism for communicating information, and a processor 1501
coupled with bus 1505 for processing information.
[0106] Computer system 1510 also includes a memory 1502 coupled to
bus 1505 for storing information and instructions to be executed by
processor 1501, including information and instructions for
performing the techniques described above, for example. This memory
may also be used for storing variables or other intermediate
information during execution of instructions to be executed by
processor 1501. Possible implementations of this memory may be, but
are not limited to, random access memory (RAM), read only memory
(ROM), or both.
[0107] A storage device 1503 is also provided for storing
information and instructions. Common forms of storage devices
include, for example, a hard drive, a magnetic disk, an optical
disk, a CD-ROM, a DVD, a flash memory, a USB memory card, or any
other medium from which a computer can read.
[0108] Storage device 1503 may include source code, binary code, or
software files for performing the techniques above, for example.
Storage device and memory are both examples of computer readable
media.
[0109] Computer system 1510 may be coupled via bus 1505 to a
display 1512, such as a cathode ray tube (CRT) or liquid crystal
display (LCD), for displaying information to a computer user. An
input device 1511 such as a keyboard and/or mouse is coupled to bus
1505 for communicating information and command selections from the
user to processor 1501. The combination of these components allows
the user to communicate with the system. In some systems, bus 1505
may be divided into multiple specialized buses.
[0110] Computer system 1510 also includes a network interface 1504
coupled with bus 1505. Network interface 1504 may provide two-way
data communication between computer system 1510 and the local
network 1520. The network interface 1504 may be a digital
subscriber line (DSL) or a modem to provide data communication
connection over a telephone line, for example. Another example of
the network interface is a local area network (LAN) card to provide
a data communication connection to a compatible LAN. Wireless links
are another example. In any such implementation, network interface
1404 sends and receives electrical, electromagnetic, or optical
signals that carry digital data streams representing various types
of information.
[0111] Computer system 1510 can send and receive information,
including messages or other interface actions, through the network
interface 1504 across a local network 1520, an Intranet, or the
Internet 1530. For a local network, computer system 1510 may
communicate with a plurality of other computer machines, such as
server 1515. Accordingly, computer system 1510 and server computer
systems represented by server 1515 may form a cloud computing
network, which may be programmed with processes described
herein.
[0112] In an example involving the Internet, software components or
services may reside on multiple different computer systems 1510 or
servers 1531-1535 across the network. The processes described above
may be implemented on one or more servers, for example. A server
1531 may transmit actions or messages from one component, through
Internet 1530, local network 1520, and network interface 1504 to a
component on computer system 1510. The software components and
processes described above may be implemented on any computer system
and send and/or receive information across a network, for
example.
[0113] The above description illustrates various embodiments of the
present invention along with examples of how aspects of the present
invention may be implemented. The above examples and embodiments
should not be deemed to be the only embodiments, and are presented
to illustrate the flexibility and advantages of the present
invention as defined by the following claims. Based on the above
disclosure and the following claims, other arrangements,
embodiments, implementations and equivalents will be evident to
those skilled in the art and may be employed without departing from
the spirit and scope of the invention as defined by the claims.
* * * * *