CN103793401B - Set up the method and device of the shared index of multiple database table - Google Patents
Set up the method and device of the shared index of multiple database table Download PDFInfo
- Publication number
- CN103793401B CN103793401B CN201210424372.8A CN201210424372A CN103793401B CN 103793401 B CN103793401 B CN 103793401B CN 201210424372 A CN201210424372 A CN 201210424372A CN 103793401 B CN103793401 B CN 103793401B
- Authority
- CN
- China
- Prior art keywords
- key
- foreign
- index entry
- index
- identifier
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Expired - Fee Related
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2272—Management thereof
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a kind of foundation method and device for major key table He the shared index of at least one foreign-key table.In the method, first obtain major key index and the outer key index of each of at least one foreign-key table of major key table, then, in the outer key index of each foreign-key table, determine the external key index entry of at least one non-NULL.The external key index entry of each non-NULL determined by for, obtain the foreign key value of this external key index entry, and lookup includes the major key index entry of the Major key identical with this foreign key value in major key indexes, then, the major key index entry found is merged with the table identifier of this foreign-key table and the foreign-key table data record identifier of this external key index entry, shares index entry to be formed.
Description
Technical field
The present invention relates to the management technique of relational database, more particularly, to foundation, there is primary key-foreign key constraint many
The method and apparatus of the shared index of individual database table and including the pass with multiple database tables of primary key-foreign key constraint
It it is the method and apparatus inquiring about data in data base.
Background technology
Typically, for database table, it may be determined that certain attribute in the data record of this database table as major key, its
A data record in database table can be uniquely identified.Major key is for ensureing the entity integrity of database table.One number
According to storehouse table only one of which major key, and the value of major key can not be null value (Null).
In relational database, it is possible to use multiple database tables are associated together by major key and external key, such multiple
Database table can be considered to have primary key-foreign key constraint.Usually, if the major key of database table 1 goes out in database table 2
Existing, then this major key is the external key of database table 2.In this case, database table 1 is referred to as major key table, and database table 2 quilt
It is referred to as foreign-key table.External key can represent the relation between two database tables, and can be used for keeping the concordance of data.
Generally, major key and/or external key can the definition when creating database table.If defining external key, define the most further
The contingency table of external key and the association of renewal.Foreign-key table becomes reference list, and major key telogenesis is the table being cited.The association bag updated
Include cascade to delete and cascading update.If deleting or change certain the data record in major key table, with this in the most all foreign-key tables
The relevant data record of data record all will be deleted or change.
Inquiry to the data record in database table for convenience, it is also possible to database table is set up index.Index can
To set up by the value of certain string in database table is ranked up.In relational database system, generally use B-tree knot
Structure represents the index of database table.Fig. 2 shows the schematic diagram of traditional index using B-tree structure.As in figure 2 it is shown, B-tree rope
Draw and include root node, intermediate node and leaf node.At root node, the index entry of storage may be used to indicate each intermediate node institute
The scope of corresponding index value, at intermediate node, the index entry of storage may be used to indicate the index corresponding to each leaf node
Value.At leaf node, the index entry of storage may be used to indicate the information of the data record corresponding with index value, and it can include index
Value (Key) and data record identifier (ROWID).Data record identifier can uniquely identify the data record in database table
Physical location, generally include the row information of row in the page information (Page) of the page of referred database table and instruction page
(Slot).
Major key table and foreign-key table are set up respectively major key index and outer key index.Major key indexes major key in major key table
The structure that value is ranked up, now, index value is the value of major key.Outer key index is that the value to foreign-key table China and foreign countries key is ranked up
Structure, now, index value is the value of external key.Major key table and foreign-key table are being established respectively major key index and the feelings of outer key index
Under condition, when causing own relevant with this data record owing to certain the data record in major key table is modified or being deleted
When data record in foreign-key table will be modified or delete, first the outer key index of all foreign-key tables is traveled through, according to quilt
The Major key corresponding to data record of the major key table of change or deletion, searches outer key index corresponding in each outer key index
, then according to the external key index entry searched, it is thus achieved that the data record of corresponding foreign-key table, to be modified or to delete behaviour
Make.Therefore, the data that no matter physical presence is relevant with the data record of the major key table being modified or deleting in foreign-key table
Record, all will travel through the outer key index of all foreign-key tables.
But, there is currently the trend that the quantity of the foreign-key table associated with major key table is increasing.If a major key table
It is associated with substantial amounts of foreign-key table, then deletes or during cascading update operation carrying out cascade, to the outer key index of all foreign-key tables
Traversal can devote a tremendous amount of time, and causes work efficiency to reduce.
Additionally, there is also so when carrying out data query in the relational database including major key table and multiple foreign-key table
Problem.When the data record corresponding to the inquiry of each Major key, it is required for the outer key index of multiple foreign-key tables is carried out
Traversal.When there is substantial amounts of foreign-key table, query time can be increased, causing poor Consumer's Experience.
Summary of the invention
According to an aspect of the invention, it is provided a kind of shared rope set up for major key table and at least one foreign-key table
The method drawn, including: obtain major key index and the outer key index of each of at least one foreign-key table described of described major key table；
For foreign-key table each described, in the outer key index of this foreign-key table, determine the external key index entry of at least one non-NULL；For
Determined by each of external key index entry of at least one non-NULL, obtain the foreign key value of this external key index entry；At described major key
Index is searched the major key index entry including the Major key identical with this foreign key value；And by the major key index entry that found with
The foreign-key table data record identifier of the table identifier of this foreign-key table and this external key index entry merges, to form described shared index
Shared index entry.
According to another aspect of the present invention, it is provided that a kind of method for inquiring about data in relational database, its
In, described relational database includes major key table and at least one foreign-key table, and described major key table and at least one external key described
Table has shares index, and wherein, the shared index entry in described shared index includes the table identifier of major key index entry, foreign-key table
With foreign-key table data record identifier, described method includes: receiving inquiry request, described inquiry request includes index value；Institute
State to share and index is searched the shared index entry including described index value；And according at least one in described shared index entry
Table identifier and corresponding data record identifier, obtain by each from the table identified by least one table identifier described
The data of position of data record identifier mark.
According to a further aspect of the invention, it is provided that a kind of set up for major key table and at least one foreign-key table share
The device of index, including index acquisition module, its be configured to obtain described major key table major key index and described at least one
The outer key index of each of foreign-key table；Index entry determines module, and it is configured to for foreign-key table each described, outer at this
In the outer key index of key table, determine the external key index entry of at least one non-NULL；Acquisition module, its be configured to for determined by
Each of the external key index entry of at least one non-NULL, obtains the foreign key value of this external key index entry；Searching module, it is configured to
The major key index entry including the Major key identical with this foreign key value is searched in described major key indexes；And merging module, its quilt
It is configured to remember the major key index entry found with the table identifier of this foreign-key table and the foreign-key table data of this external key index entry
Record identifier merges, to form the shared index entry of described shared index.
According to a further aspect of the invention, it is provided that a kind of device for inquiring about data in relational database, its
In, described relational database includes major key table and at least one foreign-key table, and described major key table and at least one external key described
Table has shares index, and wherein, the shared index entry in described shared index includes the table identifier of major key index entry, foreign-key table
With foreign-key table data record identifier, described device includes: request receiver module, and it is configured to receive inquiry request, described
Inquiry request includes index value；Index entry searches module, and it is configured in described shared index lookup and includes described index
The shared index entry of value；And data acquisition module, it is configured to according at least one the table mark in described shared index entry
Know symbol and data record identifier, obtain by respective data record from the table identified by least one table identifier described
The data of the position of identifier mark.
Accompanying drawing explanation
By combining accompanying drawing, disclosure illustrative embodiments is described in more detail, the disclosure above-mentioned and its
Its purpose, feature and advantage will be apparent from, wherein, in disclosure illustrative embodiments, and identical reference number
Typically represent same parts.
Fig. 1 shows the block diagram being suitable to the exemplary computer system/server 12 for realizing embodiment of the present invention；
Fig. 2 shows the schematic diagram of traditional index using B-tree structure；
Fig. 3 schematically shows foundation according to an embodiment of the invention for major key table and at least one external key
The flow chart of the method for the shared index of table；
Fig. 4 shows the schematic diagram of the shared index set up according to the method shown in Fig. 3；
Fig. 5 schematically shows foundation according to another embodiment of the invention outside major key table and at least one
The flow chart of the method for the shared index of key table；
Fig. 6 schematically shows according to an embodiment of the invention for inquiring about data in relational database
The flow chart of method；
Fig. 7 shows foundation according to an embodiment of the invention sharing for major key table and at least one foreign-key table
The schematic block diagram of the device of index；
Fig. 8 show according to another embodiment of the invention set up being total to for major key table and at least one foreign-key table
Enjoy the schematic block diagram of the device of index；
Fig. 9 shows the device for inquiring about data in relational database according to another embodiment of the invention
Schematic block diagram.
Detailed description of the invention
It is more fully described the preferred implementation of the disclosure below with reference to accompanying drawings.Although accompanying drawing shows the disclosure
Preferred implementation, however, it is to be appreciated that may be realized in various forms the disclosure and the embodiment party that should not illustrated here
Formula is limited.On the contrary, it is provided that these embodiments are to make the disclosure more thorough and complete, and can be by the disclosure
Scope intactly conveys to those skilled in the art.
Person of ordinary skill in the field knows, the present invention can be implemented as system, method or computer program.
Therefore, the disclosure can be to be implemented as following form, it may be assumed that can be completely hardware, can also be that software (includes completely
Firmware, resident software, microcode etc.), it is also possible to it is the form of hardware and software combination, referred to generally herein as " circuit ", " mould
Block " or " system ".Additionally, in certain embodiments, the present invention is also implemented as at one or more computer-readable mediums
In the form of computer program, this computer-readable medium comprises computer-readable program code.
The combination in any of one or more computer-readable medium can be used.Computer-readable medium can be to calculate
Machine readable signal medium or computer-readable recording medium.Computer-readable recording medium can be such as but not limit
In system, device or the device of electricity, magnetic, optical, electromagnetic, infrared ray or quasiconductor, or above combination.Calculate
The more specifically example (non exhaustive list) of machine readable storage medium storing program for executing includes: have the electrical connection, just of one or more wire
Take formula computer disk, hard disk, random-access memory (ram), read only memory (ROM), erasable type read-only storage able to programme
Device (EPROM or flash memory), optical fiber, portable compact disc read only memory (CD-ROM), light storage device, magnetic memory device,
Or the combination of above-mentioned any appropriate.In this document, computer-readable recording medium can be any to comprise or store journey
The tangible medium of sequence, this program can be commanded execution system, device or device and use or in connection.
The data signal that computer-readable signal media can include in a base band or propagate as a carrier wave part,
Wherein carry computer-readable program code.The data signal of this propagation can take various forms, including but
It is not limited to electromagnetic signal, optical signal or the combination of above-mentioned any appropriate.Computer-readable signal media can also is that
Any computer-readable medium beyond computer-readable recording medium, this computer-readable medium can send, propagate or
Transmission is for by instruction execution system, device or device use or program in connection.
The program code comprised on computer-readable medium can with any suitable medium transmission, including but do not limit
In wireless, electric wire, optical cable, RF etc., or the combination of above-mentioned any appropriate.
The computer for performing present invention operation can be write with one or more programming languages or a combination thereof
Program code, described programming language includes object oriented program language such as Java, Smalltalk, C++,
Also include that the process type programming language of routine is such as " C " language or similar programming language.Program code is permissible
Perform the most on the user computer, perform the most on the user computer, as the execution of independent software kit, a portion
Part on the user computer is divided to perform the most on the remote computer or perform on remote computer or server completely.?
Relate in the situation of remote computer, remote computer can by the network of any kind include LAN (LAN) or
Wide area network (WAN) is connected to subscriber computer, or, it may be connected to outer computer (such as utilizes Internet service to carry
Internet connection is passed through) for business.
Below with reference to the embodiment of the present invention method, device (system) and the flow chart of computer program and/or
Block diagram describes the present invention.Should be appreciated that each square frame in flow chart and/or each square frame of block diagram and flow chart and/or block diagram
Combination, can be realized by computer program instructions.These computer program instructions can be supplied to general purpose computer, special
Computer or the processor of other programmable data processing means, thus produce a kind of machine, these computer program instructions
Performed by computer or other programmable data processing means, create in the square frame in flowchart and/or block diagram and advise
The device of fixed function/operation.
These computer program instructions can also be stored in and can make computer or other programmable data processing means
In the computer-readable medium worked in a specific way, so, the instruction being stored in computer-readable medium just produces one
Command device (the instruction of the function/operation of regulation in the individual square frame included in flowchart and/or block diagram
Means) manufacture (manufacture).
Computer program instructions can also be loaded into computer, other programmable data processing means or miscellaneous equipment
On so that on computer, other programmable data processing means or miscellaneous equipment, perform sequence of operations step, in terms of producing
The process that calculation machine realizes, so that the instruction performed on computer or other programmable device can provide flowchart
And/or the process of the function/operation of regulation in the square frame in block diagram.
Fig. 1 shows the block diagram being suitable to the exemplary computer system/server 12 for realizing embodiment of the present invention.
The computer system/server 12 that Fig. 1 shows is only an example, should be to the function of the embodiment of the present invention and range
Bring any restriction.
As it is shown in figure 1, computer system/server 12 shows with the form of universal computing device.Computer system/service
The assembly of device 12 can include but not limited to: one or more processor or processing unit 16, system storage 28, connects
The bus 18 of different system assembly (including system storage 28 and processing unit 16).
Bus 18 represents one or more in a few class bus structures, including memory bus or Memory Controller,
Peripheral bus, AGP, processor or use any bus-structured local bus in multiple bus structures.Lift
For example, these architectures include but not limited to ISA(Industry Standard Architecture) bus, MCA (MAC)
Bus, enhancement mode isa bus, VESA's (VESA) local bus and periphery component interconnection (PCI) bus.
Computer system/server 12 typically comprises various computing systems computer-readable recording medium.These media can be to appoint
What usable medium that can be accessed by computer system/server 12, including volatibility and non-volatile media, movably and
Immovable medium.
System storage 28 can include the computer system-readable medium of form of volatile memory, such as random access memory
Memorizer (RAM) 30 and/or cache memory 32.Computer system/server 12 may further include other and can move
Dynamic/immovable, volatile/non-volatile computer system storage medium.Being only used as citing, storage system 34 may be used for
Read and write immovable, non-volatile magnetic media (Fig. 1 does not shows, commonly referred to " hard disk drive ").Although Fig. 1 does not shows
Go out, it is provided that for the disc driver that removable non-volatile magnetic disk (such as " floppy disk ") is read and write, and to removable
The CD drive that anonvolatile optical disk (such as CD-ROM, DVD-ROM or other light medium) is read and write.In these cases,
Each driver can be connected with bus 18 by one or more data media interfaces.Memorizer 28 can include at least one
Individual program product, this program product has one group of (for example, at least one) program module, and these program modules are configured to perform
The function of various embodiments of the present invention.
There is the program/utility 40 of one group of (at least one) program module 42, such as memorizer 28 can be stored in
In, such program module 42 includes but not limited to operating system, one or more application program, other program
Module and routine data, potentially include the realization of network environment in each or certain combination in these examples.Program mould
Block 42 generally performs the function in embodiment described in the invention and/or method.
Computer system/server 12 can also be with one or more external equipment 14(such as keyboard, sensing equipment, aobvious
Show device 24 etc.) communication, also can enable a user to the equipment mutual with this computer system/server 12 with one or more and lead to
Letter, and/or any can set with what other calculating equipment one or more communicated with making this computer system/server 12
Standby (such as network interface card, modem etc.) communicates.This communication can be passed through input/output (I/O) interface 22 and carry out.And
And, computer system/server 12 can also be by network adapter 20 and one or more network (such as LAN
(LAN), wide area network (WAN) and/or public network, such as the Internet) communication.As it can be seen, network adapter 20 passes through bus
18 communicate with other module of computer system/server 12.It should be understood that although not shown in, can be in conjunction with computer
Systems/servers 12 uses other hardware and/or software module, includes but not limited to: at microcode, device driver, redundancy
Reason unit, external disk drive array, RAID system, tape drive and data backup storage system etc..
Fig. 3 schematically shows foundation according to an embodiment of the invention for major key table and at least one external key
The flow chart of the method for the shared index of table.Below in conjunction with the accompanying drawings, the present embodiment is described in detail.
The method of the present embodiment can apply to include the relational database of major key table and at least one foreign-key table.
As it is shown on figure 3, in step S310, obtain the major key index of major key table, and each of at least one foreign-key table
Outer key index.Index creating major key table and can be set up during foreign-key table, it is also possible to create major key table and foreign-key table it
Rear foundation.The major key index set up and outer key index can be as shown in Figure 2.The major key of storage on the leaf node of major key index
Index entry includes Major key and major key table data record identifier, and wherein major key table data record identifier uniquely identify master
The physical location of the data record in key table.On the leaf node of key index, the external key index entry of storage includes that foreign key value is with outer outside
Key table data record identifier, wherein foreign-key table data record identifier uniquely identify the thing of the data record in foreign-key table
Reason position.Major key and foreign key value have identical span.
Then, for each foreign-key table, following operation is performed.In step S320, in the outer key index of foreign-key table
Determine the external key index entry of at least one non-NULL.Here, the external key index entry of non-NULL refers to foreign key value and foreign-key table data record
Identifier is all the index entry of non-null value.If external key index entry is empty, then it represents that do not have corresponding data to remember in foreign-key table
Record.This step can carry out traveling through performing by the external key index entry of external key index.
Then, for the external key index entry of each non-NULL, following operation is carried out.In step S330, obtain outer key index
The foreign key value of item, and in step S340, in major key indexes, search the major key index entry including the Major key identical with foreign key value.
Then, in step S350, by table identifier and the foreign-key table of external key index entry of the major key index entry that found and foreign-key table
Data record identifier merges, and shares index entry to be formed.So, the shared index entry formed can include Major key,
Major key table data record identifier, the table identifier of foreign-key table and foreign-key table data record identifier.Alternatively, formed is total to
Enjoy index and may also include the table identifier of major key table.Table identifier can be the title of table.
After the operation that the external key index entry of all non-NULLs has been carried out above-mentioned steps S330 to S350, define for leading
Key table and the shared index of single foreign-key table.
After the operation that all foreign-key tables have been carried out above-mentioned steps S320 to S350, can be by all of for major key table
It is merged into a shared index entry with the shared index entry in the shared index of single foreign-key table with identical Major key, thus shape
Become the shared index for major key table and at least one foreign-key table.
By above description it can be seen that the method for the present embodiment can be by a major key index and at least one external key rope
Draw and be merged into one and share index, thus by only access a shared index entry can be realized as major key table and at least one outside
Referencing operation between key table.It addition, when carrying out cascading update or cascade is deleted, it is possible to quickly determine and will be updated or delete
The data record of the foreign-key table removed, and without the outer key index of all foreign-key tables is traveled through, thus it is greatly saved operation
Time, improve efficiency.
Fig. 4 shows the schematic diagram of the shared index set up according to the method shown in Fig. 3.As shown in Figure 4, index is shared
Also using B-tree structure, it includes root node, intermediate node and leaf node.The shared rope of storage at root node and intermediate node
Draw the major key index entry that Xiang Keyu stores at the corresponding node that major key indexes identical, and at leaf node the shared index of storage
Item includes Major key (Key), major key table data record identifier (ROWID=(Page, Slot)), the table of at least one foreign-key table
Identifier (Foreign Key Table ID) and at least one foreign-key table data record identifier (ROWID=(Page,
Slot)).Certainly, shared index entry may also comprise the table identifier (Primary Key Table ID) of major key table.
Fig. 5 schematically shows foundation according to another embodiment of the invention outside major key table and at least one
The flow chart of the method for the shared index of key table.Below in conjunction with the accompanying drawings, the present embodiment is described in detail, wherein for front
The part that face embodiment is identical, pays identical reference, and suitably the description thereof will be omitted.
The method of the embodiment shown in Fig. 5 is to add on the basis of the method for the embodiment shown in Fig. 3 and share rope
That draws item updates relevant operation.
As it is shown in figure 5, after establishing shared index, in step S510, monitor the external key rope of at least one foreign-key table
Draw the change of China and foreign countries' key index item.In the present embodiment, the change of external key index entry includes following at least one: outer key index
Item is non-NULL from space-variant；External key index entry becomes empty from non-NULL；Foreign-key table data record identifier with amendment external key index entry.
When with the addition of new data record in foreign-key table, corresponding external key index entry will be non-NULL from space-variant.At this
In the case of Zhong, foreign key value and foreign-key table data record identifier in external key index entry are non-null value.
When deleting certain data record in foreign-key table, corresponding external key index entry will become empty from non-NULL.At this
In the case of Zhong, foreign key value and foreign-key table data record identifier in external key index entry are null value.
When certain data record physical location in foreign-key table changes, the corresponding external key in external key index entry
Table data record identifier will be revised to reflect that the new physical location of this data record.
Then, when any one external key index entry changes, in step S520, update corresponding shared index entry.
In this renewal step, first, obtain table identifier and the foreign key value thereof of the foreign-key table belonging to external key index entry changed, connect
, shared index is searched the shared index entry including the shared index value identical with acquired foreign key value, and according to this
The change of external key index entry, is updated the shared index entry found.Specifically, outside the change of external key index entry is
Key index item from space-variant be non-NULL time, corresponding shared index entry adds table identifier and the change of acquired foreign-key table
The foreign-key table data record identifier of external key index entry.When the change of external key index entry is that external key index entry becomes from non-NULL
Time empty, outside the table identifier deleting acquired foreign-key table from corresponding shared index entry and the external key index entry changed
Key table data record identifier.When the change of external key index entry is the foreign-key table data record identifier of amendment external key index entry
Time, replace and the table of acquired foreign-key table with the foreign-key table data record identifier that have modified in corresponding shared index entry
The former foreign-key table data record identifier that identifier is corresponding.
By above description it can be seen that the method for the present embodiment can the most dynamically maintain shares index, to protect
Card shares the concordance of index and data record.
Fig. 6 schematically shows according to an embodiment of the invention for inquiring about data in relational database
The flow chart of method.In the present embodiment, relational database can include major key table and at least one foreign-key table, wherein for major key
Table and at least one foreign-key table are set up and are shared index, wherein share the shared index entry in index and can include major key index entry, outer
The table identifier of key table and foreign-key table data record identifier.Shared index can use the method for the embodiment shown in Fig. 3 or Fig. 5
Set up.
As shown in Figure 6, in step S610, receiving inquiry request, this inquiry request can include index value.At the present embodiment
In, the scope of index value is identical with the scope of Major key.Then, in step S620, according to the index value in inquiry request, altogether
Enjoy and index is searched the shared index entry including this index value.After have found required shared index entry, in step S630, root
At least one table identifier comprised according to this shared index entry and corresponding data record identifier, identify from by table identifier
Table in obtain the data in the position by corresponding data record identifier mark.
In the present embodiment, at least one table identifier at least includes the table identifier of major key table.If sharing index entry
Only include table identifier and the major key table data record identifier of major key table, then major key table data record identifier from major key table
The position identified obtains data record, and returns the data record of this acquirement.If sharing index entry to include major key table
Table identifier, major key table data record identifier, the table identifier of at least one foreign-key table and foreign-key table data record identifier,
From major key table, then obtain the data record in the position identified by major key table data record identifier, and marked from each
The foreign-key table known obtains the data record of the position identified by respective foreign-key table data record identifier, then by these
Data record is combined, and returns the data record after combination.
By above description it can be seen that the method for the present embodiment can be only according to a shared index entry, it is achieved to bag
Include the data query of the relational database of major key table and at least one foreign-key table.
Fig. 7 shows foundation according to an embodiment of the invention sharing for major key table and at least one foreign-key table
The schematic block diagram of the device of index.Below according to accompanying drawing, the present embodiment is described in detail, wherein for above implement
The part that example is identical, suitably the description thereof will be omitted.
As it is shown in fig. 7, the device 700 setting up the shared index being used for major key table and at least one foreign-key table of the present embodiment
Can include that index acquisition module 701, index entry determine module 702, acquisition module 703, search module 704 and merge module 705.
In the device 700 of the present embodiment, index acquisition module 701 is configured to obtain the major key index of major key table, and
The outer key index of each foreign-key table.Then, index entry determines that module 702, in the outer key index of each foreign-key table, determines
The external key index entry of at least one non-NULL.As it was previously stated, the external key index entry of non-NULL refers to foreign key value and foreign-key table data record
Identifier is all the index entry of non-null value.After each outer key index is determined the external key index entry of non-NULL, for often
The external key index entry of one non-NULL, acquisition module 703 obtains the foreign key value of this external key index entry.Obtain according to by acquisition module 703
The foreign key value obtained, lookup module 704 is searched in the major key index obtained by index acquisition module 701 and is included and this foreign key value phase
The major key index entry of same Major key.Then, merge module 705 to be identified by the table of the major key index entry found with foreign-key table
The foreign-key table data record identifier of symbol and external key index entry merges, and shares index entry to be formed.Therefore, shared index entry can wrap
Include Major key, major key table data record identifier, the table identifier of foreign-key table and foreign-key table data record identifier.Alternatively,
Shared index entry may also include the table identifier of major key table.
Fig. 8 show according to another embodiment of the invention set up being total to for major key table and at least one foreign-key table
Enjoy the schematic block diagram of the device 800 of index.
As shown in Figure 8, the device 800 of the present embodiment except include indexing acquisition module 701, index entry determine module 702,
Outside acquisition module 703, lookup module 704 and merging module 705, may also include monitoring module 801 and more new module 802.
In the device 800 shown in Fig. 8, monitoring module 801 monitors the outer key index China and foreign countries key rope of at least one foreign-key table
Draw the change of item.In the present embodiment, the change of external key index entry includes following at least one: external key index entry from space-variant is
Non-NULL；External key index entry becomes empty from non-NULL；Foreign-key table data record identifier with amendment external key index entry.
When monitoring module 801 monitor any one external key index entry change time, more new module 802 updates accordingly
Share index entry.In more new module 802, acquiring unit 8021 obtains the table of the foreign-key table belonging to external key index entry changed
Identifier and foreign key value thereof, and searched in shared index include be total to identical with acquired foreign key value by searching unit 8022
Enjoy the shared index entry of index value.Then, updating block 8023 is according to the change of the external key index entry monitored, to single by searching
The shared index entry that unit 8022 finds is updated.
Specifically, updating block 8023 can include adding device, and it monitors changing of external key index entry at monitoring module 801
Change be external key index entry from space-variant into non-NULL time, shared index entry adds table identifier and the change of acquired foreign-key table
The foreign-key table data record identifier of external key index entry.
Updating block 8023 may also include deletion unit, and its change monitoring external key index entry at monitoring module 801 is
External key index entry, when non-NULL becomes sky, is deleted the table identifier of acquired foreign-key table from shared index entry and changes
The foreign-key table data record identifier of external key index entry.
Updating block 8023 may also include replacement unit, and its change monitoring external key index entry at monitoring module 801 is
When revising the foreign-key table data record identifier of external key index entry, with the foreign-key table data record that have modified in shared index entry
Identifier replaces the former foreign-key table data record identifier corresponding with the table identifier of acquired foreign-key table.
Those of ordinary skill in the art is it is understood that the device 700,800 of the embodiment shown in Fig. 7 and Fig. 8 can include
In relational database management system.
Fig. 9 shows the device for inquiring about data in relational database according to another embodiment of the invention
The schematic block diagram of 900.Below in conjunction with the accompanying drawings, the present embodiment is described in detail.
In the present embodiment, relational database includes major key table and at least one foreign-key table, and for major key table and extremely
A few foreign-key table sets up and shares index, wherein shares the shared index entry in index and can include major key index entry, foreign-key table
Table identifier and foreign-key table data record identifier.The shared index of major key table and at least one foreign-key table can be by Fig. 7 or Fig. 8 institute
The device 700,800 for major key table and the shared index of at least one foreign-key table of setting up shown is set up, and at relational database
Middle maintenance.
As it is shown in figure 9, the device 900 of the present embodiment can include asking receiver module 901, index entry to search module 902 and
Data acquisition module 903.
In the device 900 of the present embodiment, request receiver module 901 receives the inquiry request including index value.Index value
Scope identical with the scope of Major key.Then, index entry lookup module 902 is searched in shared index and is included this index value
Share index entry, and by data acquisition module 903 according at least one table identifier in the shared index entry found and
Data record identifier, from by the number obtained the table identified in the position identified by respective data record identifier
According to.
Those of ordinary skill in the art is it is understood that the device 900 of the embodiment shown in Fig. 9 may be included in relation data
In base management system.Additionally, the device 900 of the embodiment shown in Fig. 9 can with the device 700 of the embodiment shown in Fig. 7 and Fig. 8,
800 is combined.
Flow chart and block diagram in accompanying drawing show the system of multiple embodiments according to the present invention, method and computer journey
Architectural framework in the cards, function and the operation of sequence product.In this, each square frame in flow chart or block diagram can generation
One module of table, program segment or a part for code, a part for described module, program segment or code comprises one or more use
Executable instruction in the logic function realizing regulation.It should also be noted that at some as in the realization replaced, square frame is marked
The function of note can also occur to be different from the order marked in accompanying drawing.Such as, two continuous print square frames can essentially base
Originally being performed in parallel, they can also perform sometimes in the opposite order, and this is depending on involved function.It is also noted that
It is, the combination of the square frame in each square frame in block diagram and/or flow chart and block diagram and/or flow chart, can be with performing rule
The special hardware based system of fixed function or operation realizes, or can be by the group of specialized hardware with computer instruction
Incompatible realization.
Being described above various embodiments of the present invention, described above is exemplary, and non-exclusive, and also
It is not limited to disclosed each embodiment.In the case of the scope and spirit without departing from illustrated each embodiment, for this skill
For the those of ordinary skill in art field, many modifications and changes will be apparent from.The selection of term used herein, purport
Explaining the principle of each embodiment, actual application or the technological improvement to the technology in market best, or making this technology lead
Other those of ordinary skill in territory is understood that each embodiment disclosed herein.
Claims (18)
1. set up the method for major key table with the shared index of at least one foreign-key table, including:
Obtain major key index and the outer key index of each of at least one foreign-key table described of described major key table；
For foreign-key table each described:
In the outer key index of this foreign-key table, determine the external key index entry of at least one non-NULL；
Each of the external key index entry of at least one non-NULL determined by for:
Obtain the foreign key value of this external key index entry；
The major key index entry including the Major key identical with this foreign key value is searched in described major key indexes；And
By table identifier and the foreign-key table data record of this external key index entry of the major key index entry that found and this foreign-key table
Identifier merges, to form the shared index entry of described shared index.
Method the most according to claim 1, also includes:
The change of the outer key index China and foreign countries key index item of monitoring at least one foreign-key table described；And
When any one external key index entry changes, update corresponding shared index entry.
Method the most according to claim 2, wherein, the change of described external key index entry includes at least the one of following change
Individual: external key index entry is non-NULL from space-variant；External key index entry becomes empty from non-NULL；Foreign-key table data with amendment external key index entry
Record identifier.
Method the most according to claim 2, wherein, described renewal step includes:
Obtain table identifier and the foreign key value thereof of the foreign-key table belonging to external key index entry changed；
The shared index entry including the shared index value identical with acquired foreign key value is searched in described shared index；And
According to the change of described external key index entry, the shared index entry searched is updated.
Method the most according to claim 4, wherein, when the change of described external key index entry is that external key index entry is from space-variant
During non-NULL, the step that the described shared index entry to being searched is updated includes:
The table identifier adding acquired foreign-key table in described shared index entry and the external key of external key index entry changed
Table data record identifier.
Method the most according to claim 4, wherein, when the change of described external key index entry is that external key index entry is from non-space-variant
During for sky, the step that the described shared index entry to being searched is updated includes:
The table identifier deleting acquired foreign-key table from described shared index entry and the external key of external key index entry changed
Table data record identifier.
Method the most according to claim 4, wherein, when the change of described external key index entry is revised outside external key index entry
During key table data record identifier, the step that the described shared index entry to being searched is updated includes:
Described shared index entry is replaced and the table of acquired foreign-key table with the foreign-key table data record identifier that have modified
The former foreign-key table data record identifier that identifier is corresponding.
8. according to the method described in claim 1 to 7 any one, wherein, described shared index entry also includes the table of major key table
Identifier.
9. for a method of inquiring about data in relational database, wherein, described relational database includes major key table and extremely
Lack a foreign-key table, and described major key table and at least one foreign-key table described have according to claim 1 to 8 any one
The shared index that method is set up, wherein, the shared index entry in described shared index includes the table mark of major key index entry, foreign-key table
Knowing symbol and foreign-key table data record identifier, described method includes:
Receiving inquiry request, described inquiry request includes index value；
The shared index entry including described index value is searched in described shared index；And
According at least one table identifier in described shared index entry and corresponding data record identifier, from by described at least
The table of one table identifier mark obtains the data in the position identified by respective data record identifier.
10. set up the device for major key table with the shared index of at least one foreign-key table, including:
Index acquisition module, its major key index being configured to obtain described major key table and at least one foreign-key table described each
Individual outer key index；
Index entry determines module, and it is configured to, for foreign-key table each described, in the outer key index of this foreign-key table, determine
The external key index entry of at least one non-NULL；
Acquisition module, its be configured to for determined by each of external key index entry of at least one non-NULL, obtain this outer
The foreign key value of key index item；
Searching module, it is configured to search the major key rope including the Major key identical with this foreign key value in described major key indexes
Draw item；And
Merging module, it is configured to the table identifier of the major key index entry that found and this foreign-key table and this outer key index
The foreign-key table data record identifier of item merges, to form the shared index entry of described shared index.
11. devices according to claim 10, also include:
Monitoring module, it is configured to the change of the key index item at home and abroad of the outer key index of at least one foreign-key table described in monitoring；With
And
More new module, it is configured to when any one external key index entry changes, and updates corresponding shared index entry.
12. devices according to claim 11, wherein, the change of described external key index entry includes at least the one of following change
Individual: external key index entry is non-NULL from space-variant；External key index entry becomes empty from non-NULL；Foreign-key table data with amendment external key index entry
Record identifier.
13. devices according to claim 11, wherein, described more new module includes:
Acquiring unit, its table identifier and external key thereof of being configured to obtain the foreign-key table belonging to external key index entry changed
Value；
Searching unit, it is configured in described shared index lookup and includes the shared index identical with acquired foreign key value
The shared index entry of value；And
Updating block, it is configured to the change according to described external key index entry, is updated the shared index entry searched.
14. devices according to claim 13, wherein, when the change of described external key index entry is that external key index entry is from space-variant
During for non-NULL, described updating block includes:
Adding device, it is configured to the table identifier adding acquired foreign-key table in described shared index entry and changes
The foreign-key table data record identifier of external key index entry.
15. devices according to claim 13, wherein, when the change of described external key index entry is that external key index entry is from non-NULL
When becoming sky, described updating block includes:
Deleting unit, it is configured to the table identifier deleting acquired foreign-key table from described shared index entry and changes
The foreign-key table data record identifier of external key index entry.
16. devices according to claim 13, wherein, when the change of described external key index entry is amendment external key index entry
During foreign-key table data record identifier, described updating block includes:
Replacement unit, its be configured in described shared index entry with the foreign-key table data record identifier that have modified replace with
The former foreign-key table data record identifier that the table identifier of acquired foreign-key table is corresponding.
17. according to the device described in claim 10 to 16 any one, and wherein, described shared index entry also includes major key table
Table identifier.
18. 1 kinds for inquiring about the devices of data in relational database, wherein, described relational database includes major key table and extremely
Lack a foreign-key table, and described major key table and at least one foreign-key table described have according to claim 1 to 8 any one
The shared index that method is set up, wherein, the shared index entry in described shared index includes the table mark of major key index entry, foreign-key table
Knowing symbol and foreign-key table data record identifier, described device includes:
Asking receiver module, it is configured to receive inquiry request, and described inquiry request includes index value；
Index entry searches module, and it is configured in described shared index search the shared index entry including described index value；
And
Data acquisition module, it is configured to according at least one table identifier in described shared index entry and data record mark
Know symbol, obtain in the position identified by respective data record identifier from the table identified by least one table identifier described
The data at place.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210424372.8A CN103793401B (en) | 2012-10-30 | 2012-10-30 | Set up the method and device of the shared index of multiple database table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210424372.8A CN103793401B (en) | 2012-10-30 | 2012-10-30 | Set up the method and device of the shared index of multiple database table |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN103793401A CN103793401A (en) | 2014-05-14 |
| CN103793401B true CN103793401B (en) | 2016-12-21 |
Family
ID=50669084
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201210424372.8A Expired - Fee Related CN103793401B (en) | 2012-10-30 | 2012-10-30 | Set up the method and device of the shared index of multiple database table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103793401B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110019212B (en) * | 2017-11-29 | 2021-06-18 | 杭州海康威视数字技术股份有限公司 | Data processing method and device and database server |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105096202A (en) * | 2014-05-19 | 2015-11-25 | 中国石油化工股份有限公司 | Method for processing resource evaluation data |
| CN108319451B (en) * | 2017-01-16 | 2021-09-07 | 医渡云（北京）技术有限公司 | Medical data supplementing method and device |
| CN109660638A (en) * | 2018-12-10 | 2019-04-19 | 中共中央办公厅电子科技学院 | A kind of data store organisation and DNS data storage system |
| CN113641685B (en) * | 2021-10-18 | 2022-04-08 | 中国民用航空总局第二研究所 | Data processing system for guiding aircraft |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1560766A (en) * | 2004-02-17 | 2005-01-05 | 北京大学 | Key constrained automatic generation method for expandable mark language mode |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20070143250A1 (en) * | 2005-12-20 | 2007-06-21 | Beckman Coulter, Inc. | Adaptable database system |
-
2012
- 2012-10-30 CN CN201210424372.8A patent/CN103793401B/en not_active Expired - Fee Related
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1560766A (en) * | 2004-02-17 | 2005-01-05 | 北京大学 | Key constrained automatic generation method for expandable mark language mode |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110019212B (en) * | 2017-11-29 | 2021-06-18 | 杭州海康威视数字技术股份有限公司 | Data processing method and device and database server |
Also Published As
| Publication number | Publication date |
|---|---|
| CN103793401A (en) | 2014-05-14 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11468103B2 (en) | Relational modeler and renderer for non-relational data | |
| US9646030B2 (en) | Computer-readable medium storing program and version control method | |
| JP6262874B2 (en) | Database implementation method | |
| JP6045706B2 (en) | Data processing system, data processing method, and data processing apparatus | |
| CN102693302B (en) | Quick file comparison method, system and client side | |
| JP4856627B2 (en) | Partial query caching | |
| CN103793401B (en) | Set up the method and device of the shared index of multiple database table | |
| CN103902653B (en) | A kind of method and apparatus for building data warehouse table genetic connection figure | |
| US9116899B2 (en) | Managing changes to one or more files via linked mapping records | |
| CN102663076B (en) | Method for processing file data | |
| CN106933837A (en) | A kind of database table model and creation method | |
| CN104216894A (en) | Method and system for data query | |
| CN104160381A (en) | Managing tenant-specific data sets in a multi-tenant environment | |
| CN104021123A (en) | Method and system for data transfer | |
| CN103514229A (en) | Method and device used for processing database data in distributed database system | |
| CN104573022A (en) | Data query method and device for HBase | |
| CN102375827B (en) | Method for fast loading versioned electricity network model database | |
| US20070083543A1 (en) | XML schema template builder | |
| CN102158533B (en) | Distributed web service selection method based on QoS (Quality of Service) | |
| CN111414410B (en) | Data processing method, device, equipment and storage medium | |
| US20230267116A1 (en) | Translation of tenant identifiers | |
| CN114077680A (en) | Method, system and device for storing graph data | |
| CN109903050A (en) | Transaction De-weight method, transaction building method, equipment and storage medium | |
| CN106802928B (en) | Power grid historical data management method and system | |
| CN103365923A (en) | Method and device for assessing partition schemes of database |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C14 | Grant of patent or utility model | ||
| GR01 | Patent grant | ||
| CF01 | Termination of patent right due to non-payment of annual fee | ||
| CF01 | Termination of patent right due to non-payment of annual fee |
Granted publication date: 20161221 Termination date: 20201030 |