Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
quicklinks [2009/10/29 04:16]
admin
quicklinks [2020/11/30 18:56] (current)
doc
Line 1: Line 1:
-^    [[:​start|Quick Links ]]  ^^^^^ +~~NOTOC~~ 
-^   [[:general|General]] ​    ​^ ​  ​[[:​programs|Administration]] ​  ​^ ​      ​Development ​      ​^ ​  ​General Topics ​      ​^ ​  ​Miscellaneous Items^ +{{page>:sql_odxsql_bar&​nofooter&​noeditbtn}} 
-| [[overview:​intro|Omnidex Introduction]] | [[programs:​odxsql:​intro|OdxSQL]] |[[sql:​intro|Omnidex SQL and Query Types]] | [[design:​grids:​intro|Grids]] | [[releasenotes:​intro|Release Notes]] |     +===== Omnidex SQL Statements ​and OdxSQL Commands =====
-| [[odxadmin:​intro|Administrating Omnidex (OdxAdmin)]] ​    | [[programs:​odxnet:​intro|OdxNet]] |[[development:​interfaces:​odbc:​intro|ODBC]] | [[design:​unionview:​intro|Union Views]]| [[osinstall:​intro|Server Installs]] | +
-| [[design:​intro|Data and Index Design]] ​        | [[programs:​odxaim:​intro|OdxAim]] |[[development:​interfaces:​storedproc:​intro|Stored Procedures]] | [[design:​rolluptable:​intro|Rollup Tables]] | [[osinstall:​licensing:​intro|Licensing]] | +
-| [[]]       | [[programs:​dbinstal:​intro|Dbinstal]] ​  ​|[[development:​interfaces:​omniaccess:​intro|OmniAccess]] | [[design:​partitioning:​intro|Partitioning]] | [[osinstall:​settings:​intro|Settings]] | +
-| [[datawarehouse:​intro|Data Warehousing]] | [[programs:​nsadmin:​intro|NSAdmin]] | [[development:​interfaces:​clientoa:​intro|Client OA]] | [[design:​snapshots:​intro|Snapshots]] | [[synonyms:​intro|Synonym Lists]]| +
-| [[omnisearch:​intro|OmniSearch.Net]] | [[programs:​oahelper:​intro|OaHelper]] ​ | [[rdbms:​flatfile:​intro|Flat Files]] | [[development:​explainplan:​intro|Explain Plans]] | [[nls:​intro|Native Language]] | +
-| [[activecounts:​intro|Fast &Active Counts]] | [[programs:​oaenv:​intro|OaComp/​OaDecomp]] ​  | [[rdbms:​proprietary:​intro:​SqlServer/​Oracle]] | [[development:​debugging:​intro|Debugging]] | [[glossary:​intro|Glossary]] | +
-| [[powersearch:​intro|PowerSearch]] | [[programs:​client:​intro|DSEdit/​OdxQuery]] ​ | [[rdbms:​open:​intro|MySQL/​ODBC ]] | [[moretopics:​intro|More Topics]] | [[appendix:​intro|Appendix]] | +
-[[:​quicklinks|(c)]] Copyright Dynamic Information Systems - This document was last updated July 20, 2009.+
  
-development:interfaces:jdbc:intro|JDBC+^ DML ^| [[dev:​sql:​statements:​select:​home | Select ​ ]]| [[dev:​sql:​statements:​insert:​home |Insert ]] | [[dev:​sql:​statements:​delete:​home |Delete ]] | [[dev:​sql:​statements:​update:​home |Update ]] | [[dev:​sql:​statements:​qualify:​home |Qualify ]] | 
 +^ Access ^| [[dev:​sql:​statements:​connect:​home |Connect *]] | [[dev:​sql:​statements:​disconnect:​home | Disconnect *]] | [[dev:​sql:​statements:​attach:​home | Attach ]] | [[dev:​sql:​statements:​detach:​home | Detach ]] | [[dev:​sql:​statements:​set:​home | Set ]] | 
 +^ DDL Create ^| [[dev:​sql:​statements:​create_environment:​home | Create Environment ]] | [[dev:​sql:​statements:​create_database:​home | Create Database ]] | [[dev:​sql:​statements:​create_table:​home | Create Table ]] | [[dev:​sql:​statements:​create_index:​home | Create Index ]] | [[dev:​sql:​statements:​create_index_group:​home | Create Index Group ]] | 
 +^ DDL Drop  ^| [[dev:​sql:​statements:​drop_environment:​home | Drop Environment ]] | [[dev:​sql:​statements:​drop_database:​home |Drop Datebase ]] | [[dev:​sql:​statements:​drop_table:​home | Drop Table ]] | [[dev:​sql:​statements:​drop_index:​home | Drop Index ]] | [[dev:​sql:​statements:​drop_index_group:​home | Drop Index Group ]] | 
 +^ Syncing ^| [[dev:​sql:​statements:​update_indexes:​home | Update Indexes ]] | [[dev:​sql:​statements:​update_text:​home | Update Text ]] | [[dev:​sql:​statements:​update_statistics:​home | Update Statistics ]] | [[dev:​sql:​statements:​update_rollups:​home | Update Rollups ]] | [[dev:​sql:​statements:​update_rollup_table:​home | Update Rollup Table ]] | 
 +^ Special ^| [[dev:​sql:​statements:​export:​home | Export ]] | [[dev:​sql:​statements:​Partition:​home | Partition ]] | [[dev:​sql:​statements:​join:​home | Join ]] | [[dev:​sql:​statements:​open_cursor:​home | Open Cursor ]] | [[dev:​sql:​statements:​close_cursor:​home | Close Cursor ]]| 
 +^ Other ^| [[dev:​sql:​statements:​install_indexes:​home | Install Indexes ]] | [[ dev:​sql:​statements:​remove_indexes:​home | Remove Indexes ]] | [[dev:​sql:​statements:​load_ofx:​home | Load OFX ]]| [[dev:​sql:​statements:​command:​home | Command ]] | [[dev:​odxsql:commands:extract:home Extract *]] 
 +  ​
  
-----+* Statement is only available from OdxSQL. 
 +===== Omnidex SQL Standard Functions ===== 
 +| [[ dev:​sql:​functions:​abs | ABS ]]| [[ dev:​sql:​functions:​case | CASE ]] | [[ dev:​sql:​functions:​cast | CAST ]] |[[ dev:​sql:​functions:​character_length | CHAR[ACTER]_LENGTH ]] |[[ dev:​sql:​functions:​coalesce | COALESCE ]] | [[ dev:​sql:​functions:​concatenation | || (Concatenation) ]] |  
 +|[[ dev:​sql:​functions:​current_date | CURRENT_DATE ]] | [[ dev:​sql:​functions:​current_time | CURRENT_TIME]] | [[ dev:​sql:​functions:​current_timestamp | CURRENT_TIMESTAMP]] |[[ dev:​sql:​functions:​current_user | CURRENT_USER]] | [[ dev:​sql:​functions:​extract | EXTRACT]] | [[ dev:​sql:​functions:​lower | LOWER]] |  
 +| [[ dev:​sql:​functions:​mod | MOD ]] | [[ dev:​sql:​functions:​position | POSITION]] | [[ dev:​sql:​functions:​power | POWER ]] | [[ dev:​sql:​functions:​round | ROUND ]] | [[ dev:​sql:​statements:​select:​sample | SAMPLE ]] | [[ dev:​sql:​functions:​substring | SUBSTRING]] |  
 +| [[ dev:​sql:​functions:​session_user | SESSION_USER]] | [[ dev:​sql:​functions:​system_user | SYSTEM_USER ]] | [[ dev:​sql:​functions:​trim | TRIM ]] | [[ dev:​sql:​functions:​trunc | TRUNC ]] | [[ dev:​sql:​functions:​upper | UPPER ]] | [[ dev:​sql:​functions:​user | USER ]] | 
  
 +
 +===== Omnidex Extended Functions =====
 +| [[ dev:​sql:​functions:​calc_date | $CALC_DATE ]] | [[ dev:​sql:​functions:​col_length | $COL[UMN]_LENGTH ]] | [[ dev:​sql:​functions:​contains:​home | $CONTAINS ]] | [[ dev:​sql:​functions:​context:​home | $CONTEXT ]] | [[ dev:​sql:​functions:​convert | $CONVERT ]] | [[ dev:​sql:​functions:​compare_dates | $COMPARE_DATES]] |
 +|[[ dev:​sql:​functions:​current_row | $CURRENT_ROW ]] | [[ dev:​sql:​functions:​distance | $DISTANCE ]] | [[ dev:​sql:​functions:​external | $EXTERNAL ]] | [[ [[ dev:​sql:​functions:​highlight_criteria:​home | $HIGHLIGHT_CRITERIA ]] | [[ dev:​sql:​functions:​highlight_keywords:​home | $HIGHLIGHT_KEYWORDS ]] | [[ dev:​sql:​functions:​ifnull | $IFNULL ]] |
 +| [[ dev:​sql:​functions:​lj | $LJ ]] |[[ [[ dev:​sql:​functions:​lookup:​home | $LOOKUP ]] | [[ dev:​sql:​functions:​lpad | $LPAD ]] | [[ dev:​sql:​functions:​mod | $MOD ]]  | [[ dev:​sql:​functions:​proper | $PROPER ]] | [[ dev:​sql:​functions:​random | $RANDOM ]] |
 +| [[ dev:​sql:​functions:​rj | $RJ ]]  | [[ dev:​sql:​functions:​rpad | $RPAD ]] | [[ dev:​sql:​functions:​score | $SCORE ]] | [[ dev:​sql:​functions:​soundex | $SOUNDEX ]] | 
 +===== Oracle Functions =====
 +Use the CREATE ENVIRONMENT SQL_SYNTAX setting or the SET SQL_SYNTAX setting to activate.
 +| [[ dev:​sql:​orafunctions:​chr | CHR ]] | [[ dev:​sql:​orafunctions:​instr | INSTR ]] | [[ dev:​sql:​orafunctions:​new_time | NEW_TIME ]] | [[ dev:​sql:​orafunctions:​nvl | NVL ]] | [[ dev:​sql:​orafunctions:​substr | SUBSTR ]] | [[ dev:​sql:​orafunctions:​sys_extract_utc | SYS_EXTRACT_UTC ]] | [[ dev:​sql:​orafunctions:​sysdate | SYSDATE ]] | [[ dev:​sql:​orafunctions:​systimestamp | SYSTIMESTAMP ]] | [[ dev:​sql:​orafunctions:​to_date | TO_DATE ]] | [[ dev:​sql:​orafunctions:​to_number | TO_NUMBER ]] |
 +===== Omnidex Data Types =====
 +^See Also:​^[[dev:​sql:​datatypes|List of Datatypes]]^
 +=== Standard Datatypes ===
 +
 +Omnidex supports the following standard datatypes:
 +
 +^ Datatype ​               ^ Description ​                                                 ^
 +| CHARACTER(//​n//​) ​       | Space-filled character string of //n// characters ​           |
 +| CHAR(//​n//​) ​            | Synonym for CHARACTER(//​n//​) ​                                |
 +| STRING(//​n//​) ​          | Null-terminated character string of //n// characters ​        |
 +| VARCHAR(//​n//​)* ​        | String of max //n// characters, with length variable ​        |
 +| CLOB(//​n//​)* ​           | Character large object of max //n// characters, with length variable |
 +| [SIGNED] TINYINT ​       | 1-byte, signed integer ( -128 to 127 )                       |
 +| UNSIGNED TINYINT ​       | 1-byte, unsigned integer ( 0 to 255 )                        |
 +| [SIGNED] SMALLINT ​      | 2-byte, signed integer ( -32768 to 32767 )                   |
 +| UNSIGNED SMALLINT ​      | 2-byte, unsigned integer ( 0 to 65535 )                      |
 +| [SIGNED] INTEGER ​       | 4-byte, signed integer ( 2147483648 to 2147483647 )          |
 +| UNSIGNED INTEGER ​       | 4-byte, unsigned integer ( 0 to 4294967295 )                 |
 +| [SIGNED] BIGINT ​        | 8-byte, signed integer ( %%-2^63 to 2^63-1%% )               |
 +| UNSIGNED BIGINT ​        | 8-byte, unsigned integer ( %%0 to 2^64-1%% )                 |
 +| FLOAT                   | IEEE four-byte, single-precision floating point              |
 +| DOUBLE ​                 | IEEE eight-byte, double-precision floating point             |
 +| DATE                    | 10-byte ANSI date                                            |
 +| TIME                    | 11-byte ANSI time                                            |
 +| DATETIME ​               | 24-byte ANSI datetime ​                                       |
 +| ASCII DATE              | 8-byte ASCII date ( YYYYMMDD )                               |
 +| ASCII DATE(6) ​          | 6-byte ASCII date ( YYMMDD )                                 |
 +| ODBC DATE               | 6-byte ODBC proprietary date                                 |
 +| ODBC TIME               | 6-byte ODBC proprietary time                                 |
 +| ODBC DATETIME ​          | 6-byte ODBC proprietary datetime ​                            |
 +| ORACLE DATE             | 7-byte Oracle proprietary date                               |
 +| ORACLE TIME             | 7-byte Oracle proprietary time                               |
 +| ORACLE DATETIME ​        | 7-byte Oracle proprietary datetime ​                          |
 +| OMNIDEX DATE(//​n//​) ​    | Omnidex proprietary date supporting //n// digits of YYYYMMDD |
 +| OMNIDEX TIME(//​n//​) ​    | Omnidex proprietary time supporting //n// digits of HHMMSSNN |
 +| OMNIDEX DATETIME(//​n//​) | Omnidex proprietary datetime supporting //n// digits of YYYYMMDDHHMMSSNN |
 +| * Omnidex recommends use of the CHARACTER and STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable. ||
 +
 +=== National Character Datatypes === 
 +
 +Omnidex also provides partial support for National Character Datatypes (sometimes called
 +Unicode datatypes or wide characters). ​ The Omnidex SQL Engine will process these 
 +datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these 
 +datatypes.
 +
 +^ Datatype ​                 ^ Description ​                                               ^
 +| NATIONAL CHARACTER(//​n//​) | Space-filled character string of //n// characters ​         |
 +| NATIONAL CHAR(//​n//​) ​     | Synonym for CHARACTER(//​n//​) ​                              |
 +| NATIONAL STRING(//​n//​) ​   | Null-terminated character string of //n// characters ​      |
 +| NATIONAL VARCHAR(//​n//​)* ​ | String of max //n// characters, with length variable ​      |
 +| NATIONAL CLOB(//​n//​)* ​    | Character large object of max //n// characters, with length variable |
 +| * Omnidex recommends use of the NATIONAL CHARACTER and NATIONAL STRING datatypes rather than NATIONAL VARCHAR and NATIONAL CLOB due to the requirement of a length variable. ​ ||
 +
 +
 +===== Create a simple functioning Environment with an Omnidex index =====
 +<​code>​
 +os> odxsql
 +> CREATE ENVIRONMENT IN "​myenv.xml";​
 +> CREATE DATABASE mydb TYPE FLATFILE IN "​myenv.xml";​
 +> CREATE TABLE mytable PHYSICAL "​test1" ​
 +       ​(myseq integer, mytext char(60) quicktext) ​
 +       IN "​myenv.xml";​
 +> CONNECT myenv.xml;
 +> UPDATE INDEXES;
 +> SELECT * FROM mytable where mytext = '<​some criteria>';​
 +> DISCONNECT;
 +> EXIT
 +os>
 +</​code>​
 +===== SQL Statements by Category =====
 +
 +===== Omnidex SQL SELECT and Data Manipulation Statements =====
 +
 +Omnidex SQL supports SQL's SELECT and Data Manipulation Statements (INSERT, UPDATE, and DELETE). ​
 +
 +| [[dev:​sql:​statements:​select:​home|SELECT]] | Peform a Query Specification and SELECT rows. |
 +| [[dev:​sql:​statements:​insert:​home|INSERT]] | Insert rows into a Table. |
 +| [[dev:​sql:​statements:​update:​home|UPDATE]] | Update rows in a Table. |
 +| [[dev:​sql:​statements:​delete:​home|DELETE]] | Delete rows in a Table. |
 +===== Omnidex Connection Statements =====
 +Omnidex supports statements to establish connections and set processing options for the connection as well as attach and detach individual files to an Omnidex Environment.
 +
 +| [[dev:​sql:​statements:​connect:​home|CONNECT]] | Connect to an Omnidex Environment. |
 +| [[dev:​sql:​statements:​disconnect:​home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. |
 +| [[dev:​sql:​statements:​set:​home|SET]] | Set Omnidex SQL processing options. |
 +| [[dev:​sql:​statements:​attach:​home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. |
 +| [[dev:​sql:​statements:​detach:​home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
 +
 +===== Omnidex SQL Data Definition Language (DDL) Statements =====
 +Omnidex SQL has statements that create and drop objects within an Omnidex Environment.
 +
 +Also see the OdxSQL Extract command to generate DDL statements from an existing Omnidex Environment.
 +
 +^ DDL Statement ^  Description ​ ^
 +| [[dev:​sql:​statements:​create_environment:​home|CREATE ENVIRONMENT]] | Create an Omnidex Environment into a named physical operating system file. |
 +| [[dev:​sql:​statements:​create_database:​home|CREATE DATABASE]] | Create a Database within an Omnidex Environment. |
 +| [[dev:​sql:​statements:​create_table:​home|CREATE TABLE]] | Create a Table within a Database in the Omnidex metadata or an Omnidex or RDBMS temporary table. |
 +| [[dev:​sql:​statements:​create_index:​home|CREATE INDEX]] | Specify an Index in the Omnidex metadata but will not physically create the index. |
 +| [[dev:​sql:​statements:​create_index_group:​home | CREATE INDEX GROUP ]] | Specify an Index Group within the Omnidex metadata. |
 +| [[dev:​sql:​statements:​drop_environment:​home|DROP ENVIRONMENT]] | Deletes the Omnidex Metadata Environment by physically deleting the operating system file. |
 +| [[dev:​sql:​statements:​drop_database:​home|DROP DATABASE]] | Remove the named Database from the Omnidex metadata. |
 +| [[dev:​sql:​statements:​drop_table:​home|DROP TABLE]] | Remove a table from the Omnidex metadata or deletes a previously created temporary ​ table. |
 +| [[dev:​sql:​statements:​drop_index:​home|DROP INDEX]] | Removes an index from the Omnidex metadata. |
 +| [[dev:​sql:​statements:​drop_index_group | DROP INDEX GROUP]] | Removes a named Index Group from the Omnidex metadata. |
 +| [[dev:​sql:​statements:​attach:​home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. |
 +| [[dev:​sql:​statements:​detach:​home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
 +
 +
 +===== Omnidex SQL Query and Export Statements =====
 +| [[dev:​sql:​statements:​select:​home|SELECT]] | Peform a Query Specification and SELECT rows. |
 +| [[dev:​sql:​statements:​export:​home|EXPORT]] | Export rows or rowids to an external file. |
 +| [[dev:​sql:​statements:​open_cursor:​home |OPEN CURSOR]] | Open a Cursor. |
 +| [[dev:​sql:​statements:​close_cursor:​home|CLOSE CURSOR]] | Close a previously opened cursor. |
 +===== Omnidex SQL Index and Processing Maintenance Statements =====
 +Omnidex supports several statements to synchronize the Omnidex indexes, rollup tables and internal statistics. ​
 +
 +| [[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]] | Update Omnidex Indexes. |
 +| [[dev:​sql:​statements:​update_rollup:​home|UPDATE ROLLUP]] | Update Rollup Table with summarized information. |
 +| [[dev:​sql:​statements:​update_rollups:​home|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. | 
 +| [[dev:​sql:​statements:​update_statistics:​home|UPDATE STATISTICS]] | Update Statistics Information. |
 +| [[dev:​sql:​statements:​home:​update_text|UPDATE TEXT]] | Update Textual Statistics. |
 +| [[dev:​sql:​statements:​partition:​home|PARTITION]] | Partition a table into smaller sub tables based on criteria. |
 +| [[dev:​sql:​statements:​load_ofx:​home|LOAD OFX]] | Load Indexes for a table. | 
 +| Install Indexes | Specifies the Omnidex indexes into the Omnidex XML Environment. No indexing is performed. |
 +| Remove Indexes | Removes the Omnidex indexes from the Omnidex XML Environment. |
 +
 +===== Omnidex SQL Index Query Statements =====
 +Omnidex supports lower level QUALIFY and JOIN statements to query the Omnidex indexes directly. ​ These statements are advanced statements used for testing and to support legacy applications. ​ The SELECT statement is now typically used instead.
 +
 +| [[dev:​sql:​statements:​qualify:​home|QUALIFY]] | Qualify index counts using Omnidex criteria. |
 +| [[dev:​sql:​statements:​join:​home|JOIN]] | Join tables using Omnidex Indexes. |
 +
 +
 +
 +
 +
 +
 +===== Learning Omnidex =====
 +  * [[ tutorials:​simple_flatfile | Creating a very simple flat file Environment and doing retrievals. ]]
 +  * [[ tutorials:​simple_flatfile_java_JDBC | Writing a simple JDBC program to access the simple flatfile Environment. ]]
 + 
 +
 +
 +^   ​[[overview:​home | Overview]] ​    ​^ ​  ​[[nopage|Topics]] ​  ​^ ​      ​[[admin:​home | Administration]] ​     ^   [[ dev:home | Development]] ​      ​^ ​  ​[[nopage | General]] ​ ^
 +| [[overview:​omnidex | What is Omnidex?]] | [[odxadmin:​nopage | Fast Counts]] | [[programs:​odxadmin:​home | Omnidex Administrator]]| [[dev:​sql:​home | Omnidex SQL Reference]] | [[osinstall:​home | Software Installation]] |    ​
 +| [[nopage | Omnidex Architecture]] | [[admin:​nopage | Fast Aggregations]] | [[programs:​odxsql:​home | OdxSQL]] | [[dev:​odbc:​home | ODBC]] | [[nopage | Platform Support]] |
 +| [[nopage | Omnidex on Customer Lists]]| [[nopage | Textual Searches]] | [[sql:​explain:​home | Optimizing Query Performance]] |[[dev:​jdbc:​home|JDBC]] | [[appendix:​releasenotes | Release Notes]] |
 +| [[nopage | Omnidex on Business Analytics]] ​    | [[admin:​nopage | Fuzzy Searches]] ​  ​|[[nopage | RDBMS Topics]] | [[dev:​dbprocs:​home | Stored Procedures]] | [[glossary:​home|Glossary]] |
 +| [[nopage | Omnidex on Transaction Logs]] | [[admin:​nopage | Portable Snapshots]] | [[programs:​legacy|Legacy Tools/​APIs]] | [[dev:debug | Debugging]] | [[appendix:​docmap | Documentation Map]]|
 +| [[nopage | Omnidex on Flat File Databases]] | [[admin:​grid:​overview | Scalable Grids]] | [[admin:​nopage | Administrator FAQ]] | [[dev:​nopage |Developer FAQ]] | [[appendix:​nopage | Installation FAQ]]|
 +[[:​quicklinks|(c)]],,​Copyright Dynamic Information Systems - This document was last updated July 6, 2010,,
 
Back to top
quicklinks.1256789819.txt.gz · Last modified: 2016/06/28 22:38 (external edit)