Sorted-paged retrieval of hierarchical data from relational databases

• US 6,898,593 B1
• Filed: 02/05/2002
• Issued: 05/24/2005
• Est. Priority Date: 07/10/2001
• Status: Expired due to Term

• Current Assignee: Blue Yonder Group, Inc. (Panasonic Holdings Corporation)
• Original Assignee: i2 Technologies US Incorporated (Panasonic Holdings Corporation)
• Inventors: Wang, Haiying, Mulukutla, Ramkartik
• Primary Examiner(s): Ali, Mohammad
• Application Number:
  US10/072,399
• Time in Patent Office: 1,204 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-205
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method for sorted-paged retrieval of hierarchical data from a relational database is disclosed, which includes the use of one or more relational table equi-join terms in a query to increase the retrieval efficiency, suitable restrictions imposed on the join terms to further increase the retrieval efficiency, partial de-normalization or replication of information to improve the efficiency of the join operation(s) and thus further increase the retrieval efficiency, and proper cursor management to keep track of the current location of the cursor(s) involved. As a result, specific pages of objects can be efficiently retrieved from a database, while the specified sort orders and restrictions imposed by the original retrieval requests can be maintained.

Claims

1. A computer-based method for retrieving hierarchical data from a relational database, comprising the steps of:

creating a query for retrieving said hierarchical data, said query including at least one join term;

selecting, for said query, a driving table from a plurality of database tables including said hierarchical data;

imposing, for said query, at least one restriction on at least one table of said plurality of database tables including said hierarchical data; and

performing partial denormalization by replicating frequently searched data of said hierarchical data into said driving table.

2. The method of claim 1, further comprising the step of:

replicating frequently searched entities into said driving table.

3. The method of claim 1, further comprising the step of:

sending said query to said relational database creating a surrogate id column on said driving table, said surrogate id column storing a sequence of increasing integer values as records inserted in said driving table.

4. The method of claim 1, further comprising the step of:

creating a fetch instruction for said query, said fetch instruction including a number of rows of said-hierarchical data to be retrieved.

5. The method of claim 1, further comprising the step of:

creating a fetch instruction for said query, said fetch instruction causing an ordering to be performed before row numbers are assigned.

6. The method of claim 1, further comprising the steps of:

creating a Cursor URL;

sending said Cursor URL to a server, said server forming said query based on said Cursor URL; and

sending said query to said relational database.

7. The method of claim 1, further comprising the steps of:

sending said query to said relational database, said query including said at least one join term and said at least one restriction imposed on said at least one table of said plurality of database tables including said hierarchical data.

8. The method of claim 1, wherein said at least one table of said plurality of database tables including said hierarchical data comprises said driving table.

9. The method of claim 1, further comprising the steps of:

creating an address field, said address field including a Surrogate Cursor Id, a maximum or minimum value for ordering said retrieving by column, and a name of a column associated with said ordering;

sending said address field to a server, said server forming said query based on a Cursor URL; and

sending said query to said relational database.

10. The method of claim 1, wherein said relational database comprises a DB2 database system.

11. The method of claim 1, wherein said relational database comprises an ORACLE database system.

12. The method of claim 1, wherein said query comprises a query formatted in SQL.

13. A computer-based system for retrieving hierarchical data from a relational database, comprising:

a database; and

a user interface, said user interface coupled to said database and operable to;

create a query for retrieving said hierarchical data, said query including at least one join term;

select, for said query, a driving table from a plurality of database tables including said hierarchical data creating a surrogate id column on said driving table, said surrogate id column storing a sequence of increasing integer values as records inserted in said driving table;

impose, for said query, at least one restriction on at least one table of said plurality of database tables including said hierarchical data; and

perform partial denormalization by replicating frequently searched data of said hierarchical data into said driving table.

14. The system of claim 13, wherein said user interface is further operable to:

send said query to said database.

15. The system of claim 13, wherein said user interface is further operable to:

create a fetch instruction for said query, said fetch instruction including a number of rows of said hierarchical data to be retrieved.

16. The system of claim 13, wherein said user interface is further operable to:

create a Cursor URL;

send said Cursor URL to said database; and

send said query to said database.

17. The system of claim 13, wherein said user interface is further operable to:

send said query to said relational database, said query including said at least one join term and said at least one restriction imposed on said at least one table of said plurality of database tables including said hierarchical data.

18. The system of claim 13, wherein said at least one table of said plurality of database tables including said hierarchical data comprises said driving table.

19. The system of claim 13, wherein said user interface is further operable to:

create an address field, said address field including a Surrogate Cursor Id, a maximum or minimum value for ordering said retrieving by column, and a name of a column associated with said ordering;

send said address field to said database; and

send said query to said database.

20. The system of claim 13, wherein said database comprises a relational database.

21. The system of claim 13, wherein said database comprises a DB2 database system.

22. The system of claim 13, wherein said database comprises an ORACLE database system.

23. The system of claim 13, wherein said query comprises a query formatted in SQL.

24. A computer-based system for retrieving hierarchical data from a relational database, comprising:

means for creating a query for retrieving said hierarchical data, said query including at least one join term;

means for selecting, for said query, a driving table from a plurality of database tables including said hierarchical data means for creating a surrogate id column on said driving table, said surrogate id column storing a sequence of increasing integer values as records inserted in said driving table;

means for imposing, for said query, at least one restriction on at least one table of said plurality of database tables including said hierarchical data; and

means for performing partial denormalization by replicating frequently searched data of said hierarchical data into said driving table.

25. The system of claim 24, further comprising:

means for sending said query to said relational database.

26. The system of claim 24, further comprising:

means for creating a fetch instruction for said query, said fetch instruction including a number of rows of said hierarchical data to be retrieved.

27. The system of claim 24, further comprising:

means for creating a Cursor URL;

means for sending said Cursor URL to said relational database; and

means for sending said query to said relational database.

28. The system of claim 24, further comprising:

means for sending said query to said relational database, said query including said at least one join term and said at least one restriction imposed on said at least one table of said plurality of database tables including said hierarchical data.

29. Software for retrieving hierarchical data from a relational database, the software being embodied in computer-readable media and when executed operable to:

create a query for retrieving said hierarchical data, said query including at least one join term;

select, for said query, a driving table from a plurality of database tables including said hierarchical data creating a surrogate id column on said driving table, said surrogate id column storing a sequence of increasing integer values as records inserted in said driving table

impose, for said query, at least one restriction on at least one table of said plurality of database tables including said hierarchical data; and

perform partial denormalization by replicating frequently searched data of said hierarchical data into said driving table.

30. The software of claim 29, when executed further operable to:

send said query to said relational database.

31. The software of claim 29, when executed further operable to:

create a fetch instruction for said query, said fetch instruction including a number of rows of said hierarchical data to be retrieved.

32. The software of claim 29, when executed further operable to:

create a Cursor URL;

send said Cursor URL to said relational database; and

send said query to said relational database.

33. The software of claim 29, when executed further operable to:

send relational database, said query including said at least one join term and said at least one restriction imposed on said at least one table of said plurality of database tables including said hierarchical data.