Optimization of queries on a repository based on constraints on how the data is stored in the repository

• US 8,793,267 B2
• Filed: 04/24/2012
• Issued: 07/29/2014
• Est. Priority Date: 07/08/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Krishnaprasad, Muralidhar, Liu, Zhen Hua, Arora, Vikas
• Primary Examiner(s): Perveen, Rehana
• Assistant Examiner(s): Wong, Huen
• Application Number:
  US13/454,977
• Publication Number:
  US 20120271814A1
• Time in Patent Office: 826 Days
• Field of Search: None
• US Class Current: 707/759
• CPC Class Codes: G06F 16/14 Details of searching files based on file metadata
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers

Abstract

Constraints that restrict how corresponding identifiable groups of files are stored in a database, are enforced on corresponding file data. In response to a query on any data from an identifiable group of files, the effect of the constraint on how the corresponding data is stored is determined. The original query is rewritten, based on the effect of the constraint, so that the rewritten query is directed to a particular subset of the data stored in the database. Consequently, the search space is restricted to an identifiable subset of the database and execution of the rewritten query is more efficient than execution of the original query.

Claims

1. A computer-implemented method comprising:

storing, within database objects managed by a database system, data files contained in a directory of folders, each folder of said directory of folders containing at least one data file of said data files;

in said database, storing metadata that defines plurality of constraints, wherein for each folder of said directory of folders, the plurality of constraints includes a physical constraint that specifies that storage of any data file in said each folder is restricted to at least one specified database object of said database objects;

wherein for at least two of said directory of folders, a first folder includes a first data file that is restricted by a first physical constraint of said plurality of constraints to a first database object specified by said first physical constraint and a second folder includes a second data file that is restricted by a second physical constraint of said plurality of constraints to a second database object specified by said second physical constraint;

wherein the first database object is different than the second database object;

receiving, at the database system, a query that complies with a query language;

in response to receiving said query;

based on (1) said query and (2) the plurality of constraints defined by the metadata, said database system making a determination of how data required by the query is stored in said database; and

based on the determination of how data required by the query is stored in said database, rewriting the query to generate a re-written query that specifies a restriction based on said first database object specified by said first physical constraint, wherein the received query did not specify said restriction.

2. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that contain a particular type of content;

wherein receiving a query includes receiving a query that involves the particular type of content; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the particular set of one or more folders.

3. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that contain a particular type of content;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular set of one or more folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

4. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that contain a particular type of content;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular type of content; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

5. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that are associated with a particular XML schema;

wherein receiving a query includes receiving a query that involves a file that is associated with the particular XML schema; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the particular set of one or more folders.

6. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that are associated with a particular XML schema;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables;

wherein receiving a query includes receiving a query that involves the particular set of one or more folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

7. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that are associated with a particular XML schema;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables;

wherein receiving a query includes receiving a query that involves a file that is associated with the particular XML schema; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

8. The method of claim 1,

wherein the plurality of constraints includes a physical constraint that restricts storing data from files contained in a particular portion of said directory of folders to storage in a corresponding set of one or more particular database tables;

wherein receiving a query includes receiving a query that involves the particular portion of said directory of folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

9. The method of claim 1,

wherein the plurality of constraints includes a physical constraint that restricts storing data from files contained in a particular portion of said directory of folders to storage in a corresponding set of one or more particular database views;

wherein receiving a query includes receiving a query that involves the particular portion of said directory of folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database views.

10. The method of claim 1,

wherein the plurality of constraints includes a physical constraint that restricts storing identifiable subsets of data from files contained in a particular portion of said directory of folders to storage in corresponding particular database table partitions;

wherein receiving a query includes receiving a query that involves the particular portion of said directory of folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to one or more corresponding particular database table partitions.

11. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files associated with particular metadata;

wherein receiving a query includes receiving a query that involves the particular metadata; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the particular set of one or more folders.

12. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files associated with particular metadata;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular set of one or more folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

13. The method of claim 1,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files associated with particular metadata;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular metadata; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

14. A computer-implemented method comprising:

storing, within database objects managed by a database system, files contained in a plurality of identifiable groups of one or more files, each identifiable group of said plurality of identifiable groups of one or more files containing at least one file of said files;

in said database, storing metadata that defines a plurality of constraints, wherein for each identifiable group of one or more files of said plurality of identifiable groups, said plurality of constraints includes a physical constraint that specifies that storage of any file in said each identifiable group of one or more files is restricted to storage in a database in at least one specified database object from a group consisting of (a) a particular one or more database tables, (b) a particular one or more database table partitions, and (c) a particular one or more database views;

wherein for at least two of said groups of one or more files, a first group includes a first data file that is restricted by a first physical constraint of said plurality of constraints to a first database object specified by said first physical constraint and a second group includes a second data file that is restricted by a second physical constraint of said plurality of constraints to a second database object specified by said second physical constraint;

wherein the first database object is different than the second database object;

receiving, at the database system, a query that complies with a query language;

in response to receiving said query;

based on (1) said query and (2) the plurality of constraints defined by the metadata, said database system making a determination of how data required by the query is stored in said database; and

based on the determination on how data required by the query is stored in said database, rewriting the query to generate a re-written query that specifies a restriction based on said first database object specified by said first physical constraint, wherein the received query did not specify said group; and

executing the re-written query that specifies a restriction based on said first database object.

15. The method of claim 14,

wherein said each identifiable group of one or more files is identifiable based on a particular set of one or more folders said each identifiable group of one or more files is associated.

16. The method of claim 14,

wherein said each identifiable group of one or more files is identifiable based on what particular XML schema said each identifiable group of one or more files is associated.

17. The method of claim 14,

wherein said each identifiable group of one or more files is identifiable based on a particular size range within which each of the one or more files falls.

18. The method of claim 14,

wherein said each identifiable group of one or more files is identifiable based on with what particular metadata attributes said each identifiable group of one or more files is associated.

19. A non-transitory computer-readable medium storing instructions, which, when executed by one or more processors, cause performance of:

storing, within database objects managed by a database system, data files contained in a directory of folders, each folder of said directory of folders containing at least one data file of said data files;

in said database, storing metadata that defines a plurality of constraints, wherein for each folder of said directory of folders, the plurality of constraints includes a physical constraint that specifies that storage of any data file in said each folder is restricted to at least one specified database object of said database objects;

wherein for at least two of said directory of folders, a first folder includes a first data file that is restricted by a first physical constraint of said plurality of constraints to a first database object specified by said first physical constraint and a second folder includes a second data file that is restricted by a second physical constraint of said plurality of constraints to a second database object specified by said second physical constraint;

wherein the first database object is different than the second database object;

receiving, at the database system, a query that complies with a query language;

in response to receiving said query;

based on (1) said query and (2) the plurality of constraints defined by the metadata, said database system making a determination of how data required by the query is stored in said database; and

based on the determination of how data required by the query is stored in said database, rewriting the query to generate a re-written query that specifies a restriction based on said first database object specified by said first physical constraint, wherein the received query did not specify said restriction.

20. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that contain a particular type of content;

wherein receiving a query includes receiving a query that involves the particular type of content; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the particular set of one or more folders.

21. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that contain a particular type of content;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular set of one or more folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

22. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that contain a particular type of content;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular type of content; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

23. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that are associated with a particular XML schema;

wherein receiving a query includes receiving a query that involves a file that is associated with the particular XML schema; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the particular set of one or more folders.

24. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that are associated with a particular XML schema;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables;

wherein receiving a query includes receiving a query that involves the particular set of one or more folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

25. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files that are associated with a particular XML schema;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables;

wherein receiving a query includes receiving a query that involves a file that is associated with the particular XML schema; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

26. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a physical constraint that restricts storing data from files contained in a particular portion of said directory of folders to storage in a corresponding set of one or more particular database tables;

wherein receiving a query includes receiving a query that involves the particular portion of said directory of folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

27. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a physical constraint that restricts storing data from files contained in a particular portion of said directory of folders to storage in a corresponding set of one or more particular database views;

wherein receiving a query includes receiving a query that involves the particular portion of said directory of folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database views.

28. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a physical constraint that restricts storing identifiable subsets of data from files contained in a particular portion of said directory of folders to storage in corresponding particular database table partitions;

wherein receiving a query includes receiving a query that involves the particular portion of said directory of folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to one or more corresponding particular database table partitions.

29. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files associated with particular metadata;

wherein receiving a query includes receiving a query that involves the particular metadata; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the particular set of one or more folders.

30. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory of folders to containing only files associated with particular metadata;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular set of one or more folders; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

31. The non-transitory computer-readable medium of claim 19,

wherein the plurality of constraints includes a logical constraint that restricts a particular set of one or more folders of said directory folders to containing only files associated with particular metadata;

wherein the plurality of constraints includes a physical constraint that restricts storing data from the files contained in the particular set of one or more folders to storage in a corresponding set of one or more particular database tables; and

wherein receiving a query includes receiving a query that involves the particular metadata; and

wherein re-writing the query includes re-writing the query so that the re-written query is directed, at least in part, to the corresponding set of one or more particular database tables.

32. A non-transitory computer-readable medium storing instructions, which, when executed by one or more processors, cause performance of:

storing, within database objects managed by a database system, files contained in a plurality of identifiable groups of one or more files, each identifiable group of said plurality of identifiable groups of one or more files containing at least one file of said files;

in said database, storing metadata that defines a plurality of constraints, wherein for each identifiable group of one or more files of said plurality of identifiable groups, said plurality of constraints includes a physical constraint that specifies that storage of any file in said each identifiable group of one or more files is restricted to storage in a database in at least one specified database object from a group consisting of (a) a particular one or more database tables, (b) a particular one or more database table partitions, and (c) a particular one or more database views;

wherein for at least two of said groups of one or more files, a first group includes a first data file that is restricted by a first physical constraint of said plurality of constraints to a first database object specified by said first physical constraint and a second group includes a second data file that is restricted by a second physical constraint of said plurality of constraints to a second database object specified by said second physical constraint;

wherein the first database object is different than the second database object;

receiving, at the database system, a query that complies with a query language;

in response to receiving said query;

based on (1) said query and (2) the plurality of constraints defined by the metadata, said database system making a determination of how data required by the query is stored in said database; and

based on the determination on how data required by the query is stored in said database, rewriting the query to generate a re-written query that specifies a restriction based on said first database object specified by said first physical constraint, wherein the received query did not specify said group; and

executing the re-written query that specifies a restriction based on said first database object.

33. The non-transitory computer-readable medium of claim 32,

wherein said each identifiable group of one or more files is identifiable based on a particular set of one or more folders said each identifiable group of one or more files is associated.

34. The non-transitory computer-readable medium of claim 32,

wherein said each identifiable group of one or more files is identifiable based on what particular XML schema said each identifiable group of one or more files is associated.

35. The non-transitory computer-readable medium of claim 32,

wherein said each identifiable group of one or more files is identifiable based on a particular size range within which each of the one or more files falls.

36. The non-transitory computer-readable medium of claim 32,

wherein said each identifiable group of one or more files is identifiable based on with what particular metadata attributes said each identifiable group of one or more files is associated.