| Pub. Date | : Sep, 2018 |
|---|---|
| Product Name | : The IUP Journal of Soft Skills |
| Product Type | : Article |
| Product Code | : IJIT21809 |
| Author Name | : Shefali Naik |
| Availability | : YES |
| Subject/Domain | : Management |
| Download Format | : PDF Format |
| No. of Pages | : 13 |
The use of appropriate indexing improves the performance of transactions in heterogeneous distributed database, whereas inappropriate or no indexing deteriorates the same. Properly designed index leads to faster data access, which ultimately improves the execution of transactions. Various Relational Database Management Systems (RDBMS) and third-party tools exist, which provide suggestion for index management, but up to certain limits. These tools provide index suggestion with limited and simple queries. They do not analyze or suggest index for aggregate queries, sub-queries and other complicated queries. The applications which access data from heterogeneous databases need an index evaluator and recommender to analyze and recommend indexes for tables. For this type of multiple heterogeneous distributed databases, Table Index Evaluator and Recommender (TIER) is proposed which takes set of queries as inputs. Queries in the set are based on local and remote tables (tables which are distributed on various RDBMS). In order to recommend indexes, the fields which are mentioned in WHERE and HAVING clauses of inputted set of queries should be parsed. Besides this, the total frequency of each field table-wise and overall is required. The parsed fields with frequency result in Parse Matrix (PM) and the obtained PM is used by TIER for further processing. In this paper, the algorithm to obtain Clause Matrix (CM) and PM is described.
To improve concurrent transaction execution in multiple heterogeneous distributed databases, searching is the most important criteria. In ecommerce applications, many concurrent users access the same information in parallel. Users select various criteria for searching. When the search is initiated by users, these criteria will be sent to the database server in the form of Structured Query Language (SQL) queries. These may be nested queries, queries with many fields in WHERE and HAVING clauses, correlated queries, join queries, queries with criteria specified on functions or a combination of all the specified. Time taken to access and transfer required data after execution of these queries affects the overall execution of transactions. If the indexes are defined on tables properly, it helps to improve searching and hence overall execution of transactions. Many Relational Database Management Systems (RDBMS) create the indexes on primary key automatically, which are not useful most of the times. This may degrade the performance. Apart from this, application programmer or database administrators decide which indexes to be created on tables when they develop the applications. But these indexes may not be very useful in real time. Therefore, a mechanism is required which can evaluate the existing indexes and recommend new indexes. Many such tools are available, but there is no tool which recommends indexes for distributed queries. Naik (2014a) proposed Table Index Evaluator and Recommender (TIER) model to analyze and recommend table indexes for queries of multiple heterogeneous database (Ozu and Valdureiz, 2011). One of the modules of TIER is SQL Parser which is required to parse distributed queries. In this paper, the implementation of algorithm to generate Clause Matrix (CM) and Parse Matrix (PM) is described.
Parsing, Structured Query Language (SQL), Distributed transaction, Heterogeneous distributed database