US20120179698A1 - Multiple Sparse Index Intelligent Table Organization - Google Patents
Multiple Sparse Index Intelligent Table Organization Download PDFInfo
- Publication number
- US20120179698A1 US20120179698A1 US13/005,024 US201113005024A US2012179698A1 US 20120179698 A1 US20120179698 A1 US 20120179698A1 US 201113005024 A US201113005024 A US 201113005024A US 2012179698 A1 US2012179698 A1 US 2012179698A1
- Authority
- US
- United States
- Prior art keywords
- sparse
- indexes
- bitmap
- computer
- row
- 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.)
- Granted
Links
Images
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/21—Design, administration or maintenance of databases
- G06F16/211—Schema design and management
Definitions
- the field of invention relates to databases.
- the field of invention relates to reorganizing a table according to multiple sparse indexes.
- Databases use tables to organize information. Each table consists of a number of rows, each of which corresponds to a single database record. Keys are used to organize the database. Each database table has one or more columns designated as the primary key. The value of this key is unique for each record in the database. For example, assume a table exists that contains personnel information on every employee in an organization. An example of a primary key in this scenario would be an employee serial number unique to each employee.
- a database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
- the disk space required to store the index is typically less than the storage of the table (since indexes usually contain only the key-fields according to which the table is to be arranged, and excludes all the other details of the table). Thus, indexes can be stored in memory in instances where the entire table might not fit.
- Database tables are traditionally organized physically on a storage medium (such as a hard disk) according to some index (e.g., a primary key such as employee serial number). In most instances, rows within the database table are processed on a key ordered basis. Thus, physically storing database tables on the disk in keyword order minimizes I/O time, since multiple rows can be brought into memory with each I/O operation.
- a storage medium such as a hard disk
- some index e.g., a primary key such as employee serial number
- a sparse index is an index with a key and pointers to rows in a database table for a subset of data in the table. Every key in the index is associated via pointers with a row in the database table. However, not every row in the table may be pointed to by a key in the index.
- Application designers have begun to create multiple sparse SQL indexes to tune particular application queries. For many highly tuned applications, hundreds or even thousands of sparse indexes may be created to match many of the application queries.
- the disclosure and claims herein are directed to a method, system and computer program product for reorganizing a table according to multiple sparse indexes.
- the I/O performance attributes of database queries will be improved, since the reorganized table tends to group rows corresponding to each of the sparse indexes more closely together.
- a method for reorganizing a database table residing on a computer system according to a plurality of sparse indexes begins by constructing a bitmap for each row entry (i.e., a specific row in a table) within the database table, the bitmap having n bit positions. Each of the n bit positions corresponds to the referencing state of one of the plurality of sparse indexes. The method then sorts the row entries within the database table based upon the value of the bitmap.
- the step of constructing a bitmap further comprises the steps of: 1) initializing the bitmap to “0”; 2) for each row within the database table, determining if any of the plurality of sparse indexes point to the row; and 3) if any of the plurality of sparse indexes point to the row, setting the bit positions within the bitmap corresponding to the reference sparse indexes associated with the row to “1”).
- the bit positions within the bitmap are organized such that the most significant bit positions within the bitmap are assigned to the most popular indexes within the plurality of sparse indexes, and the least significant bit positions within the bitmap are assigned to the least popular indexes.
- the popularity of each of the sparse indexes is determined by monitoring query use of the sparse index by at least one counter. In another embodiment, the popularity of each of the sparse indexes is determined by computing and saving the average job priority of queries associated with the sparse index, wherein the highest average job priority sparse indexes are the most popular indexes, and the lowest average job priority indexes are the least popular indexes. In one embodiment, the bit positions within the bitmap are organized such that the most significant bit positions within the bitmap are assigned to the indexes which select the most rows within the plurality of sparse indexes, and the least significant bit positions within the bitmap are assigned to the indexes which select the fewest rows.
- the plurality of sparse indexes reference the same column within the database table, while in an alternative embodiment the plurality of sparse indexes references a plurality of columns within the database table. In one embodiment, one or more of plurality of sparse indexes are ignored in computing the value of the bitmap, based upon a set of predefined criteria. As an example, one of the set of predefined criteria is the number of rows selected by the sparse index.
- the present invention also provides a program product having a recordable computer readable storage medium.
- Program code is stored on the computer readable storage medium and is configured upon execution to reorganize a database table residing on a computer system according to a plurality of sparse indexes, including the steps of: constructing a bitmap for each row entry within the database table, the bitmap having n bit positions, each of the n bit positions corresponding to the referencing state of one of the plurality of sparse indexes; and sorting the row entries within the database table based upon the value of the bitmap.
- FIG. 1 is a block diagram illustrating a system for reorganizing a database table according to multiple sparse indexes, according to one embodiment of the present invention.
- FIG. 2 (Prior Art) illustrates a set of example query statements for creating sparse indexes from a database table.
- FIG. 4 illustrates a first table, wherein a bitmap is created for each row based upon which of the sparse indexes reference the given row; and a second table created upon sorting the rows of the first table based on the numerical value of the bitmap associated with each row.
- FIG. 5 illustrates the plurality of sparse indexes of FIG. 3 , pointing to the updated second table of FIG. 4 , wherein most of the rows in the table are now adjacent to other rows in each of the sparse indexes.
- a method, system and computer program product are provided for reorganizing a database table according to multiple sparse indexes, wherein the reorganized table has superior I/O performance attributes versus the original table. More specifically, the table is reorganized such that random I/O is minimized by more tightly grouping rows in the table associated with each of the referencing sparse indexes together. This enables more associated rows from a table relevant to a specific sparse index to be read into memory for a given I/O operation.
- FIG. 1 is a block diagram illustrating a networked system 100 for reorganizing a database table according to multiple sparse indexes, according to one embodiment of the present invention.
- the networked system 100 includes a computer 102 .
- the computer 102 may also be connected to other computers via the network 130 .
- the network 130 may be a telecommunications network and/or a wide area network (WAN).
- the network 130 is the Internet.
- the computer 102 generally includes a processor 104 connected via a bus 112 to a memory 106 , a network interface device 110 , a storage 108 , an input device 114 , and an output device 116 .
- the computer 102 is generally under the control of an operating system 148 .
- operating systems include UNIX, versions of the Microsoft Windows® operating system, IBM i (formerly known as i5/OS®) integrated operating environment running on IBM Power SystemsTM, and distributions of the Linux® operating system. More generally, any operating system supporting the functions disclosed herein may be used.
- the processor 104 is included to be representative of a single CPU, multiple CPUs, a single CPU having multiple processing cores, and the like.
- the memory 106 may be a random access memory.
- the memory 106 is shown as a single identity, it should be understood that the memory 106 may comprise a plurality of modules, and that the memory 106 may exist at multiple levels, from high speed registers and caches to lower speed but larger memory capacity DRAM chips.
- the network interface device 110 may be any type of network communications device allowing the computer 102 to communicate with other computers via the network 130 .
- the storage 108 may be a persistent storage device. Although the storage 108 is shown as a single unit, the storage 108 may be a combination of fixed and/or removable storage devices, such as fixed disc drives, floppy disc drives, tape drives, removable memory cards, or optical storage.
- the memory 106 and the storage 108 may be part of one virtual address space spanning multiple primary and secondary storage devices.
- the input device 114 may be any device for providing input to the computer 102 .
- a keyboard, keypad, light pen, touch-screen, track-ball, or speech recognition unit, audio/video player, and the like may be used.
- the output device 116 may be any device for providing output to a user of the computer 102 .
- the output device 116 may be any conventional display screen or set of speakers, along with their respective interface cards, i.e., video cards and sound cards (not shown).
- the output device 116 and input device 114 may be combined.
- a display screen with an integrated touch-screen, a display with an integrated keyboard, or a speech recognition unit combined with a text speech converter may be used.
- the memory 106 of the computer 102 includes the operating system 148 , a Database Management System (DBMS) 150 , and an application 152 .
- the DBMS 150 may manage a database 154 , shown in storage 108 .
- the DBMS 150 is the DB2 product offered by the IBM Corporation for the MVS, AIX, IBM I, Linux or Microsoft Windows operating systems, as examples.
- the present invention has application to any DBMS software that uses SQL, and may similarly be applied to database software using non-SQL queries. More generally, however, it should be understood that the data to be accessed need not be part of a DBMS at all.
- the application 152 (and more generally, any requesting entity, including the operating system 148 ) is configured to issue queries against the database 154 .
- the DBMS 150 may manage multiple databases. Further, the databases may be distributed relative to one another, e.g., over the network 130 . And although the application 152 and the DBMS 150 are shown to be executing on the computer 102 , the application 152 may also execute on a different computer that communicates with the computer 102 via the network 130 .
- the database 154 is representative of any collection of data, regardless of the particular physical representation of the data.
- a physical representation of data defines an organizational schema of the data.
- the database 154 may be organized according to a relational schema (accessible by SQL queries) or according to an XML schema (accessible by XML queries).
- embodiments of the invention are not limited to a particular schema and contemplate extension to schemas presently unknown.
- the term “schema” generically refers to a particular arrangement of data.
- the database 154 stores database tables that include data pages. Each data page is configured to store data rows that, in turn, store information.
- the database may also include a database index such as a sparse index for logically ordering the data rows.
- the database index includes index pages. Each index page is configured to store index entries, where each data row is referenced by a corresponding index entry.
- the data pages and the index pages are arranged to be stored on and retrieved from a storage medium such as the storage 108 .
- the application 152 issues a request to the DBMS 150 .
- the request includes a query statement (e.g., a select, insert, or update).
- the request issued by the application 152 may be predefined (i.e., hard coded as part of the application 152 ) or may be generated in response to input (e.g., user input).
- the DBMS 150 to service the request from the application 152 , performs a number of database operations. For example, the DBMS 150 retrieves index entries and data rows from storage (e.g., disk or tape) into a database cache (e.g., in main memory). The speed of accessing the storage 108 may be much slower than other operations involved in servicing a request, such as operations involving the database cache. Consequently, performance of the DBMS 150 in servicing the request may be, to a large extent, determined by a frequency with which the DBMS 150 accesses the storage 108 .
- storage e.g., disk or tape
- a database cache e.g., in main memory
- the DBMS 150 includes one or more routines 151 to reorganize one or more tables within the database such that random I/O is minimized by more tightly grouping rows in the table associated with each of the referencing sparse indexes together. This enables more associated rows from a table relevant to a specific sparse index to be read into memory for a given I/O operation. Accordingly, the performance of the DBMS 150 in servicing requests may be improved.
- the reorganization (i.e. sort) of the tables within the database is manually initiated by a system administrator. Typically, such an activity would be scheduled to occur at a time when overall system activity is slow such as over a weekend.
- the reorganization may be performed periodically (weekly, monthly, etc) or as needed, as determined by a degree of adjacency metric.
- a DBMS may have an application which measures the degree of adjacency for rows referenced by each sparse matrix.
- the measurement application may recommend to the system administrator to perform a reorganization, or alternatively, automatically reorganize the table via a reorganization application.
- FIG. 2A illustrates a set of example query statements for creating sparse indexes from a database table, shown generally at 200 .
- four SQL CREATE INDEX statements 202 , 204 , 206 and 208 are used to create a set of four sparse indexes 214 , 216 , 218 and 220 using SQL selection predicates on a table 210 .
- a sparse index entitled “IBMJUNE” 214 is created from table “MYTABLE” 210 over column “ORDER#” 211 where field “CUST” in the table contains the value “IBM”, and the field “MONTH” in the table contains the value “June”.
- row 2 of table “MYTABLE” 210 satisfies the search criteria, and the ROW value “2” and the “ORDER#” value “N6RYAK” are written to the “IBMJUNE” sparse index 214 .
- the “ROW” value 2 in the second column of the sparse index 214 serves as a pointer back into the table 210 .
- a sparse index entitled “GEJULY” 216 is created from table “MYTABLE” 210 over column “ORDER#” 211 , where field “CUST” in the table contains the value “GE”, and the field “MONTH” in the table contains the value “July”.
- row 1 of the table “MYTABLE” 210 satisfies the search criteria, and the “ROW” value “1” and the “ORDER#” value “W3IOPL” are written to the “GEJULY” sparse index 216 .
- the “ROW” value 1 in the second column of the sparse index 216 serves as a pointer back into the table 210 .
- a sparse index entitled “LARGEORDER” 218 is created from table “MYTABLE” 210 over column “ORDER#” 211 , wherein any entry in table “MYTABLE” 210 where field “AMOUNTDUE” exceeds $1,000,000 will be selected for inclusion in sparse index “LARGEORDER” 218 .
- rows 1 and 3 of the table “MYTABLE” 210 satisfy the search criterion, and the ROW values “1” and “3” and the “ORDER#” values “W3IOPL” and “8G5HNK”, respectively, are written to the “LARGEORDER” sparse index 218 .
- the “ROW” values 1 and 3 in the second column of the sparse index 218 serve as pointers back into the table 210 .
- a sparse index entitled “MNCUST” 220 is created from table “MYTABLE” 210 over column “CUST#” 212 , wherein any entry in “MYTABLE” 210 that contains the value “MN” in the “STATE” field will be selected for inclusion in the spare index “MNCUST” 220 .
- rows 2 and 4 of the table “MYTABLE” 212 satisfy the search criterion, and the ROW values “2” and “4” and the “CUST#” values “11” and “55”, respectively, are written to the “MNCUST” sparse index 220 .
- the “ROW” values 2 and 4 in the second column of the sparse index 220 serve as pointers back into the table 210 .
- a Select/Omit logical file has keys and criteria which specifies which rows (or records) to either select or omit from the full physical file (i.e. table).
- the specific Select/Omit definition is specified via Data Description Specification (DDS). This select/omit portion is similar in functionality to the WHERE clause in an SQL sparse index.
- Select/Omit logicals are normally used with traditional database interfaces in RPG, COBOL, or CL. Whereas, modern languages tend to use SQL based interfaces to the database. Both interfaces allow similar functionality, but require different methods and syntax to specify the key(s) and selection criteria of the index.
- FIG. 3 illustrates a plurality of sparse indexes 302 , 304 , 306 and 308 pointing to a referenced table 310 , where no sparse index points to rows in the referenced table that are adjacent to each other, shown generally at 300 .
- the rows within COLUMN_X 312 of the table are in order.
- no sparse index 302 , 304 , 306 or 308 has rows which are adjacent to each other.
- sparse INDEX_A 302 references rows 5, 8 and 10 within table 310 .
- Sparse INDEX_B 304 references rows 2, 4 and 6 within table 310 .
- Sparse INDEX_C 306 references rows 1, 3 and 10 within the table 310 .
- sparse INDEX_D 308 references rows 4, 7 and 9 within the table 310 .
- FIG. 4 illustrates a first table 410 , in accordance with the present invention, wherein a bitmap 415 is created for each row entry within the table based upon which of the sparse indexes reference the given row; and a second table 420 created upon sorting the rows of the first table in ascending order based on the numerical value of the bitmap associated with each row, shown generally at 400 .
- First table 410 is essentially table 310 from FIG. 3 , with the addition of a new column 415 containing bitmap information associated with each row of the table.
- Second table 420 illustrates first table 410 , after the table has been re-sorted. Note that the table has been re-sorted in ascending order, based on the decimal value of the bitmap in the first table 410 , column 415 .
- the first row of the first table 410 is pointed to by sparse Index “C” 306 (see FIG. 3 ).
- the bitmap 315 associated with the first row is “0100” (the columns in the bitmap being arranged in DCBA order), having a decimal sort weight value of “4”.
- Row 4 of table 410 is pointed to by two sparse indexes (Index_B 304 and Index_D 308 as shown in FIG. 3 ).
- the “B” and “D” values within the bitmap are assigned a “1” value associated with the reference sparse index. Since the bitmap is arranged in DCBA order, the resultant bitmap value associated with the row is “1010”, or decimal value “10”.
- the leftmost position in the bitmap is associated with the most popular index
- the second leftmost position in the bitmap is associated with the second most popular index
- the most popular index can be determined by monitoring which sparse indexes different database queries use, then keeping counters for each sparse index to determine the popularity.
- the priority of jobs in which database queries run is monitored and recorded, such that sparse indexes used most often in high priority jobs are associated with the leftmost bitmap positions, while sparse indexes most used with lower priority jobs are associated with the rightmost bitmap positions. In this way, the highest average job priority sparse indexes are the most popular indexes, and the lowest average job priority sparse indexes are the least popular indexes.
- bit positions within the bitmap are organized such that the most significant bit positions within the bitmap are assigned to the indexes which select the most rows within the plurality of sparse indexes, and the least significant bit positions within the bitmap are assigned to the indexes which select the fewest rows.
- certain sparse indexes may not be considered (i.e., ignored) when determining the sort weight. As an example, if a certain sparse index selects too may rows within a table, it may be ignored, since it implicitly causes rows to be blocked. Similarly, if a certain sparse index selects too few rows, it may also be ignored since it would not need to use blocking. Additionally, a sparse index may not be frequently be used enough to be considered.
- aspects of the present invention may be embodied as a system, method or computer program product. Accordingly, aspects of the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment (including firmware, resident software, micro-code, etc.) or an embodiment combining software and hardware aspects that may all generally be referred to herein as a “circuit,” “module” or “system.” Furthermore, aspects of the present invention may take the form of a computer program product embodied in one or more computer readable medium(s) having computer readable program code embodied thereon.
- a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.
- a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof.
- a computer readable signal medium may be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.
- Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.
- Computer program code for carrying out operations for aspects of the present invention may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C++ or the like and conventional procedural programming languages, such as the “C” programming language or similar programming languages.
- the program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server.
- the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider).
- LAN local area network
- WAN wide area network
- Internet Service Provider for example, AT&T, MCI, Sprint, EarthLink, MSN, GTE, etc.
- These computer program instructions may also be stored in a computer readable medium that can direct a computer, other programmable data processing apparatus, or other devices to function in a particular manner, such that the instructions stored in the computer readable medium produce an article of manufacture including instructions which implement the function/act specified in the flowchart and/or block diagram block or blocks.
- the computer program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other devices to cause a series of operational steps to be performed on the computer, other programmable apparatus or other devices to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide processes for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks.
- each block in the block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s).
- the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved.
- each block of the block diagrams, and combinations of blocks in the block diagrams can be implemented by special purpose hardware-based systems that perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Description
- 1. Technical Field
- The field of invention relates to databases. In particular, the field of invention relates to reorganizing a table according to multiple sparse indexes.
- 2. Description of the Related Art
- Databases use tables to organize information. Each table consists of a number of rows, each of which corresponds to a single database record. Keys are used to organize the database. Each database table has one or more columns designated as the primary key. The value of this key is unique for each record in the database. For example, assume a table exists that contains personnel information on every employee in an organization. An example of a primary key in this scenario would be an employee serial number unique to each employee.
- A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records. The disk space required to store the index is typically less than the storage of the table (since indexes usually contain only the key-fields according to which the table is to be arranged, and excludes all the other details of the table). Thus, indexes can be stored in memory in instances where the entire table might not fit.
- Database tables are traditionally organized physically on a storage medium (such as a hard disk) according to some index (e.g., a primary key such as employee serial number). In most instances, rows within the database table are processed on a key ordered basis. Thus, physically storing database tables on the disk in keyword order minimizes I/O time, since multiple rows can be brought into memory with each I/O operation.
- Structured Query Language (SQL) sparse indexes are a newer technology that is gaining popularity. A sparse index is an index with a key and pointers to rows in a database table for a subset of data in the table. Every key in the index is associated via pointers with a row in the database table. However, not every row in the table may be pointed to by a key in the index. Application designers have begun to create multiple sparse SQL indexes to tune particular application queries. For many highly tuned applications, hundreds or even thousands of sparse indexes may be created to match many of the application queries.
- Currently, there is no way to physically reorganize a table according to multiple sparse indexes. Thus, as multiple sparse index use becomes more common in some environments, performance may be suboptimal due to high randomness of the I/O operations. For example, if a particular index selects n rows, the n rows may be spread over up to n blocks of data on disk causing up to n IOs when they are read into memory.
- The disclosure and claims herein are directed to a method, system and computer program product for reorganizing a table according to multiple sparse indexes. With the reorganized table, the I/O performance attributes of database queries will be improved, since the reorganized table tends to group rows corresponding to each of the sparse indexes more closely together.
- In one embodiment, a method for reorganizing a database table residing on a computer system according to a plurality of sparse indexes is provided. The method begins by constructing a bitmap for each row entry (i.e., a specific row in a table) within the database table, the bitmap having n bit positions. Each of the n bit positions corresponds to the referencing state of one of the plurality of sparse indexes. The method then sorts the row entries within the database table based upon the value of the bitmap.
- In one embodiment, the step of constructing a bitmap further comprises the steps of: 1) initializing the bitmap to “0”; 2) for each row within the database table, determining if any of the plurality of sparse indexes point to the row; and 3) if any of the plurality of sparse indexes point to the row, setting the bit positions within the bitmap corresponding to the reference sparse indexes associated with the row to “1”). In one embodiment, the bit positions within the bitmap are organized such that the most significant bit positions within the bitmap are assigned to the most popular indexes within the plurality of sparse indexes, and the least significant bit positions within the bitmap are assigned to the least popular indexes. In one embodiment, the popularity of each of the sparse indexes is determined by monitoring query use of the sparse index by at least one counter. In another embodiment, the popularity of each of the sparse indexes is determined by computing and saving the average job priority of queries associated with the sparse index, wherein the highest average job priority sparse indexes are the most popular indexes, and the lowest average job priority indexes are the least popular indexes. In one embodiment, the bit positions within the bitmap are organized such that the most significant bit positions within the bitmap are assigned to the indexes which select the most rows within the plurality of sparse indexes, and the least significant bit positions within the bitmap are assigned to the indexes which select the fewest rows.
- In one embodiment, the plurality of sparse indexes reference the same column within the database table, while in an alternative embodiment the plurality of sparse indexes references a plurality of columns within the database table. In one embodiment, one or more of plurality of sparse indexes are ignored in computing the value of the bitmap, based upon a set of predefined criteria. As an example, one of the set of predefined criteria is the number of rows selected by the sparse index.
- In one embodiment, the present invention also provides a program product having a recordable computer readable storage medium. Program code is stored on the computer readable storage medium and is configured upon execution to reorganize a database table residing on a computer system according to a plurality of sparse indexes, including the steps of: constructing a bitmap for each row entry within the database table, the bitmap having n bit positions, each of the n bit positions corresponding to the referencing state of one of the plurality of sparse indexes; and sorting the row entries within the database table based upon the value of the bitmap.
- In one embodiment, the present invention also provides a system, the system including one of more computer processors, and a memory containing a program, which when executed to the one or more processors is configured to perform an operation. The operation includes: constructing a bitmap for each row entry within the database table, the bitmap having n bit positions, each of the n bit positions corresponding to a referencing state of one of the plurality of sparse indexes; and sorting the row entries within the database table based upon the value of the bitmap.
- The foregoing and other features and advantages will be apparent from the following more particular description, as illustrated in the accompanying drawings.
- So that the manner in which the above recited features, advantages and objects of the present invention are attained and can be understood in detail, a more particular description of the invention, briefly summarized above, may be had by reference to the embodiments thereof which are illustrated in the appended drawings.
- It is to be noted, however, that the appended drawings illustrate only typical embodiments of this invention and are therefore not to be considered limiting of its scope, for the invention may admit to other equally effective embodiments.
-
FIG. 1 is a block diagram illustrating a system for reorganizing a database table according to multiple sparse indexes, according to one embodiment of the present invention. -
FIG. 2 (Prior Art) illustrates a set of example query statements for creating sparse indexes from a database table. -
FIG. 3 (Prior Art) illustrates a plurality of sparse indexes pointing to a referenced table, where no sparse index has rows in the referenced table that are adjacent to each other. -
FIG. 4 illustrates a first table, wherein a bitmap is created for each row based upon which of the sparse indexes reference the given row; and a second table created upon sorting the rows of the first table based on the numerical value of the bitmap associated with each row. -
FIG. 5 illustrates the plurality of sparse indexes ofFIG. 3 , pointing to the updated second table ofFIG. 4 , wherein most of the rows in the table are now adjacent to other rows in each of the sparse indexes. - A method, system and computer program product are provided for reorganizing a database table according to multiple sparse indexes, wherein the reorganized table has superior I/O performance attributes versus the original table. More specifically, the table is reorganized such that random I/O is minimized by more tightly grouping rows in the table associated with each of the referencing sparse indexes together. This enables more associated rows from a table relevant to a specific sparse index to be read into memory for a given I/O operation.
-
FIG. 1 is a block diagram illustrating a networkedsystem 100 for reorganizing a database table according to multiple sparse indexes, according to one embodiment of the present invention. Thenetworked system 100 includes acomputer 102. Thecomputer 102 may also be connected to other computers via thenetwork 130. In general, thenetwork 130 may be a telecommunications network and/or a wide area network (WAN). In a particular embodiment, thenetwork 130 is the Internet. - The
computer 102 generally includes aprocessor 104 connected via abus 112 to amemory 106, anetwork interface device 110, astorage 108, aninput device 114, and anoutput device 116. Thecomputer 102 is generally under the control of anoperating system 148. Examples of operating systems include UNIX, versions of the Microsoft Windows® operating system, IBM i (formerly known as i5/OS®) integrated operating environment running on IBM Power Systems™, and distributions of the Linux® operating system. More generally, any operating system supporting the functions disclosed herein may be used. Theprocessor 104 is included to be representative of a single CPU, multiple CPUs, a single CPU having multiple processing cores, and the like. Similarly, thememory 106 may be a random access memory. While thememory 106 is shown as a single identity, it should be understood that thememory 106 may comprise a plurality of modules, and that thememory 106 may exist at multiple levels, from high speed registers and caches to lower speed but larger memory capacity DRAM chips. Thenetwork interface device 110 may be any type of network communications device allowing thecomputer 102 to communicate with other computers via thenetwork 130. - The
storage 108 may be a persistent storage device. Although thestorage 108 is shown as a single unit, thestorage 108 may be a combination of fixed and/or removable storage devices, such as fixed disc drives, floppy disc drives, tape drives, removable memory cards, or optical storage. Thememory 106 and thestorage 108 may be part of one virtual address space spanning multiple primary and secondary storage devices. - The
input device 114 may be any device for providing input to thecomputer 102. For example, a keyboard, keypad, light pen, touch-screen, track-ball, or speech recognition unit, audio/video player, and the like may be used. Theoutput device 116 may be any device for providing output to a user of thecomputer 102. For example, theoutput device 116 may be any conventional display screen or set of speakers, along with their respective interface cards, i.e., video cards and sound cards (not shown). Although shown separately from theinput device 114, theoutput device 116 andinput device 114 may be combined. For example, a display screen with an integrated touch-screen, a display with an integrated keyboard, or a speech recognition unit combined with a text speech converter may be used. - As shown, the
memory 106 of thecomputer 102 includes theoperating system 148, a Database Management System (DBMS) 150, and anapplication 152. TheDBMS 150 may manage adatabase 154, shown instorage 108. In one embodiment, theDBMS 150 is the DB2 product offered by the IBM Corporation for the MVS, AIX, IBM I, Linux or Microsoft Windows operating systems, as examples. Generally, those skilled in the art will recognize that the present invention has application to any DBMS software that uses SQL, and may similarly be applied to database software using non-SQL queries. More generally, however, it should be understood that the data to be accessed need not be part of a DBMS at all. The application 152 (and more generally, any requesting entity, including the operating system 148) is configured to issue queries against thedatabase 154. Although only one database is shown for simplicity, theDBMS 150 may manage multiple databases. Further, the databases may be distributed relative to one another, e.g., over thenetwork 130. And although theapplication 152 and theDBMS 150 are shown to be executing on thecomputer 102, theapplication 152 may also execute on a different computer that communicates with thecomputer 102 via thenetwork 130. - In one embodiment, the
database 154 is representative of any collection of data, regardless of the particular physical representation of the data. A physical representation of data defines an organizational schema of the data. By way of illustration, thedatabase 154 may be organized according to a relational schema (accessible by SQL queries) or according to an XML schema (accessible by XML queries). However, embodiments of the invention are not limited to a particular schema and contemplate extension to schemas presently unknown. As used herein, the term “schema” generically refers to a particular arrangement of data. - In one embodiment, the
database 154 stores database tables that include data pages. Each data page is configured to store data rows that, in turn, store information. The database may also include a database index such as a sparse index for logically ordering the data rows. The database index includes index pages. Each index page is configured to store index entries, where each data row is referenced by a corresponding index entry. The data pages and the index pages are arranged to be stored on and retrieved from a storage medium such as thestorage 108. - In one embodiment, the
application 152 issues a request to theDBMS 150. The request includes a query statement (e.g., a select, insert, or update). Depending on the embodiment, the request issued by theapplication 152 may be predefined (i.e., hard coded as part of the application 152) or may be generated in response to input (e.g., user input). - In one embodiment, to service the request from the
application 152, theDBMS 150 performs a number of database operations. For example, theDBMS 150 retrieves index entries and data rows from storage (e.g., disk or tape) into a database cache (e.g., in main memory). The speed of accessing thestorage 108 may be much slower than other operations involved in servicing a request, such as operations involving the database cache. Consequently, performance of theDBMS 150 in servicing the request may be, to a large extent, determined by a frequency with which theDBMS 150 accesses thestorage 108. - In one embodiment, the
DBMS 150 includes one ormore routines 151 to reorganize one or more tables within the database such that random I/O is minimized by more tightly grouping rows in the table associated with each of the referencing sparse indexes together. This enables more associated rows from a table relevant to a specific sparse index to be read into memory for a given I/O operation. Accordingly, the performance of theDBMS 150 in servicing requests may be improved. - In one embodiment, the reorganization (i.e. sort) of the tables within the database is manually initiated by a system administrator. Typically, such an activity would be scheduled to occur at a time when overall system activity is slow such as over a weekend. In another embodiment, the reorganization may be performed periodically (weekly, monthly, etc) or as needed, as determined by a degree of adjacency metric. For example, a DBMS may have an application which measures the degree of adjacency for rows referenced by each sparse matrix. If, for example, the measurement application is run periodically and shows the rows are not very adjacent (i.e., fragmented) for each sparse index (i.e., the degree of adjacency metric exceeds a predefined threshold value), the measurement application may recommend to the system administrator to perform a reorganization, or alternatively, automatically reorganize the table via a reorganization application.
-
FIG. 2A (Prior Art) illustrates a set of example query statements for creating sparse indexes from a database table, shown generally at 200. In the illustrated embodiment, four SQLCREATE INDEX statements sparse indexes - In the first example SQL
CREATE INDEX statement 202, a sparse index entitled “IBMJUNE” 214 is created from table “MYTABLE” 210 over column “ORDER#” 211 where field “CUST” in the table contains the value “IBM”, and the field “MONTH” in the table contains the value “June”. As can be seen,row 2 of table “MYTABLE” 210 satisfies the search criteria, and the ROW value “2” and the “ORDER#” value “N6RYAK” are written to the “IBMJUNE”sparse index 214. The “ROW”value 2 in the second column of thesparse index 214 serves as a pointer back into the table 210. - In the second example SQL
CREATE INDEX statement 204, a sparse index entitled “GEJULY” 216 is created from table “MYTABLE” 210 over column “ORDER#” 211, where field “CUST” in the table contains the value “GE”, and the field “MONTH” in the table contains the value “July”. In this instance,row 1 of the table “MYTABLE” 210 satisfies the search criteria, and the “ROW” value “1” and the “ORDER#” value “W3IOPL” are written to the “GEJULY”sparse index 216. The “ROW”value 1 in the second column of thesparse index 216 serves as a pointer back into the table 210. - In the third example SQL
CREATE INDEX statement 206, a sparse index entitled “LARGEORDER” 218 is created from table “MYTABLE” 210 over column “ORDER#” 211, wherein any entry in table “MYTABLE” 210 where field “AMOUNTDUE” exceeds $1,000,000 will be selected for inclusion in sparse index “LARGEORDER” 218. In this instance,rows sparse index 218. The “ROW” values 1 and 3 in the second column of thesparse index 218 serve as pointers back into the table 210. - In the fourth example SQL
CREATE INDEX statement 208, a sparse index entitled “MNCUST” 220 is created from table “MYTABLE” 210 over column “CUST#” 212, wherein any entry in “MYTABLE” 210 that contains the value “MN” in the “STATE” field will be selected for inclusion in the spare index “MNCUST” 220. In this instance,rows sparse index 220. The “ROW” values 2 and 4 in the second column of thesparse index 220 serve as pointers back into the table 210. - Another type of sparse index in addition to SQL is a Select/Omit logical file. A Select/Omit logical has keys and criteria which specifies which rows (or records) to either select or omit from the full physical file (i.e. table). The specific Select/Omit definition is specified via Data Description Specification (DDS). This select/omit portion is similar in functionality to the WHERE clause in an SQL sparse index.
- Select/Omit logicals are normally used with traditional database interfaces in RPG, COBOL, or CL. Whereas, modern languages tend to use SQL based interfaces to the database. Both interfaces allow similar functionality, but require different methods and syntax to specify the key(s) and selection criteria of the index.
- Additional types of sparse indexes may be utilized within the present invention, in addition to the SQL and select/omit logical files, and still remain within the scope and spirit of the present invention.
-
FIG. 3 (Prior Art) illustrates a plurality ofsparse indexes COLUMN_X 312 of the table are in order. Despite this fact, nosparse index sparse INDEX_A 302references rows Sparse INDEX_B 304references rows Sparse INDEX_C 306references rows sparse INDEX_D 308references rows - As a result, when applications build SQL sparse indexes (such as 302, 304, 306 and 308) to match certain queries, the non-adjacency of the referenced rows within table 310 will tend to cause I/O operations to be random rather than blocked, thus producing suboptimal performance.
-
FIG. 4 illustrates a first table 410, in accordance with the present invention, wherein abitmap 415 is created for each row entry within the table based upon which of the sparse indexes reference the given row; and a second table 420 created upon sorting the rows of the first table in ascending order based on the numerical value of the bitmap associated with each row, shown generally at 400. First table 410 is essentially table 310 fromFIG. 3 , with the addition of anew column 415 containing bitmap information associated with each row of the table. - As illustrated earlier in
FIG. 3 , each row within the table may be associated with at least one sparse index within the table 310. InFIG. 4 , incolumn 415, a bitmap entry is created for each row, wherein the i-bit position within the bitmap entry represents the i-th sparse index. Thus if a given row is referenced by a sparse index, a “1” appears in the bit associated with that sparse index in the bitmap entry, otherwise the bit entry contains a “0” (default) value assigned at initialization. - After the entire bitmap entry is constructed, a decimal representation is then computed for each bitmap entry. This decimal representation becomes the sort weight for each row within the table. Thus, a bitmap value of “0100” would be assigned a decimal value of “4”, and a bitmap value of “1010” would be assigned a decimal value of “10”. This value is used to re-sort the rows of the table. Second table 420 illustrates first table 410, after the table has been re-sorted. Note that the table has been re-sorted in ascending order, based on the decimal value of the bitmap in the first table 410,
column 415. - By way of example, the first row of the first table 410 is pointed to by sparse Index “C” 306 (see
FIG. 3 ). As a result, the bitmap 315 associated with the first row is “0100” (the columns in the bitmap being arranged in DCBA order), having a decimal sort weight value of “4”.Row 4 of table 410 is pointed to by two sparse indexes (Index_B 304 andIndex_D 308 as shown inFIG. 3 ). As a result, the “B” and “D” values within the bitmap are assigned a “1” value associated with the reference sparse index. Since the bitmap is arranged in DCBA order, the resultant bitmap value associated with the row is “1010”, or decimal value “10”. - Note that if a row is not selected by any index, it would have a sort weight of zero and be blocked with other rows that are not selected by any index. Within a particular sort weight, rows can, by way of example, be sorted by the actual value in the column.
- Within the examples provided, all sparse indexes point to the same column within the database table. In other embodiments of the present invention, the sparse indexes may point to a different column, or plurality of columns within the database table, and still remain within the scope and spirit of the present invention.
- In one embodiment, when determining sort order for the database table, the leftmost position in the bitmap is associated with the most popular index, the second leftmost position in the bitmap is associated with the second most popular index, etc. In this way, the most popular indexes will be grouped more tightly together, and, in fact, the leftmost bit position is guaranteed to have all of its rows together.
- There are several alternatives available to determine the most popular sparse indexes. For example, the most popular index can be determined by monitoring which sparse indexes different database queries use, then keeping counters for each sparse index to determine the popularity. In another example, the priority of jobs in which database queries run is monitored and recorded, such that sparse indexes used most often in high priority jobs are associated with the leftmost bitmap positions, while sparse indexes most used with lower priority jobs are associated with the rightmost bitmap positions. In this way, the highest average job priority sparse indexes are the most popular indexes, and the lowest average job priority sparse indexes are the least popular indexes. In one embodiment, the bit positions within the bitmap are organized such that the most significant bit positions within the bitmap are assigned to the indexes which select the most rows within the plurality of sparse indexes, and the least significant bit positions within the bitmap are assigned to the indexes which select the fewest rows.
- In one embodiment of the present invention, certain sparse indexes may not be considered (i.e., ignored) when determining the sort weight. As an example, if a certain sparse index selects too may rows within a table, it may be ignored, since it implicitly causes rows to be blocked. Similarly, if a certain sparse index selects too few rows, it may also be ignored since it would not need to use blocking. Additionally, a sparse index may not be frequently be used enough to be considered.
-
FIG. 5 illustrates the plurality of sparse indexes ofFIG. 3 , pointing to the updated, sorted second table 420 ofFIG. 4 , wherein most of the rows in the table are now adjacent to other rows in each of the sparse indexes. In this illustrative example, the first twosparse indexes sparse indexes - In the preceding paragraphs, reference is made to embodiments of the invention. However, it should be understood that the invention is not limited to specific described embodiments. Instead, any combination of the following features and elements, whether related to different embodiments or not, is contemplated to implement and practice the invention. Furthermore, although embodiments of the invention may achieve advantages over other possible solutions and/or over the prior art, whether or not a particular advantage is achieved by a given embodiment is not limiting of the invention. Thus, the following aspects, features, embodiments and advantages are merely illustrative and are not considered elements or limitations of the appended claims except where explicitly recited in a claim(s). Likewise, reference to “the invention” shall not be construed as a generalization of any inventive subject matter disclosed herein and shall not be considered to be an element or limitation of the appended claims except where explicitly recited in a claim(s).
- As will be appreciated by one skilled in the art, aspects of the present invention may be embodied as a system, method or computer program product. Accordingly, aspects of the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment (including firmware, resident software, micro-code, etc.) or an embodiment combining software and hardware aspects that may all generally be referred to herein as a “circuit,” “module” or “system.” Furthermore, aspects of the present invention may take the form of a computer program product embodied in one or more computer readable medium(s) having computer readable program code embodied thereon.
- Any combination of one or more computer readable medium(s) may be utilized. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. More specific examples (a non-exhaustive list) of the computer readable storage medium would include the following: a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.
- A computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.
- Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.
- Computer program code for carrying out operations for aspects of the present invention may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C++ or the like and conventional procedural programming languages, such as the “C” programming language or similar programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider).
- Aspects of the present invention are described above with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems) and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and/or block diagrams, and combinations of blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks.
- These computer program instructions may also be stored in a computer readable medium that can direct a computer, other programmable data processing apparatus, or other devices to function in a particular manner, such that the instructions stored in the computer readable medium produce an article of manufacture including instructions which implement the function/act specified in the flowchart and/or block diagram block or blocks.
- The computer program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other devices to cause a series of operational steps to be performed on the computer, other programmable apparatus or other devices to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide processes for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks.
- The block diagrams in the Figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams, and combinations of blocks in the block diagrams, can be implemented by special purpose hardware-based systems that perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
- While the foregoing is directed to embodiments of the present invention, other and further embodiments of the invention may be devised without departing from the basic scope thereof, and the scope thereof is determined by the claims that follow.
Claims (30)
Priority Applications (5)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US13/005,024 US8566333B2 (en) | 2011-01-12 | 2011-01-12 | Multiple sparse index intelligent table organization |
CN201280005241.4A CN103314377B (en) | 2011-01-12 | 2012-01-06 | sparse index table organization |
GB1309991.6A GB2499551A (en) | 2011-01-12 | 2012-01-06 | Sparse index table organization |
PCT/IB2012/050077 WO2012095771A1 (en) | 2011-01-12 | 2012-01-06 | Sparse index table organization |
DE112012000280.6T DE112012000280B4 (en) | 2011-01-12 | 2012-01-06 | Organization of tables with reduced indexes |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US13/005,024 US8566333B2 (en) | 2011-01-12 | 2011-01-12 | Multiple sparse index intelligent table organization |
Publications (2)
Publication Number | Publication Date |
---|---|
US20120179698A1 true US20120179698A1 (en) | 2012-07-12 |
US8566333B2 US8566333B2 (en) | 2013-10-22 |
Family
ID=46456059
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US13/005,024 Active 2031-12-31 US8566333B2 (en) | 2011-01-12 | 2011-01-12 | Multiple sparse index intelligent table organization |
Country Status (5)
Country | Link |
---|---|
US (1) | US8566333B2 (en) |
CN (1) | CN103314377B (en) |
DE (1) | DE112012000280B4 (en) |
GB (1) | GB2499551A (en) |
WO (1) | WO2012095771A1 (en) |
Cited By (11)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN104572828A (en) * | 2014-12-08 | 2015-04-29 | 中国科学院信息工程研究所 | Auxiliary indexing method and auxiliary indexing system based on space bitmap model |
US9311124B2 (en) | 2013-11-07 | 2016-04-12 | Sap Se | Integrated deployment of centrally modified software systems |
US9390115B2 (en) * | 2013-10-11 | 2016-07-12 | Oracle International Corporation | Tables with unlimited number of sparse columns and techniques for an efficient implementation |
EP3103039A4 (en) * | 2014-02-20 | 2017-02-15 | Huawei Technologies Co., Ltd. | System and method for an efficient database storage model based on sparse files |
US10467207B2 (en) | 2013-05-24 | 2019-11-05 | Sap Se | Handling changes in automatic sort |
CN110619183A (en) * | 2019-09-24 | 2019-12-27 | 中国航空工业集团公司沈阳飞机设计研究所 | Full-life-cycle test flight test data management method |
US10642817B2 (en) | 2015-06-03 | 2020-05-05 | Huawei Technologies Co., Ltd. | Index table update method, and device |
US11256746B2 (en) | 2016-04-25 | 2022-02-22 | Oracle International Corporation | Hash-based efficient secondary indexing for graph data stored in non-relational data stores |
US11425136B2 (en) * | 2016-07-28 | 2022-08-23 | Molecula Corp. | Systems and methods of managing data rights and selective data sharing |
CN115809248A (en) * | 2022-12-30 | 2023-03-17 | 联通智网科技股份有限公司 | Data query method and device and storage medium |
CN116991901A (en) * | 2023-09-25 | 2023-11-03 | 深圳市秦丝科技有限公司 | Data control system and method based on multidimensional database query |
Families Citing this family (5)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US9411838B2 (en) | 2014-02-14 | 2016-08-09 | International Business Machines Corporation | Table organization using one or more queries |
ES2930440T3 (en) * | 2016-02-05 | 2022-12-13 | Amadeus Sas | Database table index |
CN109656948B (en) * | 2018-11-15 | 2021-01-22 | 金蝶软件(中国)有限公司 | Bitmap data processing method and device, computer equipment and storage medium |
US11294905B2 (en) * | 2019-01-07 | 2022-04-05 | Optumsoft, Inc. | Sparse data index table |
US11321392B2 (en) | 2019-02-19 | 2022-05-03 | International Business Machines Corporation | Light weight index for querying low-frequency data in a big data environment |
Citations (4)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5758145A (en) * | 1995-02-24 | 1998-05-26 | International Business Machines Corporation | Method and apparatus for generating dynamic and hybrid sparse indices for workfiles used in SQL queries |
US5884307A (en) * | 1997-02-28 | 1999-03-16 | Oracle Corporation | Updating bitmapped indexes |
US6122626A (en) * | 1997-06-16 | 2000-09-19 | U.S. Philips Corporation | Sparse index search method |
US6879976B1 (en) * | 1999-08-19 | 2005-04-12 | Azi, Inc. | Data indexing using bit vectors |
Family Cites Families (9)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US6546395B1 (en) | 1999-08-30 | 2003-04-08 | International Business Machines Corporation | Multi-dimensional restructure performance by selecting a technique to modify a relational database based on a type of restructure |
US6629102B1 (en) | 2000-07-28 | 2003-09-30 | International Business Machines Corporation | Efficiently updating a key table during outline restructure of a multi-dimensional database |
US6829621B2 (en) * | 2000-10-06 | 2004-12-07 | International Business Machines Corporation | Automatic determination of OLAP cube dimensions |
US7191174B2 (en) | 2003-05-22 | 2007-03-13 | International Business Machines Corporation | Method, query optimizer, and computer program product for implementing live switchover to temporary sparse index for faster query performance |
US7979404B2 (en) | 2004-09-17 | 2011-07-12 | Quest Software, Inc. | Extracting data changes and storing data history to allow for instantaneous access to and reconstruction of any point-in-time data |
EP1732013A1 (en) | 2005-06-06 | 2006-12-13 | Deutsche Thomson-Brandt Gmbh | Method and device for searching a data unit in a database |
US20100036799A1 (en) | 2008-08-05 | 2010-02-11 | Teradata Us, Inc. | Query processing using horizontal partial covering join index |
CN101520868A (en) | 2009-02-24 | 2009-09-02 | 上海大学 | Method for applying analytic hierarchy process to reviewer information database system |
CN101702162A (en) | 2009-11-19 | 2010-05-05 | 福建鑫诺通讯技术有限公司 | Data management module of embedded system |
-
2011
- 2011-01-12 US US13/005,024 patent/US8566333B2/en active Active
-
2012
- 2012-01-06 GB GB1309991.6A patent/GB2499551A/en not_active Withdrawn
- 2012-01-06 DE DE112012000280.6T patent/DE112012000280B4/en active Active
- 2012-01-06 WO PCT/IB2012/050077 patent/WO2012095771A1/en active Application Filing
- 2012-01-06 CN CN201280005241.4A patent/CN103314377B/en active Active
Patent Citations (4)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5758145A (en) * | 1995-02-24 | 1998-05-26 | International Business Machines Corporation | Method and apparatus for generating dynamic and hybrid sparse indices for workfiles used in SQL queries |
US5884307A (en) * | 1997-02-28 | 1999-03-16 | Oracle Corporation | Updating bitmapped indexes |
US6122626A (en) * | 1997-06-16 | 2000-09-19 | U.S. Philips Corporation | Sparse index search method |
US6879976B1 (en) * | 1999-08-19 | 2005-04-12 | Azi, Inc. | Data indexing using bit vectors |
Cited By (11)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US10467207B2 (en) | 2013-05-24 | 2019-11-05 | Sap Se | Handling changes in automatic sort |
US9390115B2 (en) * | 2013-10-11 | 2016-07-12 | Oracle International Corporation | Tables with unlimited number of sparse columns and techniques for an efficient implementation |
US9311124B2 (en) | 2013-11-07 | 2016-04-12 | Sap Se | Integrated deployment of centrally modified software systems |
EP3103039A4 (en) * | 2014-02-20 | 2017-02-15 | Huawei Technologies Co., Ltd. | System and method for an efficient database storage model based on sparse files |
CN104572828A (en) * | 2014-12-08 | 2015-04-29 | 中国科学院信息工程研究所 | Auxiliary indexing method and auxiliary indexing system based on space bitmap model |
US10642817B2 (en) | 2015-06-03 | 2020-05-05 | Huawei Technologies Co., Ltd. | Index table update method, and device |
US11256746B2 (en) | 2016-04-25 | 2022-02-22 | Oracle International Corporation | Hash-based efficient secondary indexing for graph data stored in non-relational data stores |
US11425136B2 (en) * | 2016-07-28 | 2022-08-23 | Molecula Corp. | Systems and methods of managing data rights and selective data sharing |
CN110619183A (en) * | 2019-09-24 | 2019-12-27 | 中国航空工业集团公司沈阳飞机设计研究所 | Full-life-cycle test flight test data management method |
CN115809248A (en) * | 2022-12-30 | 2023-03-17 | 联通智网科技股份有限公司 | Data query method and device and storage medium |
CN116991901A (en) * | 2023-09-25 | 2023-11-03 | 深圳市秦丝科技有限公司 | Data control system and method based on multidimensional database query |
Also Published As
Publication number | Publication date |
---|---|
CN103314377A (en) | 2013-09-18 |
DE112012000280B4 (en) | 2020-03-05 |
GB201309991D0 (en) | 2013-07-17 |
US8566333B2 (en) | 2013-10-22 |
WO2012095771A1 (en) | 2012-07-19 |
DE112012000280T5 (en) | 2013-09-19 |
CN103314377B (en) | 2017-02-08 |
GB2499551A (en) | 2013-08-21 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US8566333B2 (en) | Multiple sparse index intelligent table organization | |
US9043310B2 (en) | Accessing a dimensional data model when processing a query | |
US9514187B2 (en) | Techniques for using zone map information for post index access pruning | |
EP2901323B1 (en) | Policy driven data placement and information lifecycle management | |
US9805077B2 (en) | Method and system for optimizing data access in a database using multi-class objects | |
US10387411B2 (en) | Determining a density of a key value referenced in a database query over a range of rows | |
US7831620B2 (en) | Managing execution of a query against a partitioned database | |
EP3014488B1 (en) | Incremental maintenance of range-partitioned statistics for query optimization | |
US8380703B2 (en) | Feedback loop between a query optimizer and a cache manager | |
US20040148293A1 (en) | Method, system, and program for managing database operations with respect to a database table | |
US8140517B2 (en) | Database query optimization using weight mapping to qualify an index | |
US8386445B2 (en) | Reorganizing database tables | |
US20120310921A1 (en) | Identifying impact of installing a database patch | |
US7792819B2 (en) | Priority reduction for fast partitions during query execution | |
US9734177B2 (en) | Index merge ordering | |
KR20150098660A (en) | Maintenance of active database queries | |
US10394790B2 (en) | Table organization using one or more queries | |
EP3696688B1 (en) | Locking based on categorical memory allocation | |
Harrison | Oracle Performance Survival Guide: A Systematic Approach to Database Optimization | |
CN111367920A (en) | Two-dimensional table-based storage method, index construction method and storage device | |
Hamdoon et al. | Pragmatic approach to query optimization |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
AS | Assignment |
Owner name: INTERNATIONAL BUSINESS MACHINES CORPORATION, NEW Y Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNOR:MURAS, BRIAN R.;REEL/FRAME:025624/0521 Effective date: 20110111 |
|
STCF | Information on status: patent grant |
Free format text: PATENTED CASE |
|
FPAY | Fee payment |
Year of fee payment: 4 |
|
AS | Assignment |
Owner name: ALIBABA GROUP HOLDING LIMITED, HONG KONG Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNOR:INTERNATIONAL BUSINESS MACHINES CORPORATION;REEL/FRAME:053970/0339 Effective date: 20201002 |
|
AS | Assignment |
Owner name: ALIBABA GROUP HOLDING LIMITED, CAYMAN ISLANDS Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNOR:INTERNATIONAL BUSINESS MACHINES CORPORATION;REEL/FRAME:055339/0415 Effective date: 20201021 |
|
MAFP | Maintenance fee payment |
Free format text: PAYMENT OF MAINTENANCE FEE, 8TH YEAR, LARGE ENTITY (ORIGINAL EVENT CODE: M1552); ENTITY STATUS OF PATENT OWNER: LARGE ENTITY Year of fee payment: 8 |