Differences

This shows you the differences between two versions of the page.

--- integration:rdbms:queries [2011/03/14 19:20]
127.0.0.1 external edit
+++ integration:rdbms:queries [2016/06/28 22:38] (current)
@@ Line 10: / Line 10: @@
 **[[integration:rdbms:queries|Queries]]** |
 [[integration:rdbms:updates|Updates]] |
-[[integration:rdbms:creation|Generating Data Files]] |
+[[integration:rdbms:integration|Integration Guides]]
-[[integration:rdbms:implementation|Implementation Guides]]
 ----
-==== Querying Raw Data Files ====
+==== Querying Relational Databases ====
-Omnidex allows applications to issue SQL statements against raw data files using standard ODBC and JDBC interfaces.  Once a raw data file has been included in an Omnidex Environment File, it is treated like a standard table.  Applications can relate with this data file as a standard table, just as though the table was resident in a relational database.
+=== Query Optimization ===
-Applications can take advantage of the great breadth of the SQL language.  Queries can filter rows using criteria, join tables, aggregate rows, order rows and process functions.
+Omnidex allows the relational database tables and the Omnidex indexes to be queried using standard SQL statements using an ODBC or JDBC interface.  The query optimizer within Omnidex will evaluate the SQL statement and choose the best approach for processing.  Queries will only access the indexes or the database tables if needed.
-== Criteria ==
+Some queries will be processed entirely within the Omnidex indexes with no need to access the underlying relational database, as shown below.
-Omnidex supports the complete suite of criteria options in the SQL language against raw data files.  Omnidex indexing should be installed on criteria columns to achieve the best performance.
+{{:integration:rdbms:relational_database_queries_indexes_only.png|}}
-Delimited and fixed-length raw data files do not differentiate a NULL column as is done in a relational database.  Omnidex will treat empty fields as NULL fields to compensate for this.  For character-class datatypes, an IS NULL test will qualify any row containing an empty space in that column.  For binary-class datatypes, an IS NULL test will qualify any row containing a zero in that column.
+Some queries may be processed entirely in the relational database with no need to access the Omnidex indexes, as shown below.
-== Table Joins ==
+{{:integration:rdbms:relational_database_queries_database_only.png|}}
-Omnidex supports standard table joins between raw data files, including inner joins, outer joins and cross joins.  Since raw data files do not generally have native indexing capabilities like relational databases, it is important to install Omnidex indexing on the join columns.  This will insure the best optimization of table joins.
+Some queries will be processed using a combination of Omnidex indexes and the underlying relational database, as shown below.
-== Aggregations ==
+{{:integration:rdbms:relational_database_queries_combined.png|}}
-Omnidex supports standard aggregations such as COUNT, SUM, AVG, MIN and MAX, both with and without the GROUP BY clause.  Omnidex indexing should be installed to achieve the best performance.
+=== Omnidex SQL ===
-Aggregations are generally appropriate for binary datatypes.  For delimited files, be sure to declare numeric columns using binary datatypes to allow aggregations.  Of course, binary datatypes can only be declared when the numeric data is consistent and valid across all rows.
+Queries can be submitted using the Omnidex SQL language.  Omnidex SQL consists of ANSI SQL, supplemented with Omnidex extensions and functions.  Omnidex also supports a limited number of proprietary functions found in the popular relational databases.  Access to these relational database extensions can be controlled using the [[dev:sql:statements:set_sql_syntax|SET SQL_SYNTAX]] statement.  Applications using a particular SQL dialect from a relational database will need to have the syntax adjusted to Omnidex SQL.
-== Ordering ==
+Omnidex will attempt to optimize all queries; however, like the relational databases, queries can sometimes be tuned by restructuring or reorganizing the query.  Omnidex does favor certain approaches to queries.  Additionally, many poor-performing relational queries have been customized by talented and hard-working database administrators to achieve the best performance for the underlying relational database.  This may not lead to the best performance with Omnidex.  Consult the documentation on [[admin:optimization:home|optimizing queries]] to understand more about how queries can be optimally structured for Omnidex.
-Omnidex supports ordering result sets using the ORDER BY clause.  Omnidex indexing should be installed to achieve the best performance.
-== Functions ==
-Omnidex allows a wide selection of [[dev:sql:functions:home|SQL Functions]] that can be used against raw data files.
-Omnidex also allows [[admin:features:expressioncols|Expression-based Columns]] in a table declaration, which create a virtual column that returns the result of a SQL expression.
-== Datatypes ==
-Raw data files can support retrieving data in a wide variety of datatypes.  The can store character and binary data, and data can be further converted in SQL statements.  Raw data files do not support VARCHAR and CLOB datatypes due to their reliance on length variables; however, CHARACTER and STRING datatypes may be used instead with greater convenience.
-====  ====
-\\
-In general terms, applications should treat a raw data file just like any other relational database.  Many companies transfer their data from relational databases to raw data files, and the only difference their applications notice is an increase in performance.

Back to top

integration/rdbms/queries.1300130416.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes