Methods and systems for auto-partitioning of schema objects

• US 20040243618A1
• Filed: 05/30/2003
• Published: 12/02/2004
• Est. Priority Date: 05/30/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Bolsius, Roger, Malaney, Kevin, Jakubiak, Matthew
• Granted Patent: US 7,174,345 B2
• Time in Patent Office: Days
• US Class Current: 707/102
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method of partitioning a schema object containing Web server log file data that includes a plurality of dated entries. Existing partitions within the schema object are identified, as well as the date associated with each existing partition. Unique dates of the entries within the log file data are also identified. A separate partition within the schema object is then automatically created for a) each identified unique date of the entries within the log file data that is not associated with an identified existing partition, and for b) each date immediately preceding each date in a) unless the partition has previously been created. This method avoids splitting loaded partitions when the Web server log file data is loaded out of turn.

Claims

1. A method of partitioning a schema object comprising a plurality of rows, each row including a date, comprising the steps of:

generating a list of dates associated with the plurality of rows;

determining whether a partition exists for each unique date in the generated list of dates;

determining whether a partition exists for each date that immediately precedes each unique date in the generated list of dates;

carrying out a first creating step to create a partition for each date in the generated list of dates for which a partition does not yet exist, and

carrying out a second creating step to create a partition for each date that immediately precedes each date in the generated list for which a partition does not yet exist.

2. The method of claim 1, wherein for each date in the generated list, the second partition-creating step is carried out before any data is stored in the partition created in the first partition-creating step.

3. The method of claim 2, wherein the second partition-creating step is carried out before any data is stored in the partition created in the second partition-creating step.

4. The method of claim 1, wherein the first and second partition creating steps are created with selectable parameters.

5. The method of claim 4, wherein the selectable parameters includes a tablespace to which the created partition is to belong.

6. The method of claim 1, wherein the generating step includes a step of reading a Web server log file.

7. The method of claim 1, wherein the first and second creating steps include formulating and executing a partition-creating command.

8. The method of claim 7, wherein the partition-creating command includes a Data Definition Language (DDL) Structured Query Language (SQL) command.

9. A machine-readable medium having data stored thereon representing sequences of instructions which, when executed by computing device, causes said computing device to partition a schema object comprising a plurality of rows, each row including a date, by performing the steps of:

generating a list of dates associated with the plurality of rows;

determining whether a partition exists for each unique date in the generated list of dates;

determining whether a partition exists for each date that immediately precedes each unique date in the generated list of dates;

carrying out a first creating step to create a partition for each date in the generated list of dates for which a partition does not yet exist, and

carrying out a second creating step to create a partition for each date that immediately precedes each date in the generated list for which a partition does not yet exist.

10. The medium of claim 9, wherein for each date in the generated list, the second partition-creating step is carried out before any data is stored in the partition created in the first partition-creating step.

11. The medium of claim 10, wherein the second partition-creating step is carried out before any data is stored in the partition created in the second partition-creating step.

12. The medium of claim 9, wherein the first and second partition creating steps are created with selectable parameters.

13. The medium of claim 12, wherein the selectable parameters includes a tablespace to which the created partition is to belong.

14. The method of claim 9, wherein the generating step includes a step of reading a Web server log file.

15. The medium of claim 9, wherein the first and second creating steps include formulating and executing a partition-creating command.

16. The medium of claim 14, wherein the partition-creating command includes a Data Definition Language (DDL) Structured Query Language (SQL) command.

17. A computer system suitable for partitioning a schema object comprising a plurality of rows, each row including a date, comprising:

at least one processor;

at least one data storage device;

a plurality of processes spawned by said at least one processor, the processes including processing logic for;

generating a list of dates associated with the plurality of rows;

determining whether a partition exists for each unique date in the generated list of dates;

determining whether a partition exists for each date that immediately precedes each unique date in the generated list of dates;

carrying out a first creating step to create a partition for each date in the generated list of dates for which a partition does not yet exist, and

carrying out a second creating step to create a partition for each date that immediately precedes each date in the generated list for which a partition does not yet exist.

18. A method of partitioning a database table comprising a plurality of rows, each row being associated with a date, comprising the steps of:

generating a first list of dates, the first list of dates containing dates associated with the plurality of rows;

generating a second list of dates from the first list, the second list containing only dates that immediately precede each date in the first list;

generating a third list of dates from the first and second lists of dates, the third list containing each unique date that appears on the first list or on the second list and that does not have an existing partition within the database table associated therewith;

for each date in the third list, sequentially carrying out the steps of;

identifying a partition in the table associated with an earliest date that is after the date in the third list, and

splitting the identified partition at a boundary defined by the date in the third list to create a new partition associated with the date in the third list.

19. The method of claim 18, wherein the first list generating step includes a step of reading a Web server log file, wherein the first list of dates is generated from data in the log file.

20. The method of claim 18, further including loading Web server log file data into at least one of the created new partitions.

21. The method of claim 19, wherein the splitting step creates a new partition for which the Web server log file has no Web server log file data.

22. A machine-readable medium having data stored thereon representing sequences of instructions which, when executed by computing device, causes said computing device to partition a database table comprising a plurality of rows, each row being associated with a date, by performing the steps of:

generating a first list of dates, the first list of dates containing dates associated with the plurality of rows;

generating a second list of dates from the first list, the second list containing only dates that immediately precede each date in the first list;

generating a third list of dates from the first and second lists of dates, the third list containing each unique date that appears on the first list or on the second list and that does not have an existing partition within the database table associated therewith;

for each date in the third list, sequentially carrying out the steps of;

identifying a partition in the table associated with an earliest date that is after the date in the third list, and

splitting the identified partition at a boundary defined by the date in the third list to create a new partition associated with the date in the third list.

23. The medium of claim 22, wherein the first list generating step includes a step of reading a Web server log file, wherein the first list of dates is generated from data in the log file.

24. The medium of claim 22, further including loading Web server log file data into at least one of the created new partitions.

25. The medium of claim 23, wherein the splitting step creates a new partition for which the Web server log file has no Web server log file data.

26. A computer system suitable for partitioning a database table comprising a plurality of rows, each row being associated with a date, the computer system comprising:

at least one processor;

at least one data storage device;

a plurality of processes spawned by said at least one processor, the processes including processing logic for;

generating a first list of dates, the first list of dates containing dates associated with the plurality of rows;

generating a second list of dates from the first list, the second list containing only dates that immediately precede each date in the first list;

generating a third list of dates from the first and second lists of dates, the third list containing each unique date that appears on the first list or on the second list and that does not have an existing partition within the database table associated therewith;

for each date in the third list, sequentially carrying out the steps of;

identifying a partition in the table associated with an earliest date that is after the date in the third list, and

splitting the identified partition at a boundary defined by the date in the third list to create a new partition associated with the date in the third list.

27. A method of partitioning a schema object containing Web server log file data, the log file data including a plurality of dated entries, comprising:

identifying existing partitions within the schema object, a date being associated with each existing partition;

identifying unique dates of the entries within the log file data;

creating a separate partition within the schema object for;

a) each identified unique date of the entries within the log file data that is not associated with an identified existing partition, and

b) each date immediately preceding each date in a) unless previously created.