Providing database system native operations for user defined data types

• US 6,697,794 B1
• Filed: 02/28/2001
• Issued: 02/24/2004
• Est. Priority Date: 02/28/2001
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation
• Inventors: Milby, Gregory Howard
• Primary Examiner(s): Robinson, Greta
• Assistant Examiner(s): Pannala, Sathyanarayan
• Application Number:
  US09/795,736
• Time in Patent Office: 1,091 Days
• Field of Search: 707/1-10, 707/102
• US Class Current: 707/702
• CPC Class Codes: G06F 16/2291 User-Defined Types; Storage management thereof
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and computer program for providing a database system native operation for a data element which has a user defined data type (UDT). The method and computer program include assigning a data type, which is supported by the database system native operation, to an alias. The method and computer program further include processing the data element to produce the alias, linking the alias to the data element, and performing the database system native operation using the alias.

Claims

1. A database system for accessing a database with a data element which has a user defined data type (UDT), the database system comprising

a massively parallel processing system comprising one or more nodes;

a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;

a plurality of virtual processes each of the one or more CPUs providing access to one or more virtual processes;

each virtual process configured to manage data stored in one of a plurality of data-storage facilities;

a parsing engine comprising

a session control, which controls access to the plurality of virtual processes;

a parcer, which interprets and optimizes a command for access to a database to produce a series of steps to be performed; and

a dispatcher, which controls the sequence of the series of steps and determines which of the plurality of virtual processes will perform each of the series of steps;

where the parcer includes

an optimizer which optimizes a plan for executing the series of steps;

where the optimizer includes

a process for getting a type for an alias for the UDT data element; and

a process for getting the alias for the UDT data element.

2. The database system of claim 1 where the process for getting the type returns an integer type.

3. The database system of claim 1 where the UDT data element comprises a Binary Large Object (BLOB) and the process for getting the alias includes computing a checksum for a value of the BLOB.

4. The database system of claim 1 where the UDT data element comprises a ROW ID and a value and the optimizer further comprises

an indexer for indexing a record including the alias and the ROW ID into an indexing construct using the alias as the indexing key.

5. The database system of claim 1 where the UDT data element comprises a ROW ID and a value and the optimizer further comprises

a process for selecting a record matching a search UDT data element from an indexing construct comprising

using the process for getting a type for the search alias for the search UDT data element;

using the process for getting the search alias for the search UDT data element; and

retrieving one or more records from the indexing construct which are indexed by the search alias.

6. The database system of claim 5 where the one or more retrieved records include pointers to respective one or more UDTs and the process for selecting the record matching the search UDT data element further comprises

selecting the one UDT pointed to by the one or more retrieved records that best matches the search UDT.

7. The database system of claim 1 where the optimizer further comprises a histogram process for building a histogram using the alias.

8. The database system of claim 1 where the optimizer further comprises a frequency search process using the alias.

9. The database system of claim 1 where the UDT data element comprises a ROW ID and a value and the optimizer further comprises

a hashing process for hashing a record including the alias and the ROW ID using the alias as the hashing key.

10. The database system of claim 1 where one or more of the virtual processes include

a process for getting a type for an alias for the UDT data element;

and

a process for getting the alias for the UDT data element.