Patent Application
20180373759
Illustrated embodiments generally relate to data processing, and more particularly to dynamic diagonal search in databases.
Typically, search engines follow horizontal search in row store databases, or vertical search in column store databases. In the row store databases, complete table scanning of a table takes place row-by-row, and in the column store databases complete table scan takes place column-by-column. In a database table, search is optimized if a primary key is generated in the database table. Further, an optimized search is achieved if the search is performed on the field associated with the primary key. Also, search is optimized if index is generated in the database table. The optimized search is achieved if the search is performed on the indexed field in the database table. It is challenging to identify a search optimization technique that can be used on a database table that neither has a primary key defined nor an index defined. Further, it is challenging to identify a search optimization technique that will be compatible with both row store databases and column store databases.
The claims set forth the embodiments with particularity. The embodiments are illustrated by way of examples and not by way of limitation in the figures of the accompanying drawings in which like references indicate similar elements. Various embodiments, together with their advantages, may be best understood from the following detailed description taken in conjunction with the accompanying drawings.
Embodiments of techniques for dynamic diagonal search in databases are described herein. In the following description, numerous specific details are set forth to provide a thorough understanding of the embodiments. A person of ordinary skill in the relevant art will recognize, however, that the embodiments can be practiced without one or more of the specific details, or with other methods, components, materials, etc. In some instances, well-known structures, materials, or operations are not shown or described in detail.
Reference throughout this specification to “one embodiment”, “this embodiment” and similar phrases, means that a particular feature, structure, or characteristic described in connection with the embodiment is included in at least one of the one or more embodiments. Thus, the appearances of these phrases in various places throughout this specification are not necessarily all referring to the same embodiment. Furthermore, the particular features, structures, or characteristics may be combined in any suitable manner in one or more embodiments.
When a row search is performed on the table 100, values stored in the table 100 are searched or scanned by individual rows in a sequential manner. For example, a query is received as shown below:
In response to the received query, when a row search or scan is performed, row 110 is searched or scanned first such as ‘India’ 112 (X0001), ‘100’ (X0002), ‘5’ (X0003) and ‘Delhi’ 118 (X0004), followed by scanning of the row 120, row 130 and row 140 in a sequential manner. While performing the sequential row scan, a data pointer pointing to a memory address of a value is sequentially incremented by ‘1’. In the sequential row scan of table 100, to scan from the first value ‘India’ 112 (X0001) to the last value ‘Beijing’ 148 (X0016), the data pointer is sequentially incremented by ‘1’ to move from the first memory location ‘X0001’ to the last memory location ‘X0016’. This results in ‘15’ sequential movements of the data pointer to complete the scan from the first memory location ‘X0001’ to the last memory location ‘X0016’. The row search or scan did not result in successful search result.
In response to the received query, when a column search or scan is performed, column country 102 is searched or scanned such as ‘India’ 112 (X0001), ‘Japan’ (X0005), Nepal (X0009), and China (X0013). Second column population 104, third column sales growth % 106 and fourth column area of max growth 108 are scanned in parallel with the first column country 102. While performing the parallel column scan, a data pointer pointing to a memory address of a. value is incremented by ‘1’. In the column scan of table 100, to scan from the first value ‘India’ 112 (X0001) until the last value ‘China’ 142 (X0013) in the column country 102, the data pointer is incremented by ‘1’ for every movement from the memory location X0001 to X0013. Similarly, the data pointer is incremented by ‘1’ in the remaining columns. This results in ‘12’ movements of the data pointer to complete the scan from the first memory location ‘X0001’ to the last memory location ‘X0016’. The column search or scan does not result in successful search result.
In one embodiment, in response to the received query, a diagonal search or diagonal scan is performed, such as ‘India’ 112 (X0001), ‘200’ (X0006), ‘20’ (X00011) and ‘Beijing’ (X0016). While performing the diagonal search or scan, a data pointer pointing to a. memory address of a value is incremented by ‘5’. For diagonal search, increment of the data pointer is dynamically determined based on the size of the table or the number of columns in the table. For example, data pointer increment is ‘5’ because the number of the columns in table 100 is ‘4’. Initially memory address ‘X0001’ is scanned and the data. pointer is incremented by ‘5’ to scan the next memory address ‘X0006’. Similarly, memory locations ‘X00011’ and ‘X0016’ are scanned by incrementing the data pointer by ‘5’. Scan from memory address ‘X0001’ to ‘X0006’ is considered as ‘1’ movement or ‘1’ jump by the data pointer. By incrementing the data pointer by ‘5’, diagonal search of table 100 requires ‘3’ movements of the data pointer from the memory address ‘X0001’ to ‘X0016’. The diagonal search or scan did not result in successful search result.
Diagonal search of the table 100 takes ‘3’ movement of the data pointer to complete the scan of the entire table in comparison to the row scan that took ‘15’ movement or column scan that took ‘12’ movement of the data pointer to complete the scan of the entire table. In the example illustrated above, diagonal scan of the table takes ‘5’ times lesser time than row or column scan, resulting in faster table scan. Further, though the table 100 neither has a primary key defined nor index defined, the diagonal scan of the table results in faster scan in comparison to row or column scan. The diagonal search may be applied on the table with different number of columns and rows. The number of columns or rows does not restrict the diagonal search in any way.
SELECT*FROM “RV_TB” WHERE COUNTRY=‘JAPAN’ (QID2)
is received in the database, information corresponding to the search query is stored in the dynamic table 200. Consider the above query ‘QID2’ received in the table 100 ‘RV_TB’ (FIG.1). The search query is identified by a query ID such as ‘Q1D2’, and the table is identified by a table ID such as ‘RV_TB’. Query ‘QID2’ 214 and table ID ‘RV_TB’ 216 are stored in the dynamic table 200. Query ID may be automatically generated based on the table queried and the conditions in the ‘where’ clause. When search query ‘Q1D2’ is received in the database for the first time, the search query is executed in the database using default search logic, and based on the execution, additional information is generated and stored in the dynamic table 200. An execution plan is an ordered set of steps to access data in a database. For example, the execution plan provides information such as how data is accessed, types of joins used, type of filtering or sorting used, primary keys and foreign keys accessed, estimated time for individual operation, etc. The database is packaged with a set of default execution plans to access or query the database. The default search logic is provided by the default execution plans. A diagonal search execution plan may be provided as a plugin or add-on to the default execution plans. Diagonal search logic is provided by the diagonal search execution plan. The default search execution plan may be available in a search engine and the diagonal search execution plan may be provided as a plugin or add-on in the search engine. The query ‘QID2’ is executed using the default search logic and the result of the execution is recorded in the dynamic table 200. When the search query QID2 is executed for the first time, value ‘no’ is stored in field 220 of the table modified 208 column. The table is deemed modified if there are records inserted, deleted, updated or defragmented. The search query ‘QID2’ is executed and the ‘where’ clause country=‘JAPAN’ is identified in the memory address ‘X0005’, and a successful search result is obtained. The memory address ‘X0005’ 218 is stored in the dynamic table 200 in the column memory address 206. The memory address ‘X0005’ is the address where the conditional clause is met and the search result is identified. Subsequently, when the search query QID2 is received in the database, a determination is made to validate if the table ‘RV_TB’ was modified since its table identifier was last stored in the dynamic table 200. If the determination is made that the table ‘RV_TB’ was modified since its table identifier was last stored in the dynamic table 200, a value ‘yes’ is stored in field 220 corresponding to table modified column 208. The result of executing the search query QID2214 is recorded in the field 222 of column search successful 210. If the search is successful for the search query QID2, a value of ‘yes’ is stored in field 222 corresponding to the column search successful 210. A count of the successful search query is also maintained in the column count 212. For example, since the search query QID2214 is successful, count of the successful search query QID2214 is stored as ‘1’ in field 224.
Whenever a subsequent search query is received in the database, the query ID of the received search query is matched with the values stored in the query ID 202. If a match is found, a validation is performed to determine whether the table ‘RV_TB’ 216 is modified. If the column table modified 208 indicates ‘no’ in the field 220, and if the column search successful 210 field indicates ‘yes’ in the field 222, it is determined whether the diagonal search execution plan is not available for execution of the query ID QID2. Upon determining that the diagonal search execution plan is available, the received search query can be executed from the memory address ‘X005’ 218 stored in the dynamic table 200. The dynamic table 200 can be used as a quick reference for any search query to determine if a previously successful search was performed for a specific query ID. If a successful search is performed for the query ID, then a dynamic diagonal search can be performed from the memory address stored in the column memory address 206 instead of a starting memory address of the table. Validating table modification and determining if the search is successful is collectively referred to as dynamic search criteria.
A unique query ID is assigned based on the table and conditional clauses in the query. For example, a query ID such as QID2 is assigned since the table accessed is ‘RV_TB’ and the conditional clause is country=‘Japan’. The memory address ‘X0005’ 218 is stored in the field corresponding to the query ID QID2 . When another query as shown below is received:
In the above query, table queried is ‘RV_TB’ and the ‘where’ conditional clause is COUNTRY=‘JAPAN’. This is same or similar to the table queried and the ‘where’ conditional clause in query QID2. The above query is analyzed to determine if there are additional conditional clauses. Accordingly, it is determined that an additional conditional clause POPULATION=‘150’ is available with an ‘AND’ clause. Since the above query is same or similar to QID2 with the additional conditional ‘AND’ clause, the above query may be treated as a sub-query of the query QID2. Sub-query may share the same query of parent, therefore, the above sub-query may share the same query ID of parent QID2, and hence is assigned the same query ID. For example, when the above query is executed, a diagonal search may be performed from the memory address ‘X0005’ 218, since the first conditional clause COUNTRY=‘JAPAN’ remains the same. The additional condition clause POPULATION=‘300’ further narrows this search, and hence searching from the memory address ‘X0005’ holds good resulting in a desired search with ‘no records found’ result.
When, a query as shown below is received:
In the above query, table queried is ‘RV_TB’ and the ‘where’ conditional clause is COUNTRY=‘JAPAN’. This is same or similar to the table queried and the ‘where’ conditional clause in query QID2. The above query is analyzed to determine if there are additional conditional clauses. Accordingly, it is determined that an additional conditional clause POPULATION=‘150’ is available with an ‘OR’ clause. Since the above query is same or similar to QID2 with the additional conditional ‘OR’ clause, the above query may be not be treated as a sub-query of the query QID2. The additional conditional ‘OR’ clause does not narrow the search of the query QID2. The above may not share the same query of parent, and hence is assigned a new query ID QID3, For example, when the above query is executed, a diagonal search cannot be performed from the memory address ‘X0005’ 218, since the second conditional ‘OR’ clause POPULATION=‘300’ is at memory address ‘X0002’ (as shown in
If it is determined that the diagonal search execution plan is available, at 308, a diagonal search logic can be invoked. Based on the diagonal search execution plan, the diagonal search logic is applied for the received search query. Diagonal search logic is applied on the table from a memory address retrieved from the dynamic table. Diagonal search is performed on the table stored in a database, and a search result is retrieved from the table. If it is determined that the diagonal search execution plan is not available, at 310, a set of default execution plans is used to access or query the database. The default search logic is provided by the default search execution plan. The default search logic is available in a search engine, and the diagonal search logic is plugged into the search engine. Based on the default search execution plan, the default search logic such as row scan or column scan is applied for the received search query. The default search logic is applied on the table stored in the database, and a search result is retrieved from the table. At 312, the retrieved search result is displayed in a graphical user interface. The result of execution of the default search logic may be stored in the dynamic table.
In one embodiment, when it is determined that the diagonal search execution plan is available, the memory address retrieved from the dynamic table may be used as a reference to perform the diagonal search. The diagonal search execution plan may include diagonal search logic to determine the best way to split the table into sub-tables to perform diagonal search. The table size may be one of the factors in splitting the table into sub-tables. With the memory address as reference, the table may be split into one or more sub-tables, and diagonal search may be performed on the sub-tables in parallel. The diagonal search is efficient and faster compared to row search or column search since the diagonal search is performed in parallel on the sub-tables.
For example, if the starting memory address identified from the dynamic table is ‘X0004’, the diagonal search execution plan determines that the diagonal scan is to be performed from memory location ‘X0004’. Accordingly, area of max growth ‘Delhi’ 426 in row 412 is scanned first, and the data pointer is incremented by ‘5’, and sales growth % ‘40’ 428 in row 416 is scanned. Subsequently, the data pointer is incremented by ‘5’, and population ‘150’ 430 in row 420 is diagonally scanned, and then country ‘China’ 432 in row 424 is diagonally scanned. The diagonal scan gives a visual appearance of tight to left diagonal scan in the user interface.
In one embodiment, the diagonal search execution plan may determine that multiple diagonal scans may be performed simultaneously on the table. For example, consider a query as shown below:
The memory address ‘X0001’ may be identified in the dynamic table. The diagonal search execution plan may determine that multiple simultaneous diagonal scans would be appropriate for the query QID4. The simultaneous diagonal scans are initiated from memory address ‘X0001’, and the memory address ‘X0004’. The diagonal scan from the memory address ‘X0001’ increments the data pointer and reaches the memory address ‘X0011’ where it finds a conditional clause sales growth=‘20’ 418 in memory location ‘X0011’ in row 420. Simultaneously, the diagonal scan from the memory address ‘X0004’ increments the data pointer and reaches the memory address ‘X0010’ where it finds a conditional clause population=‘150’ 430 in memory location ‘X0010’ in row 420. Since the diagonal scans have identified conditional clauses in the row 420, the diagonal search execution plan determines that a row scan would be appropriate. Therefore, the row scan is performed on the row 420 to retrieve a search result, In the execution of query QID4, the diagonal scan from the memory address ‘X0001’ involves ‘2’ data pointer movements in the table such as X0001->X0006->X0011, the other diagonal scan involves ‘2’ data pointer movement in the table such as X0004->X0007->X0010, and the row scan from memory location X0011 to X0012 involves data pointer movement and from memory location X0010 to X0009 involves ‘1’ data pointer movement. This results in a total of ‘2’+‘2’+‘1’+‘1’=‘6’ data pointer movement in the table. The time taken for the data pointer movement is much lesser in simultaneous diagonal search leading to faster search.
SELECT B, D, F, H FROM “RV_TB” (QID5)
Upon determining that a diagonal search execution plan is available on scanning a dynamic table, diagonal search logic is applied on the table 500. Based on the starting memory address identified in the dynamic table, the diagonal search execution plan determines that multiple diagonal searches can be simultaneously performed on the table 500. The diagonal search execution plan analyses the query QIDS, and determines that the table 500 may be split into a sub-table based on the columns in the select statement. Further, if size of the table 500 exceeds a certain length, the table 500 may be split further into sub-tables For example, since the query QID5 refers to columns B, D, F and H in the table ‘RV_TB’, the columns in the select clause are used to split into sub-table ‘RV_TB01 ’ as shown in 502, and ‘RV_TB02’ as shown in 504. The selected columns with a few rows are in sub-table ‘RV_TB01’ and the selected columns with remaining rows are in sub-table ‘RV_TB02’. Diagonal scan is independently performed on the sub-table ‘RV_TB01’ and sub-table ‘RV_TB02’. Multiple diagonal searches can be simultaneously performed on the sub-table ‘RV_TB01’ and sub-table ‘RV_TB02’. This way the diagonal searches are performed in parallel on the two split sub tables ‘RV_TB01’ and ‘RV_TB02’.
FIG.7 shows table 700 illustrating dynamic diagonal search, according to one embodiment. Upon determining that a diagonal search execution plan is available on scanning a dynamic table, diagonal search logic is applied on the table 700. Based on a starting memory address identified in the dynamic table, the diagonal search execution plan determines that a diagonal search can to be performed on the table 700. For example, when we receive a search query as shown below:
SELECT*FROM “RV_TB” WHERE A=‘1’ AND B =‘1’ (QID5)
the diagonal search execution plan determines the conditional clause A=‘1’ and B=‘1’, and further determines that a primary key is defined on column B. Since the primary key is defined on the column B, range scan can be initiated in parallel along with the diagonal search. The diagonal search logic in the diagonal search execution plan is used to perform range scan along with the diagonal search. The range scan is performed on rows 702, 704 and 706 since the conditional clause B=‘1’ is satisfied for these rows. The diagonal search 708 inherently initiates multiple parallel range scans on the individual rows 702, 704 and 706. The diagonal search is initiated as a main thread. The diagonal search 708 initiates parallel range scan 710 in row 702, parallel range scan 712 in row B 704, and parallel range scan 714 in row 706. Multiple parallel range scans may be bidirectional depending on the diagonal search logic configured. The multiple parallel column scans are initiated as parallel threads. Scanning of the table 700 is faster and the dependency between the individual scans is also reduced.
For example, section 902 of memory locations is in pattern ‘A’ indicating frequently accessed data with most successful search attempts. Section 404 of memory locations is in pattern indicating less frequently accessed data with few successful search attempts. Section 906 of memory locations is in pattern ‘C’ indicating data never accessed with no successful search attempts. The information in the heat map is consolidated over a period of time, and diagonal search may be performed leading to faster successful search attempts. The heat map can be used as a basis for defragmentation of the database. The defragmentation may be performed at a memory address/cell level based on the frequency pattern. For example, pattern ‘A’ cells shown in location 908 and 910 may be defragmented to contiguous storage location as shown in 912 so that the search yields faster results. Once data is defragmented to contiguous storage locations, data pointer increment is minimized, thereby improving speed of data access. Whenever a memory location is defragmented and moved to a new memory location, the new memory location is updated in the dynamic table in the field memory address.
Some embodiments may include the above-described methods being written as one or more software components. These components, and the functionality associated with each, may be used by client, server, distributed, or peer computer systems. These components may be written in a computer language corresponding to one or more programming languages such as functional, declarative, procedural, object-oriented, lower level languages and the like. They may be linked to other components via. various application programming interfaces and then compiled into one complete application for a server or a client. Alternatively, the components maybe implemented in server and client applications. Further, these components may be linked together via various distributed programming protocols. Some example embodiments may include remote procedure calls being used to implement one or more of these components across a distributed programming environment. For example, a logic level may reside on a first computer system that is remotely located from a second computer system containing an interface level (e.g., a graphical user interface). These first and second computer systems can be configured in a server-client, peer-to-peer, or some other configuration. The clients can vary in complexity from mobile and handheld devices, to thin clients and on to thick clients or even other servers.
The above-illustrated software components are tangibly stored on a computer readable storage medium as instructions. The term “computer readable storage medium” should be taken to include a single medium or multiple media that stores one or more sets of instructions. The term “computer readable storage medium” should be taken to include any physical article that is capable of undergoing a set of physical changes to physically store, encode, or otherwise carry a set of instructions for execution by a computer system which causes the computer system to perform any of the methods or process steps described, represented, or illustrated herein. Examples of computer readable storage media include, but are not limited to: magnetic media, such as hard disks, floppy disks, and magnetic tape; optical media such as CD-ROMs, DVDs and holographic devices; magneto-optical media; and hardware devices that are specially configured to store and execute, such as application-specific integrated circuits (ASICs), programmable logic devices (PLDs) and ROM and RAM devices. Examples of computer readable instructions include machine code, such as produced by a compiler, and files containing higher-level code that are executed by a computer using an interpreter. For example, an embodiment may be implemented using Java, C++, or other object-oriented programming language and development tools. Another embodiment may be implemented in hard-wired circuitry in place of, or in combination with machine readable software instructions.
A data source is an information resource. Data sources include sources of data that enable data storage and retrieval. Data sources may include databases, such as relational, transactional, hierarchical, multi-dimensional (e.g., OLAP), object oriented databases, and the like. Further data sources include tabular data (e.g., spreadsheets, delimited text files), data tagged with a markup language (e.g, XML data), transactional data, unstructured data (e.g., text files, screen scrapings), hierarchical data (e.g., data in a file system, XML data), files, a plurality of reports, and any other data source accessible through an established protocol, such as Open Data Base Connectivity (ODBC), produced by an underlying software system (e.g., ERP system), and the like. Data sources may also include a data source where the data is not tangibly stored or otherwise ephemeral such as data streams, broadcast data, and the like. These data sources can include associated data foundations, semantic layers, management systems, security systems and so on.
In the above description, numerous specific details are set forth to provide a thorough understanding of embodiments. One skilled in the relevant art will recognize, however that the embodiments can be practiced without one or more of the specific details or with other methods, components, techniques, etc. In other instances, well-known operations or structures are not shown or described in detail.
Although the processes illustrated and described herein include series of steps, it will be appreciated that the different embodiments are not limited by the illustrated ordering of steps, as sonic steps may occur in different orders, some concurrently with other steps apart from that shown and described herein. In addition, not all illustrated steps may be required to implement a methodology in accordance with the one or more embodiments. Moreover, it will be appreciated that the processes may be implemented in association with the apparatus and systems illustrated and described herein as well as in association with other systems not illustrated.
The above descriptions and illustrations of embodiments, including what is described in the Abstract, is not intended to be exhaustive or to limit the one or more embodiments to the precise forms disclosed. While specific embodiments of, and examples for, the one or more embodiments are described herein for illustrative purposes, various equivalent modifications are possible within the scope, as those skilled in the relevant art will recognize. These modifications can be made in light of the above detailed description. Rather, the scope is to be determined by the following claims, which are to be interpreted in accordance with established doctrines of claim construction.
