# US 2015/0106382 A1 · Tables With Unlimited Number Of Sparse Columns And Techniques For An Efficient Implementation

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2015/0106382 A1
- Document ID: US20150106382A1
- Status: Published application
- Kind: A1
- Application number: 14/052,622
- Filed: 2013-10-11
- Published: 2015-04-16
- Earliest claimed: 2013-10-11
- Assignees: Oracle International Corporation
- Applicants: Oracle International Corporation
- Inventors: Zhen Hua Liu; Dieter Gawlick
- Web page: https://patentgrep.com/patent/US20150106382A1

## CPC

- G06F 16/221: {Column-oriented storage; Management thereof}
- G06F 16/2282: {Tablespace storage structures; Management thereof}
- G06F 16/245: Query processing
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

A method and apparatus queries a table in a database where the table includes at least one column declared to be sparse. A binary large object may be used to store the sparse column data. The object includes a column-id and column-value pair for each non-null value. To answer a query with a constraint on a sparse column, the object is searched for one or more column ids to obtain the column values. Rows whose column values match a constraint are returned. In another embodiment, an internal table is used. Each tuple in the internal table has a column id and a value array indexed by an ordinal row number. To answer a query with a constraint on a sparse column, the column value in the internal table is found and matched against the constraint. If the match is successful, the index of the column value in the internal table is returned.

## Claims

### Claim 1

A method, the method comprising steps of:
computing a query requiring sparse columns of a first table that are declared to be sparse, wherein each sparse column of said sparse columns is associated with a column id of a plurality of column ids, wherein sparse column values of said each sparse column are each stored in association with the respective column id as a column-id-value pair in a composite column of said first table; and
wherein computing a query comprises:
for each first table row of first table rows in said first table:
(a) using the respective column ids of the sparse columns, locating, in the composite column for said each first table row, one or more column-id-value pairs of the sparse columns;
(b) retrieving one or more respective sparse column values of said one or more column-id-value pairs; and
wherein the method is performed by one or more computing devices.

### Claim 2 (depends on 1)

The method of claim 1, the steps further including:
receiving a DDL statement declaring said sparse columns as sparse;
in response to receiving said DDL statement,
creating said composite column; and
storing said sparse column values for said sparse columns in said composite column.

### Claim 3 (depends on 1)

The method of claim 1, wherein computing the query further comprises:
executing a query execution plan comprising one or more plan operators, wherein
executing said one or plan operators causes performance of steps (a) and (b).

### Claim 4 (depends on 1)

The method of claim 1, wherein said composite column is a BLOB column.

### Claim 5 (depends on 1)

The method of claim 1, wherein for a particular sparse column of said sparse columns, column-id value pairs of said particular sparse column are each stored in association with a length value indicating a length of a column value.

### Claim 6 (depends on 1)

The method of claim 1, the steps further including indexing said sparse column values in an index, wherein said index comprises;
a first column that stores said plurality of column ids;
a second column that stores a plurality of column value arrays;
wherein each index row of index rows in said index includes:
in said first column, a column id of a particular sparse column of said sparse columns;
in said second column, a particular column value array of said plurality of column value arrays representing sparse column values of said particular sparse column, each element of said particular column value array having an array index that corresponds to a first table row in said first table and representing the column value of said particular sparse column for said first table row.

### Claim 7

A method comprising,
a database server receiving a DDL statement declaring sparse columns of a first table as sparse;
in response to receiving a DDL statement:
associating a column id of a plurality of column ids with each sparse column of said sparse columns,
creating a second table comprising:
a first column to store said plurality of column ids;
a second column that store a plurality of column value arrays; and
wherein each second table row of second table rows in said second table includes:
in said first column, a column id of a particular sparse column of said sparse columns;
in said second column, a particular column value array of said plurality of column value arrays representing sparse column values of said particular sparse column, each element of said particular column value array being associated with an array index that corresponds to a first table row in said first table and representing the column value of said particular sparse column for said first table row; and
wherein the method is performed by one or more computing devices.

### Claim 8 (depends on 7)

The method of claim 7, the method further comprising:
in response to receiving said DDL statement, creating in said first table a row id column to store a plurality of ordinal row ids;
wherein for each first table row in said first table, the row id column stores an ordinal row id of said plurality of row ids;
wherein for each second table row in said second table, said each second table row contains:
in said first column, a column id of a particular sparse column of said sparse columns;
in said second column, a particular column value array of said column value arrays representing sparse column values of said particular sparse column, each element of said particular column value array having an index that corresponds to the ordinal row id of a particular first table row in said first table and representing the column value of said particular sparse column for said first table row.

### Claim 9 (depends on 8)

The method of claim 8,
computing a query that specifies first criteria for a first sparse column of said sparse columns and second criteria for a second sparse column of said sparse columns;
wherein a first row in said second table corresponds to said first sparse column and stores a first column value array;
wherein a second row in said second table corresponds to said second sparse column and stores a second column value array;
wherein computing said query comprises:
generating first ordinal row ids associated with elements in said first column value array that hold a value satisfying said first criteria;
generating second ordinal row ids associated with elements in said second column value array that hold a value satisfying said second criteria;
comparing the first ordinal row ids and second ordinal row ids to determining first table rows in said first table that satisfy both said first criteria and said second criteria.

### Claim 10 (depends on 9)

The method of claim 9, wherein a B+ tree index indexes said first column.

### Claim 11

One or more non-transitory storage media storing instructions which, when executed by one or more computing devices, cause performance of a method comprising the steps of:
computing a query requiring sparse columns of a first table that are declared to be sparse, wherein each sparse column of said sparse columns is associated with a column id of a plurality of column ids, wherein sparse column values of said each sparse column are each stored in association with the respective column id as a column-id-value pair in a composite column of said first table; and
wherein computing a query comprises:
for each first table row of first table rows in said first table:
(a) using the respective column ids of the sparse columns, locating, in the composite column for said each first table row, one or more column-id-value pairs of the sparse columns;
(b) retrieving one or more respective sparse column values of said one or more column-id-value pairs; and
wherein the method is performed by one or more computing devices.

### Claim 12 (depends on 11)

The one or more non-transitory storage media of claim 11, the steps further including:
receiving a DDL statement declaring said sparse columns as sparse;
in response to receiving said DDL statement,
creating said composite column; and
storing said sparse column values for said sparse columns in said composite column.

### Claim 13 (depends on 11)

The one or more non-transitory storage media of claim 11, wherein computing the query further comprises:
executing a query execution plan comprising one or more plan operators, wherein
executing said one or plan operators causes performance of steps (a) and (b).

### Claim 14 (depends on 11)

The one or more non-transitory storage media of claim 11, wherein said composite column is a BLOB column.

### Claim 15 (depends on 11)

The one or more non-transitory storage media of claim 11, wherein for a particular sparse column of said sparse columns, column-id value pairs of said particular sparse column are each stored in association with a length value indicating a length of a column value.

### Claim 16 (depends on 11)

The one or more non-transitory storage media of claim 11, the steps further including indexing said sparse column values in an index, wherein said index comprises;
a first column that stores said plurality of column ids;
a second column that stores a plurality of column value arrays;
wherein each index row of index rows in said index includes:
in said first column, a column id of a particular sparse column of said sparse columns;
in said second column, a particular column value array of said plurality of column value arrays representing sparse column values of said particular sparse column, each element of said particular column value array having an array index that corresponds to a first table row in said first table and representing the column value of said particular sparse column for said first table row.

### Claim 17

One or more non-transitory storage media storing instructions which, when executed by one or more computing devices, cause performance of a method comprising the steps of:
a database server receiving a DDL statement declaring sparse columns of a first table as sparse;
in response to receiving a DDL statement:
associating a column id of a plurality of column ids with each sparse column of said sparse columns,
creating a second table comprising:
a first column to said plurality of column ids;
a second column that to store a plurality of column value arrays; and
wherein each second table row of second table rows in said second table includes:
in said first column, a column id of a particular sparse column of said sparse columns;
in said second column, a particular column value array of said plurality of column value arrays representing sparse column values of said particular sparse column, each element of said particular column value array being associated with an array index that corresponds to a first table row in said first table and representing the column value of said particular sparse column for said first table row; and
wherein the one or more non-transitory storage media is performed by one or more computing devices.

### Claim 18 (depends on 17)

The one or more non-transitory storage media of claim 17, the one or more non-transitory storage media further comprising:
in response to receiving said DDL statement, creating in said first table a row id column to store a plurality of ordinal row ids;
wherein for each first table row in said first table, the row id column stores an ordinal row id of said plurality of row ids;
wherein for each second table row in said second table, said each second table row contains:
in said first column, a column id of a particular sparse column of said sparse columns;
in said second column, a particular column value array of said column value arrays representing sparse column values of said particular sparse column, each element of said particular column value array having an index that corresponds to the ordinal row id of a particular first table row in said first table and representing the column value of said particular sparse column for said first table row.

### Claim 19 (depends on 18)

The one or more non-transitory storage media of claim 18,
computing a query that specifies first criteria for a first sparse column of said sparse columns and second criteria for a second sparse column of said sparse columns;
wherein a first row in said second table corresponds to said first sparse column and stores a first column value array;
wherein a second row in said second table corresponds to said second sparse column and stores a second column value array;
wherein computing said query comprises:
generating first ordinal row ids associated with elements in said first column value array that hold a value satisfying said first criteria;
generating second ordinal row ids associated with elements in said second column value array that hold a value satisfying said second criteria;
comparing the first ordinal row ids and second ordinal row ids to determining first table rows in said first table that satisfy both said first criteria and said second criteria.

### Claim 20 (depends on 19)

The one or more non-transitory storage media of claim 19, wherein a B+ tree index indexes said first column.
