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

• US 8,166,059 B2
• Filed: 10/03/2005
• Issued: 04/24/2012
• 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): Boccio, Vincent
• Assistant Examiner(s): Wong, Huen
• Application Number:
  US11/243,345
• Publication Number:
  US 20070011167A1
• Time in Patent Office: 2,395 Days
• Field of Search: 707/713, 707/759, 707/769
• 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, for each folder of said directory of folders, defines one or more constraints, wherein said one or more 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 to a first database object and a second folder includes a second data file that is restricted to a second database object;

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;

reading said metadata defining said one or more constraints;

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

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 at least one specified database object, wherein the received query did not specify said restriction; and

executing, at the database system, the re-written query that specifies the restriction based on said at least one specified database object.

2. The method of claim 1,

wherein the one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that contain a particular type of content;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that contain a particular type of content;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that are associated with a particular XML schema;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that are associated with a particular XML schema;

wherein the one or more 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 one or more constraints includes a physical constraint that restricts storing data from files contained in a particular portion of a file system directory 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 the file system directory; 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 one or more constraints includes a physical constraint that restricts storing data from files contained in a particular portion of a file system directory 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 the file system directory; 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 one or more constraints includes a physical constraint that restricts storing identifiable subsets of data from files contained in a particular portion of a file system directory to storage in corresponding particular database table partitions;

wherein receiving a query includes receiving a query that involves the particular portion of the file system directory; 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files associated with particular metadata;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files associated with particular metadata;

wherein the one or more 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 containing at least one file of said files;

in said database, storing metadata that, for each identifiable group of one or more files of said plurality of identifiable groups, defines one or more constraints, wherein said one or more 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 a 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 to a first database object and a second group includes a second data file that is restricted to a second database object;

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

in response to receiving said query;

reading said metadata defining said one or more constraints;

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

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 specified database object, wherein the received query did not specify said group; and

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

15. The method of claim 14,

wherein said each identifiable group of one or more files is identifiable based on with what 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 with 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, for each folder of said directory of folders, defines one or more constraints, wherein said one or more 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 to a first database object and a second folder includes a second data file that is restricted to a second database object;

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;

reading said metadata defining said one or more constraints;

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

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 at least one specified database object, wherein the received query did not specify said restriction; and

executing, at the database system, the re-written query that specifies the restriction based on said at least one specified database object.

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

wherein the one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that contain a particular type of content;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that contain a particular type of content;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that are associated with a particular XML schema;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files that are associated with a particular XML schema;

wherein the one or more 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 one or more constraints includes a physical constraint that restricts storing data from files contained in a particular portion of a file system directory 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 the file system directory; 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 one or more constraints includes a physical constraint that restricts storing data from files contained in a particular portion of a file system directory 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 the file system directory; 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 one or more constraints includes a physical constraint that restricts storing identifiable subsets of data from files contained in a particular portion of a file system directory to storage in corresponding particular database table partitions;

wherein receiving a query includes receiving a query that involves the particular portion of the file system directory; 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files associated with particular metadata;

wherein the one or more 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 one or more constraints includes a logical constraint that restricts a particular set of one or more folders, from a file system directory, to containing only files associated with particular metadata;

wherein the one or more 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 containing at least one file of said files;

in said database, storing metadata that, for each identifiable group of one or more files of said plurality of identifiable groups, defines one or more constraints, wherein said one or more 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 a 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 to a first database object and a second group includes a second data file that is restricted to a second database object;

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

in response to receiving said query;

reading said metadata defining said one or more constraints;

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

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 specified database object, wherein the received query did not specify said group; and

executing the re-written query that specifies the restriction based on said specified 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 with what 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 with 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.