Parallel database processing system and retrieval method using secondary key

• US 5,515,531 A
• Filed: 05/28/1993
• Issued: 05/07/1996
• Est. Priority Date: 05/29/1992
• Status: Expired due to Term

• Current Assignee: Hitachi, Ltd.
• Original Assignee: Hitachi, Ltd.
• Inventors: Fujiwara, Shinji, Nagasaka, Mitsuru, Shintani, Yooichi
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US08/069,193
• Time in Patent Office: 1,075 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A parallel database processing system a global database processing unit, a plurality of local database processing units, and a network interconnecting the global database processing unit and a plurality of local database processing units, wherein one table containing a number of records is partitioned into local tables, each local table being provided in a corresponding one of the local database processing units, secondary key indexes representing correspondence between the values of secondary keys for all records in the table and access information to the records corresponding to the secondary keys, are partitioned on the basis of the values into local secondary key indexes, each set of the local secondary key indexes being provided in a corresponding one of the local database processing units, and an identifying unit is provided in the global database processing unit, the identifying unit responsive to a retrieval request with the secondary key identifying the local database processing unit having the local secondary key index corresponding to the secondary key. At the global database processing unit, the retrieval request with the secondary key is received, the identifying unit identifies the local database processing unit having the local secondary key index corresponding to the secondary key, and the retrieval request with the secondary key is transferred to the identified local database processing unit. At the identified local database processing unit, its own local secondary key indexes are referred to obtain access information corresponding to the transferred retrieval request with the secondary key, and the retrieval request with the secondary key is processed by using the access information so that the local table corresponding to the retrieval request with the secondary key is accessed.

Claims

1. A method of retrieving a database by using a secondary key in a parallel database processing system having global database processing means, a plurality of local database processing means, and a network interconnecting said global database processing means and a plurality of said local database processing means, wherein one table containing a number of records is partitioned into local tables on the basis of values of primary keys, each of said local tables being provided in a corresponding one of said local database processing means, said global database processing means including a primary key partition table responsive to a retrieval request with one of said primary keys for identifying one of said plurality of local database processing means having said local table corresponding to said one of said primary keys, primary key indexes representing correspondence between the values of said primary keys for all records in said one table and storage locations of all records in said local tables being partitioned on the basis of said primary keys into local primary key indexes, each set of local primary key indexes being provided in a corresponding one of said plurality of local database processing means having a corresponding one of said local tables, so that said retrieval request with said primary key received by said global database processing means is transferred to a corresponding one of said plurality of local database processing means with reference to said primary key partition table and said corresponding one of said plurality of local database processing means converts the transferred retrieval request with said primary key into a storage location corresponding to said transferred retrieval request with reference to its own set of local primary key indexes to access its own local table based on said storage location thus converted to obtain a record corresponding to said retrieval request with said primary keys, secondary key indexes representing correspondence between the values of secondary keys for all records in said one table and access information to said records corresponding to said secondary keys are partitioned on the basis of said values of said secondary keys into local secondary key indexes, each set of said local secondary key indexes being provided in a corresponding one of said local database processing means, and identifying means is provided in said global database processing means, said identifying means being responsive to a retrieval request with said secondary key identifying said local database processing means having said local secondary key index corresponding to said secondary key, said method comprising the steps of:

a) at said global database processing means, receiving said retrieval request with said secondary key, causing said identifying means to identify the one of said local database processing means having said local secondary key index corresponding to said secondary key, and transferring said retrieval request with said secondary key to said identified one of said local database processing means; and

b) at said identified local database processing means, referring to its own said local secondary key indexes, obtaining said access information corresponding to said transferred retrieval request with said secondary key, and processing said retrieval request with said secondary key by using said access information so that said local table corresponding to said retrieval request with said secondary key is accessed.

2. A method of retrieving a database by using a secondary key in a parallel database processing system according to claim 1, wherein said step b) includes the substeps of:

at said identified local database processing means, identifying said local database processing means corresponding to said obtained access information; and

at said local database processing means corresponding to said obtained access information, accessing its own said local table to obtain information of a target record.

3. A method of retrieving a database by using a secondary key in a parallel database processing system according to claim 1, wherein said identifying means is a table indicating correspondence between secondary key groups and corresponding ones of local database processing means having said local secondary key indexes corresponding said secondary key groups.

4. A method of retrieving a database by using a second key in a parallel database processing system according to claim 1, wherein

said second key indexes represent correspondence between the values of said secondary keys for all records and the values of said primary keys as said access information to said records corresponding to said secondary keys, and

said step b) includes substep:

b1) at said identified local database processing means, obtaining a primary key corresponding to said transferred retrieval request with said secondary key with reference to its own said local secondary indexes and selecting one of said plurality of local database processing means corresponding to said obtained primary key,

b2) at said selecting local database processing means, with reference to its own local primary key indexes, obtaining a storage location of the record corresponding to said obtained primary key, and

b3) at said selected local database processing means, accessing its own local table based on said obtained storage location to obtain a target record.

5. A method of retrieving a database by using a second key in a parallel database processing system according to claim 4, wherein

each of said plurality of local database processing means includes a copy of said primary key partition table and

said substep b1) includes a substep of, at said identified local database processing means, selecting one of said plurality of local database processing means corresponding to said obtained primary key with reference to its own copy of said primary key partition table.

6. A method of retrieving a database by using a second key in a parallel database processing system according to claim 1, wherein

said second key indexes represent correspondence between the values of said secondary keys for all records in said one table and the storage locations of said all records in said local tables as said access information to said records corresponding to said second secondary keys, and

said step b) includes substeps

b1) at said identified local database processing means, obtaining a storage location corresponding to said transferred retrieval request with said secondary key with reference to its own said local secondary indexes and selecting one of said plurality of local database processing means corresponding to one of said local tables having said obtained storage location, and

b2) at said selected local database processing means, for accessing its own local table based on said obtained storage location to obtain a target record.

7. A method of retrieving a database by using a secondary key in a parallel database processing system having global database processing means, a plurality of local database processing means, and a network interconnecting said global database processing means and a plurality of said local database processing means, wherein one table containing a number of records is partitioned into local tables on the basis of values of primary keys, each of said local tables being provided in a corresponding one of said local database processing means, said global database processing means including a primary key partition table responsive to a retrieval request with one of said primary keys for identifying one of said plurality of local database processing means having said local table corresponding to said one of said primary keys, primary key indexes representing correspondence between the values of said primary keys for all records in said one table and storage locations of all records in said local tables being partitioned on the basis of said primary keys into local primary key indexes each set of local primary key indexes being provided in a corresponding one of said plurality of local database processing means having a corresponding one of said local tables, so that said retrieval request with said primary key received by said global database processing means is transferred to a corresponding one of said plurality of local database processing means with reference to said primary key partition table and said corresponding one of said plurality of local database processing means converts the transferred retrieval request with said primary key into a storage location corresponding to said transferred retrieval request with reference to its own set of local primary key indexes to access its own local table based on said storage location thus converted to obtain a record corresponding to said retrieval request with said primary keys and secondary key indexes representing correspondence between the values of secondary keys for all records in said one table and said primary keys are provided in said global database processing means or distributed in said plurality of local database processing means, said method comprising the step of:

a) at either said global database processing means and said local database processing means, receiving said retrieval request with said secondary key, referring to said secondary key indexes and converting said retrieval request with said secondary key into a retrieval request with said primary key so that said local table corresponding to said retrieval request with said primary key is accessed.

8. A method of retrieving a database by using a secondary key in a parallel database processing system according to claim 7, wherein

said secondary key indexes are partitioned on the basis of said values into local secondary key indexes, each set of said local secondary key indexes being provided in a corresponding one of said local database processing means, and first identifying means are provided in said global database processing means, said first identifying means being responsive to said retrieval request with said secondary key identifying said local database processing means having said local secondary key index corresponding to said secondary key, and wherein said step a) includes the substeps of:

b) at said global database processing means, receiving said retrieval request with said secondary key, causing said first identifying means to identify said local database processing means having said local secondary key index corresponding to said secondary key, and transferring said retrieval request with said second key to said identified local database processing means; and

c) at said identified local database processing means, referring to said local secondary key indexes stored in said identified local database processing means, and converting said transferred retrieval request with said secondary key into a retrieval request with said primary key.

9. A method of retrieving a database by using a secondary key in a parallel database processing system according to claim 8, wherein

second identifying means, which is a copy of said primary key partition table, are provided in each of said local database processing means, said second identifying means responsive to said retrieval request with said primary key identifying said local database processing means having a record corresponding to said primary key, and wherein said step a) further includes a substep of:

at said identified local database processing means, responding to said retrieval request with said primary key, causing said second identifying means of said identified local database processing means to identify said local database processing means having a target record, and at said local database processing means identified by said second identifying means, accessing said local table at the location corresponding to said primary key.

10. A method of retrieving a database by using a secondary key in a parallel database processing system according to claim 9, wherein

said second identifying means is a table indicating correspondence between said primary keys and corresponding ones of said local database processing means having said records corresponding to said primary keys, said table is provided in said global database processing means, and a copy of said table is provided in each said local database processing means.

11. A method of retrieving a database by using a secondary key in a parallel database processing system according to claim 8, wherein said step a) further includes the substeps of:

if said local database processing means identified by said second identifying means is different from said local database processing means identified by said first identifying means, transferring said retrieval request with said primary key from said local database processing means identified by said first identifying means to said local database processing means identified by said second identifying means via said network; and

at said local database processing means identified by said second identifying means, accessing said local table at the location corresponding to said transferred retrieval request with said primary key.

12. A method of retrieving a database by using a secondary key in a parallel database processing system according to claim 7, wherein:

said secondary key indexes are provided in said global database processing means;

identifying means are provided in said global database processing means, said identifying means being responsive to said primary key identifying said local database processing means corresponding to said primary key; and

primary key indexes are provided in each of said local database processing means, said primary key indexes indicating the storage locations of records stored in said local table and corresponding to said primary keys, and wherein said stem a) includes the substeps of;

at said global database processing means, referring to its own said secondary key indexes, converting said retrieval request with said secondary key into a retrieval request with said primary key, responding to said retrieval request with said primary key, causing said identifying means to identify said local database processing means corresponding to said primary key, and transferring said retrieval request with said primary key to said identified local database processing means; and

at said identified local database processing means, referring to its own said primary key indexes, and accessing said local table in accordance with said transferred retrieval request with said primary key.

13. A parallel database processing system having global database processing means, a plurality of local database processing means, and a network interconnecting said global database processing means and a plurality of said local database processing means, one table containing a number of records being partitioned into local tables on the basis of values of primary keys, each of said local tables being provided in each of said local database processing means, said global database processing means including a primary partition table responsive to a retrieval request with one of said primary keys for identifying one of said plurality of local database processing means having said local table corresponding to said one of said primary keys, primary key indexes representing correspondence between the values of said primary keys for all records in said one table and storage locations of all records in said local tables being partitioned on the basis of said primary keys into local primary key indexes each set of local primary key indexes being provided in a corresponding one of said plurality of local database processing means having a corresponding one of said local tables, so that said retrieval request with said primary key received by said global database processing means is transferred to a corresponding one of said plurality of local database processing means with reference to said primary key partition table and said corresponding one of said plurality of local database processing means converts the transferred retrieval request with said primary key into a storage location corresponding to said transferred retrieval request with reference to its own set of local primary key indexes to access its own local table based on said storage location thus converted to obtain a record corresponding to said retrieval request with said primary keys wherein secondary key indexes representing correspondence between the values of secondary keys for all records in said one table and access information to said records corresponding to said secondary keys, are partitioned on the basis of said values of said secondary keys into fragmented secondary key indexes, each set of said fragmented secondary key indexes being provided in a corresponding one of said local database processing means, said global database processing means includes:

identifying means responsive to a retrieval request with said secondary key for identifying said local database processing means having said fragmented secondary key index corresponding to said secondary key; and

means for receiving said retrieval request with said secondary key, causing said identifying means to identify said local database processing means having said fragmented secondary key index corresponding to said secondary key, and transferring said retrieval request with said secondary key to said identified local database processing means, and

wherein each of said local database processing means includes access means for referring to its own said fragmented secondary key indexes, obtaining said access information corresponding to said transferred retrieval request with said secondary key, and processing said retrieval request with said secondary key by using said access information so that said local table corresponding to said retrieval request with said secondary key is accessed.

14. A parallel database processing system according to claim 13, wherein

global secondary key indexes are partitioned on the basis of the values of said secondary keys into fragmented global secondary key indexes, each set of said fragmented global secondary key indexes being provided in a corresponding one of said local database processing means, said global secondary key indexes indicating correspondence between the values of said secondary keys of all records in said one table and said local database processing means having said local tables storing said records corresponding said secondary keys,

each said local database processing means having local secondary key indexes indicating correspondence between said secondary keys of said records stored in its own said local table and the locations of said records, said local secondary key indexes and said global secondary key indexes in each said local database processing means constituting said fragmented secondary key indexes, and

wherein said access means in said local database processing means includes:

means for identifying said local database processing means as said access information while referring to said fragmented global secondary key indexes, said identified local database processing means having said local secondary key indexes containing said secondary key in said transferred retrieval request;

means for transferring said transferred retrieval request with said secondary key to a remote one of said local database processing means via said network if said identified local database processing means is said remote one; and

means for accessing a target record for said transferred retrieval request with said secondary key while referring to its own said fragmented secondary key indexes if said identified local database processing means is its own said local database processing means.

15. A parallel database system according to claim 13, wherein said access means of said local database processing means

accesses said local table for said retrieval request with said secondary key by using a standard database processing language, if said access information indicates an access to its own said local database processing means, and transfers said retrieval request with said secondary key to a remote one of said local database processing means via said network by using said standard database processing language, if said access information indicates an access to said remote one.

16. A parallel database processing system according to claim 13, wherein

said fragmented secondary key indexes of each said local database processing means are a fragment of a plurality set of secondary key indexes partitioned on the basis of the values of said secondary keys, said secondary key indexes indicating correspondence among the values of said secondary keys of all records of said table, identifications of said local database processing means storing said records, and the locations of said records, and

said access means of said local database processing means includes means for directly accessing said local table of a remote one of said local database processing means via said network, if said access information obtained in response to said transferred retrieval request with said secondary key, as said local database processing means storing a target record for said secondary key and the location of said record, indicates an access to said remote one.

17. A parallel database processing system having global database processing means, a plurality of local database processing means, and a network interconnecting said global database processing means and a plurality of said local database processing means, one table containing a number of records being partitioned into local tables on the basis of values of primary keys, each of said local tables being provided in each of said local database processing means, said global database processing means including a primary key partition table responsive to a retrieval request with one of said primary keys for identifying one of said plurality of local database processing means having said local table corresponding to said one of said primary keys, primary key indexes representing correspondence between the values of said primary keys for all records in said one table and storage locations of all records in said local tables being partitioned on the basis of said primary keys into local primary key indexes each set of local primary key indexes being provided in a corresponding one of said plurality of local database processing means having a corresponding one of said local tables, so that said retrieval request with said primary key received by said global database processing means is transferred to a corresponding one of said plurality of local database processing means with reference to said primary key partition table and said corresponding one of said plurality of local database processing means converts the transferred retrieval request with said primary key into a storage location corresponding to said transferred retrieval request with reference to its own set of local primary key indexes to access its own local table based on said storage location thus converted to obtain a record corresponding to said retrieval request with said primary keys wherein

secondary key indexes representing correspondence between the values of secondary keys for all records in said one table and said primary keys, are partitioned on the basis of said values of said secondary keys into fragmented secondary key indexes, each set of said fragmented secondary key indexes being provided in a corresponding one of said local database processing means,

wherein said global database processing means includes:

identifying means, responsive to a retrieval request with said secondary key, for identifying said local database processing means having said fragmented secondary key index corresponding to said secondary key; and

means for receiving said retrieval request with said secondary key, causing said identifying means to identify said local database processing means having said fragmented secondary key index corresponding to said secondary key, and transferring said retrieval request with said secondary key to said identified local database processing means, and

wherein each said local database processing means includes access means for referring to its own said fragmented secondary key indexes, converting said transferred retrieval request with said secondary key into a retrieval request with said primary key so that so that said local table corresponding to said retrieval request with said primary key is accessed.

18. A parallel database processing system according to claim 17, wherein

each said local database processing means further includes second identifying means, which is a copy of said primary key partition table, responsive to said converted retrieval request with said primary key for identifying said local database processing means having a record corresponding to said primary key, and

said access means of said local database processing means includes means responsive to said converted retrieval request with said primary key for transferring said retrieval request with said primary key to a remote one of said local database processing means via said network if said local database processing means identified by said second identifying means in response to said retrieval request with said primary key is said remote one.

19. A parallel database processing system according to claim 17, wherein

said second key indexes represent correspondence between the values of said secondary keys for all records and the values of said primary keys as said access information to said records corresponding to said secondary keys,

said access means of each of said plurality of local database processing means further include a copy of said primary key partition table for, in response to said converted retrieval request with said primary key, identifying one of said plurality of local database processing means having a record corresponding to said primary key,

said access means of each of said plurality of local database processing means, in response to said converted retrieval request with said primary key, identifying one of said plurality of local database processing means having a record corresponding to said primary key with reference to its own copy of said primary key partition table and transferring said converted retrieval request with said primary key of said transferred retrieval request to said identified one of said plurality of local database processing means, and

said access means of said identified one of said plurality of local database processing means, in response to said transferred retrieval request with said primary key, obtaining a storage location of a record corresponding to said primary key of said transferred retrieval request with reference to its own main key indexes and accessing its own local table based on said obtained storage location to obtain a target record.

20. A parallel database processing system having global database processing means, a plurality of local database processing means, and a network interconnecting said global database processing means and a plurality of said local database processing means, one table containing a number of records being partitioned into local tables on the basis of values of primary keys, each of said local tables being provided in each said local database processing means, said global database processing means including a primary key partition table responsive to a retrieval request with one of said primary keys for identifying one of said plurality of local database processing means having said local table corresponding to said one of said primary keys, primary key indexes representing correspondence between the values of said primary keys for all records in said one table and storage locations of all records in said local tables being partitioned on the basis of said primary keys into local primary key indexes each set of local primary key indexes being provided in a corresponding one of said plurality of local database processing means having a corresponding one of said local tables, so that said retrieval request with said primary key received by said global database processing means is transferred to a corresponding one of said plurality of local database processing means with reference to said primary key partition table and said corresponding one of said plurality of local database processing means converts the transferred retrieval request with said primary key into a storage location corresponding to said transferred retrieval request with reference to its own set of local primary key indexes to access its own local table based on said storage location thus converted to obtain a record corresponding to said retrieval request with said primary keys, wherein said global database processing means includes:

secondary key indexes indicating correspondence between the values of secondary keys of all records of said one table and said primary keys;

identifying means for identifying said local database processing means corresponding to said primary key;

means for converting said received retrieval request with said secondary key into a retrieval request with said primary key while referring to said secondary key indexes;

means responsive to said converted retrieval request with said primary key for identifying said local database processing means corresponding to said primary key by using said identifying means; and

means for transferring said retrieval request to said identified local database processing means, and

wherein each of said local database processing means includes;

primary key indexes indicating correspondence between the values of said primary keys and the locations of said records stored in said local table; and

access means for accessing said local table corresponding to said retrieval request with said primary key while referring to said primary key indexes.