The present invention relates to a database management system (DBMS) in which data space is segmentalized, in particular to a database management program of a DBMS for an embedded device.
With regard to an embedded device having large-capacity storage, list fetch is used frequently in many applications such as a car navigation system, a music player and an HDD recorder.
Especially in the car navigation system, a dedicated file system in which emphasis is put to performing a real-time processing of list fetch using an I/O device performing a seek processing extremely slowly such as CD/DVD is used, and contrivance is made so that data to be list-displayed is positioned in sequential space on the disk. However, this dedicated file system is designed for a device performing no update operation, and in a case where update is performed using a device capable of update operation represented by an HDD, all the data are rearranged at each update operation.
Further, in the DBMS, as a function to allocate data in sequential space, there is a clustered table that guarantees that data is sorted physically with respect to a specified clustered key column structuring a table. This is shown in Non-Patent Document 1, for example.
Here, an example of a conventional embodiment of the clustered table in a DBMS in which storage space management composed of a page and a segment shown in
Here, a page (5002) is a physical unit representing a minimum unit of data I/O to storage space (5001) and each of segments (5003, 5004) is a physical unit representing a minimum unit in allocation of space to a table and an index. The segment is composed of a plurality of the pages. Relation between the clustered table and the storage space is shown in
When insert operation to the clustered table is performed, since it is required to maintain a state in which data is sorted physically, insertion is performed by a method different from that for a normal table. The method of performing insert operation to the clustered table is explained with reference to a flow chart shown in
On the other hand, if there is no empty space in the judgment (7004) whether there is empty space to insert a row in the same segment or not, a procedure of B (7014) is carried out. Here, firstly, the lock of the index related to a corresponding table is gotten (7010), and then, the row is inserted into a new segment (7011), the lock of the index is released (7007) and the insert processing is ended (7009). An operation example in a case where a row having a clustered key of 12 is inserted according to the procedure of B (8014) is shown in
By use of the clustered table, in performing a search with a range of the clustered key value designated, since storage I/O is sequentialized, the search is sped up. However, on the other hand, a state in which data is sorted physically is broken by the update and the insert operation over empty space at preparation of the table, and therefore, periodical maintenance is required. This maintenance is also rearrangement of all data in the same manner as in the update operation in the dedicated file system described above.
In the DBMS for embedded application, in order to achieve data I/O performance comparable to that of the dedicated file system incapable of update of the conventional system, it is necessary to arrange data in physical sequential space in the same manner. However, in the embedded application, since maintenance-free and long-term performance stability is important, an existing clustered table cannot be used as it is. Therefore, a table structure which is specialized for data fetch of a pattern called list fetch in the embedded application and improving I/O performance is desired.
Here, a characteristic of the list fetch is explained taking car navigation application as an example, with reference to
1. list fetch of intersections in a section (10002) including own car
2. list fetch of intersections in 9 adjacent sections (10003) around own car
Here, a pattern in which all data is arranged in sequential space in a case where a file system dedicated for car navigation is used is the pattern of 1, and it is impossible to arrange all data including the pattern of 2 in the sequential space. Therefore, also in a DBMS specialized for embedded application, it is enough if it is guaranteed that the pattern of 1 is arranged in the sequential space.
And, as a characteristic common to the two patterns, it is pointed out that a search condition is an equal condition of a section number and that a plurality of intersections having the same section number exists in search target data. By putting this characteristic otherwise in terms of DBMS, it becomes a search of an equal condition with respect to columns including a duplicated key.
Note that, a similar list fetch appears frequently also in application other than the car navigation application. For example, list fetch such as “list of music titles by an identical artist” and “list of music titles contained in an identical album” in music player application and “list of programs of * month * day” and “list of programs of a channel *” in HDD recorder application appear.
Non-Patent Document 1: [Product Manual] Scalable Database Server HiRDB Version 7 System Introduction and Design Guide (For UNIX(R)) 3000-6-272, 12.9 Specification of Clustered Key”, pp. 342-343
However, in a case of managing a single or plurality of sets of rows having values of the column A duplicated using relational database, even if a row having a value of the column A duplicated is arranged in sequential space as an initial state in order to speed-up a search having a condition of the column A, by repeating insert, deletion and update of rows, a state in which the row having the value of the column A duplicated is arranged in the sequential space is broken and time required for the search having the condition of the column A is lengthened, and therefore, in a case of solving this problem by a conventional method, rearrangement of all rows is required and long time is taken.
Accordingly, an object of the present invention is to provide a database management program in which the time required for the search having the condition of the column A is not lengthened even if insert, deletion and update of rows are repeated even in the case where the row having the value of the column A duplicated is arranged in the sequential space as an initial state in order to speed up the search having the condition of the column A, and the rearrangement of all rows taking long time in the case of solving this problem by the conventional method is not required.
In the database management program according to the present invention, when no empty sequential space is found in space where the rows having the same value of the column A is arranged on storage in a case of performing insertion, deletion and update, only a set of rows having the value of the column A duplicated is arranged in the sequential space using algorithm having a characteristic of including a procedure to search for sequential empty space having capacity larger than the whole rows having the same value of the column A, and a procedure to move the whole rows having the same value of the column A to the empty space in a case where the empty space is found by the search.
According to the present invention, when managing a single or plurality of sets of rows having values of the column A duplicated using the relational database, in a case where a row having a value of the column A duplicated is arranged in sequential space as an initial state in order to speed-up the search having the condition of the column A, even if insertion, deletion and update of a plurality of rows are repeated, the time required for the search having the condition of the column A is not lengthened, and the rearrangement of all rows taking long time in the case of solving this problem by the conventional method is not required.
A first embodiment of the present invention is described with reference to
The database server (1001) executes a database management program (1002) and includes a storage device (1008).
The database management program (1002) includes a data update module (1003) and a data search module (1004), and further includes a duplicated ID sequential arrangement table identification table (1005), a duplicated ID sequential arrangement table ID management table (1006) and a duplicated ID sequential arrangement table parent-child relation management table (1007).
The storage device (1008) stores storage space (1009), and in the storage space (1009), a duplicated ID sequential arrangement table (1010) and an index (1011) are stored.
The data update module (1003) and the data search module (1004) refer to the duplicated ID sequential arrangement table identification table (1005), the duplicated ID sequential arrangement table ID management table (1006) and the duplicated ID sequential arrangement table parent-child relation management table (1007), and identify the duplicated ID sequential arrangement table (1010) and the index (1011).
Note that, an entire system structure is the same in a second embodiment and a third embodiment described later. The duplicated ID sequential arrangement table parent-child relation management table (1007) is a table mainly used in the second embodiment and the third embodiment. In the explanation of the first embodiment described hereinafter, the duplicated ID sequential arrangement table parent-child relation management table (1007) does not appear.
A structure of the duplicated ID sequential arrangement table identification table is shown in
Next, a structure of the duplicated ID sequential arrangement table ID management table is shown in
A part of a structure of the duplicated ID sequential arrangement table (1010) on the storage space is shown in
Note that, a plurality of segments structuring a set of rows (12002) having the same ID arranged in adjacent segment is referred to shortly as a set of rows (13001) of the same ID composed of a set of segments shown in
In a case of performing insert operation to the duplicated ID sequential arrangement table, since it is required to maintain a state in which rows having the same ID are arranged in adjacent segments physically, insertion is performed by a method different from that for a normal table. The method of performing the insert operation to the duplicated ID sequential arrangement table is explained with reference to a flow chart shown in
In an insert processing (14001) performing the insert operation, firstly, size of a row to be inserted and size of empty space of the storage space are compared, and judgment (14002) whether the insertion can be performed or not is made. Here, if the insertion cannot be performed, error judgment is made (14021) and the insert processing is ended (14010). If the insertion can be performed, a position of a set of segments including a set of rows having the same ID in the storage space is searched (14003). Different from the case of the clustered table, this search (14003) is an equal condition, and there may be a case in which the set of segments cannot be found. Therefore, judgment (14004) whether the set of segments is found or not is carried out.
If the set of segments is found, judgment (14005) whether there is empty space to insert a row in a first partial set of the set of segments or not is carried out. If there is empty space, the procedure of A (14022) is carried out. In the procedure of A (14022), firstly, a lock of the index related to a corresponding table is gotten (14006), and then, the row is inserted into the empty space of the first partial set of the set of segments (14007), the index is updated (14008), the lock of the index is released (14009) and the insert processing is ended (14010). An operation example in a case where a row of ID=13 is inserted according to the procedure of A (14022) is shown in
On the other hand, if there is no empty space in the judgment (14005), sequential empty space larger than size of an entire set of rows having the same ID as that of the row to be inserted is searched in the storage space (14011), and judgment (14012) whether it is found or not is made. In a case where it is found in the judgment (14012), a procedure of B (14023) is carried out. In the procedure of B (14023), firstly, a lock related to a corresponding table is gotten (14013), then an entire existing set of rows having the same ID as that of the row to be inserted is moved (14014) to a set of new segments to be secured in the empty space found in the search (14011), the row is inserted into empty space of a set of the same segments (14015), the index is updated (14008), the lock of the index is released (14009), and the insert processing is ended (14010). An operation example in a case where a row of ID=13 is inserted according to the procedure of B (14023) is shown in
In a case where sequential empty space larger than the size of the entire set of rows having the same ID as that of the row to be inserted is not found in the judgment (14012), judgment (14016) whether there is a partial set having empty space in partial sets of the set of segments having the same ID as that of the row to be inserted or not is made. In the judgment (14016), if a partial set of the set of segments having empty space is found, the procedure of C (14024) is carried out. In the procedure of C (14024), firstly, a lock related to a corresponding table is gotten (14013), the row is inserted into the partial set of the set of segments having the empty space (14018), the index is updated (14008), the lock of the index is released (14009) and the insert processing is ended (14010). An operation example in a case where a row of ID=13 is inserted according to the procedure of C (14024) is shown in
On the other hand, in a case where a partial set of segments having empty space is not found in the judgment (14016), a procedure of D (14025) is carried out. In the procedure of D (14025), firstly, a lock related to a corresponding table is gotten (14019), and then, the row is inserted into a new partial set of the set of segments (14020), the index is updated (14008), the lock of the index is released (14009) and the insert processing is ended (14010). An operation example in a case where a row of ID=13 is inserted according to the procedure of D (14025) is shown in
Further, in a case where a set of segments including a set of rows having the same ID as that of the row to be inserted is not found in the judgment (14004), a procedure of E (14026) is carried out. In the procedure of E (14026), firstly, a lock related to a corresponding table is gotten (14019), and then, the row is inserted (14020) into a new partial set of the set of segments, the index is updated (14008), the lock of the index is released (14009) and the insert processing is ended (14010). An operation example in a case where a row of ID=13 is inserted according to the procedure of E (14025) is shown in
In the present embodiment, by performing the procedure of C (14024), the rows having duplicated ID are stored in non-sequential space. However, by performing the procedure of B (14023) in later insert operation, the rows are rearranged to sequential space.
By arranging a plurality of columns among columns structuring a table at physical vicinity on storage, I/O to a storage device may be sped-up.
In the present embodiment, a plurality of columns designated to be arranged at physical vicinity are sequentially called n-th-order key column, and in the more dominant column, a value of n is made larger. In this example, in a case of an equal condition search of the ID column (21003) which is a second-order key column, all data can be fetched collectively. For example, as for an equal condition search having “ID is equal to 2” as a search condition, a set of segments (21013) of ID=2 can be fetched collectively, and as for an equal condition search having “ID is equal to 3” as a search condition, a set of segments (21014) with ID=3 can be fetched collectively.
Further, also in a case of an equal condition search of a road type column (21002) which is a first-order key column, since rows matching the condition are rather organized, data can be fetched at high speed. For example, as for an equal condition search having “road type is equal to highway” as a search condition, a plurality of sets of segments (21007, 21010) of road type=highway are fetched, in a case of “road type is equal to national road”, a plurality of sets of segments (21008, 21011) of road type=national road are fetched, and in a case of “road type is equal to prefectural road”, a plurality of sets of segments (21009, 21012) of road type=prefectural road are fetched. Such a table is one obtained by expanding a duplicated ID sequential arrangement table so as to cope with a plurality of columns.
In a case where insert operation is performed to the duplicated ID sequential arrangement table expanded to a plurality of columns, it is required to maintain a state in which rows having the same ID are arranged in adjacent segments physically over a plurality of columns, and therefore, insertion is performed by a method different from that for a normal table.
A method of performing insert operation to the duplicated ID sequential arrangement table expanded to a plurality of columns is described with reference to a flow chart shown in
In an insert processing (22001) performing the insert operation, firstly, size of a row to be inserted and size of empty space of storage space are compared, and judgment (22002) whether the insertion can be performed or not is made. Here, if the insertion cannot be performed, error judgment is made (22027) and the insert processing is ended (22014). If the insertion can be performed, an insert portion is searched in the storage space. For example, in an example shown in
Next, judgment (22007) whether there is empty space in a first partial set of an i-th-order set of segments found in the judgment (22005) or i is a highest order or not is made, and if one of these conditions is satisfied, a lock of an index related to a corresponding table is gotten (22008), and then, judgment (22009) whether i=1 is true or not is made. If i=1 is true in the judgment (22009), a procedure of A (22028) is carried out. In the procedure of A (22028), firstly, the row is inserted into an empty space of a first-order set of segments (22011), the index is updated (22012), the lock of the index is released (22013) and the insert operation is ended (22014). An operation example in a case where a row of ID=2 and road type=national road is inserted according to the procedure of A (22028) is shown in
On the other hand, in a case where i=1 is not true in the judgment (22009), a procedure of B (22029) is carried out. In the procedure of B (22029), since it is a case in which a first-order set of segments to which the row is inserted is not found in the judgment (22004), firstly, empty space is secured (22010) for the first-order set of segments. And then, the row is inserted (22011) into the empty space of the first-order set of segments, the index is updated (22012), the lock of the index is released (22013), and the insert operation is ended (22014). An operation example in a case where a row of ID=2 and road type=national road is inserted according to the procedure of B (22029) is shown in
In a case where both the conditions are not satisfied in the judgment (22007), an action moving a second or higher order set of segments to other empty space is carried out. Here, using a counter variable j, sequential empty space larger than a j-th-order set of segment is searched (22016) sequentially from j=highest order (22015). And then, judgment (22017) whether it is found or not is made, and if it is not found, 1 is subtracted from j (22018), and the search (22016) is repeated.
Here, in a case where j reaches 0, the procedure goes ahead in the judgment (22017). And then, the lock of the index related to the corresponding table is gotten (22019).
Next, judgment (22020) whether j=0 is true or not is made. In a case where j=0 is true in the judgment (22020), a procedure of C (22030) is carried out. In the procedure of C (22030), since j=0 is true in the judgment (22017), that is, sequential empty space larger than the first-order set of segments cannot be secured, the first-order set of segments is divided. Firstly, empty space for the first-order set of segments is secured (22021), the row is inserted (22022) into the empty space of the first-order set of segments, the index is updated (22012), the lock of the index is released (22013) and the insert operation is ended (22014). An operation example in a case where a row of ID=2 and road type=national road is inserted according to the procedure of C (22030) is shown in
In a case where j=0 is not true in the judgment (22020), a procedure of D (22031) is carried out. In the procedure of D (22031), since judgment that sequential empty space larger than a j-th-order set of segments higher than first-order is found in the judgment (22017) is made, the j-th-order set of segments is rearranged in a sequential space.
Firstly, sequential empty space larger than the j-th-order set of segments is secured (22023) in the empty space found in the search (22016), and then, the first-order set of segments is moved (22024) to n which is an insertion row in ascending order, the row is inserted (22025) into empty space of the first-order set of segments, the first-order set of segments is moved (22026) from n which is the insertion row or later in ascending order, the index is updated (22012), the lock of the index is released (22013), and the insert operation is ended (22014). An operation example in a case where a row of ID=2 and road type=national road is inserted according to the procedure of D (22031) is shown in
Finally, a duplicated ID sequential arrangement table identification table, a duplicated ID sequential arrangement table ID management table and a duplicated ID sequential arrangement table parent-child relation management table according to the present embodiment are shown in
Here, since the intersection table (21001) is stored as the duplicated ID sequential arrangement table with designating the road type column (21002) in
As an effective storage method of map data divided into sections (10001) shown in
As an example of an allocation method of the identification numbers, in
The Z-order storage method can be expanded to space of adjacent four or more sections. As an example thereof, an example of second-order Z-order storage handling adjacent 16 sections is shown in
By allocating identification numbers in second-order Z-shaped order, sections from 1 (29006) to 16 (29007) can be stored so as to be fetched collectively. And, by enlarging the Z-shape in the same manner, expansion obtaining such as a third-order Z-shape composed of adjacent 64 sections or a fourth-order Z-shape composed of adjacent 256 sections can be made.
The four sections included in the same first-order z-shape have a characteristic that values obtained by dividing (section number−1) by four are equal. The value obtained by adding 1 to this quotient is defined as a first-order Z-shape identifier. The first-order Z-shape identifiers (28008, 28009, 28010, 28011, 28012, 28013, 28014, 28015, 28016, 28017, 28018, 28019, 29020, 28021, 28022, 28023) are identifiers allocated according to the above definition.
In the same manner, as for the second-order Z-shape, one obtained by adding 1 to a quotient obtained by dividing (first-order Z-shape identifier—1) by four is referred to as a second-order Z-shape identifier. The numeric
The Z-order storage method handled in the present embodiment can be embodied as a special example of the duplicated ID sequential arrangement table expanded to a plurality of columns shown in the second embodiment irrespective of a storage order.
And, the fifth adjacent four sections (28012) are a set of segments (30015) of first-order Z-shaped order identifier=5 and four sections (30010, 30011, 30012, 30013) structuring this are arranged in sequential space as a first-order key column (30004) on the storage space (30003) in the same manner. As described above, by regarding the first-order Z-shape identifiers as the second-order key columns, it can be embodied as a special example of the duplicated ID sequential arrangement table expanded to two columns. Recursively, by regarding a n-th Z-shape identifiers as a n+1-th key columns, it can be embodied as a special example of a duplicated ID sequential arrangement table expanded to n+1 columns.
A duplicated ID sequential arrangement identification table, a duplicated ID sequential arrangement ID management table and a duplicated ID sequential arrangement table parent-child relation management table used in the present embodiment are described with reference to
In the duplicated ID sequential arrangement table parent-child relation management table (31007) used in the present embodiment, in addition to a table name (31008) column, a column name (31009) column and an order number (31011) column, a condition column (31010) column is provided so that it can be identified whether it follows to the Z-order storage method or not. The duplicated ID sequential arrangement table identification table (31001) is composed of a management number (31002) column and a table name (31003) column in the same manner as in the case of the second embodiment, and the duplicated ID sequential arrangement table ID management table (31004) is structured of a table name (31005) column and a column name (31006) column also in the same manner as in the case of the second embodiment.
Here, in order to arrange four vicinities adjacent in the first-order Z-shaped order adjacently with respect to the ID column (30002) of the intersection table (30001) in
Note that, in the present embodiment, the Z-order storage method is taken as an example, however, an N-order storage method arranging N-shaped order four vicinities in the same manner and a clock-order storage method arranging four vicinities clockwise can known easily by analogy. Also in the duplicated ID sequential arrangement table parent-child relation management table (31007) in the present embodiment, by designating the N-order storage method or the clock-order storage method in the condition column (31010), the N-order storage method or the clock-order storage method can be realized.
In embedded applications, since restriction of usable memory size is severe and a large buffer cannot be allocated like in DBMS for server, I/O performance of a storage device affects directly upon total search performance. Especially in car navigation system application, since a file structure optimized so that a conventional car navigation system can use sequential access of an optical disk device efficiently is used, even if a hard disk device of higher-speed is introduced, by occurrence of a random access, performance is degraded with respect to an optical disk. Therefore, it is necessary to perform a control so as to restrict the random access using the present invention.
Filing Document | Filing Date | Country | Kind | 371c Date |
---|---|---|---|---|
PCT/JP2005/016965 | 9/14/2005 | WO | 00 | 1/25/2008 |