Sorted-paged retrieval of hierarchical data from relational databases
View Patent ↗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.
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.