This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:text:clob [2011/01/25 17:28] els |
admin:indexing:text:clob [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 2: | Line 2: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Chapter ====== | + | ====== Administration: Omnidex Indexing ====== |
| - | ===== Topic ===== | + | ===== Omnidex Text ===== |
| - | **[[nav|Nav1]]** | [[nav|Nav2]] | + | [[admin:indexing:text:home|Overview]] | |
| + | **[[admin:indexing:text:clob|Textual Datatypes]]** | | ||
| + | [[admin:indexing:text:retrieve|External Files]] | | ||
| + | [[admin:indexing:text:proximity|Proximity Searches]] | | ||
| + | [[admin:indexing:text:advanced|Advanced Searches]] | | ||
| + | [[admin:indexing:text:results|Displaying Results]] | | ||
| + | [[admin:indexing:text:relevancy|Relevancy]] | ||
| ---- | ---- | ||
| + | ==== Textual Datatypes ==== | ||
| + | Databases generally store text in character columns or in variable character columns (often called VARCHAR columns). These datatypes are good for storing smaller amounts of text, such as names, addresses and short descriptions. These datatypes are often limited in size, however. For larger amounts of text, a database may employ special datatypes designed specifically for this purpose. As examples, Oracle provides a CLOB (Character Large Object) datatype and SQL Server provides a TEXT datatype. These datatypes can often store up to 2-4 gigabytes of text. | ||
| - | ==== Subtopic ==== | + | Omnidex recommends storing text in either CHARACTER or STRING datatypes. These are the simplest datatypes to use and provide great flexibility. Omnidex also supports VARCHAR and CLOB dataypes, but these datatypes are more difficult to use and are more restricted. For most applications, the STRING datatype will be preferred since it allows null-terminated data up to 16 MB. |
| - | ^Characteristics ^ CHAR ^ C STRING ^ VARCHAR ^ CLOB ^ | ||
| - | |\\ **Datatype Characteristics** ||||| | ||
| - | |Character data allowed | Y | Y | Y | Y | | ||
| - | |Binary data allowed | N | N | N | N | | ||
| - | |Embedded nulls allowed | | | Y | Y | | ||
| - | |Null-terminated | | Y | | | | ||
| - | |Data_lengths required | | | Y | Y | | ||
| - | |Max size | 4,095 | 64mb | 4,095 | 64mb | | ||
| - | |\\ **Usage Characteristics** ||||| | ||
| - | |Select item of simple query | Y | Y | Y | Y | | ||
| - | |Select item of outer query | Y | Y | Y | Y | | ||
| - | |Select item of nested query | Y | Y | Y | N | | ||
| - | |Select item of INSERT | Y | Y | Y | N | | ||
| - | |Select item of set operation | Y | Y | Y | Y | | ||
| - | |Table joins | Y | Y | Y | N | | ||
| - | |WHERE clause | Y | Y | Y | Limited <sup>1</sup> | | ||
| - | |GROUP BY clause | Y | Y | Y | N | | ||
| - | |ORDER BY clause | Y | Y | Y | N | | ||
| - | |HAVING clause | Y | Y | Y | N | | ||
| - | |SELECT INTO clause | Y | Y | Y | N | | ||
| - | |Aggregate functions | Y | Y | Y | N | | ||
| - | |SQL Functions | Y | Y | Y | Limited <sup>2</sup> | | ||
| - | | | | | | N | | ||
| - | |\\ **Update Characteristics** ||||| | ||
| - | | Inserts, updates and deletes | Y | Y | Y | N | | ||
| - | |--------------------------------------------------------------------------|----------------|----------------|----------------|----------------| | ||
| - | <sup>1. Limited to use of LIKE, $CONTAINS and IS NULL operators.</sup> | + | ^ Datatype ^ Description ^ |
| + | |\\ \\ \\ **CHARACTER**\\ \\ \\ \\ |Space-padded data up to 4,095 bytes.| | ||
| + | |\\ \\ \\ **STRING** \\ \\ \\ \\ |Null-terminated data up to 64MB. If indexed with Omnidex, the extracted text from this column may be up to 16MB.| | ||
| + | |\\ \\ \\ **VARCHAR** \\ \\ \\ \\ |Non-terminated and non-padded data up to 4,095 bytes. This datatype may contain embedded null characters since it is not null-terminated; however, it should not be used to store binary data. When using API's to access this datatype, data lengths variables are required since no terminator is used to indicate the end of the text. This datatype is also not appropriate for fixed length raw data files since the data length cannot be stored in raw data files. Raw data files should use CHARACTER or STRING datatypes.| | ||
| + | |\\ \\ \\ **CLOB** \\ \\ \\ \\ |Non-terminated and non-padded data up to 64MB. If indexed with Omnidex, the extracted text from this column may be up to 16MB. This datatype may contain embedded null characters since it is not null-terminated; however, it should not be used to store binary data. When using API's to access this datatype, data lengths variables are required since no terminator is used to indicate the end of the text. This datatype is also not appropriate for fixed length raw data files since the data length cannot be stored in raw data files. Raw data files should use CHARACTER or STRING datatypes.\\ \\ The handling of CLOB data may be more expensive than the handling of CHARACTER, STRING and VARCHAR data. It is better to use those datatypes if their size limitations will not be exceeded.| | ||
| + | \\ | ||
| + | ---- | ||
| + | ==== Comparing Textual Datatypes ==== | ||
| + | |||
| + | The textual datatypes have different characteristics and have different restrictions within Omnidex SQL. The following table shows the capabilities of each datatype. | ||
| + | |||
| + | |< 80% 40% 15% 15% 15% 15% >| | ||
| + | ^Characteristics ^ CHARACTER ^ STRING ^ VARCHAR ^ CLOB ^ | ||
| + | |\\ **Datatype Characteristics** ||||| | ||
| + | |Character data allowed | Yes | Yes | Yes | Yes | | ||
| + | |Binary data allowed | No | No | No | No | | ||
| + | |Embedded nulls allowed | No | No | Yes | Yes | | ||
| + | |Null-terminated | No | Yes | No | No | | ||
| + | |Data_lengths required | No | No | Yes | Yes | | ||
| + | |Max size | 4,095 | 16mb | 4,095 | 16mb | | ||
| + | |\\ **Usage Characteristics** ||||| | ||
| + | |Select item of simple query | Yes | Yes | Yes | Yes | | ||
| + | |Select item of outer query | Yes | Yes | Yes | Yes | | ||
| + | |Select item of nested query | Yes | Yes | Yes | No | | ||
| + | |Select item of INSERT | Yes | Yes | Yes | No | | ||
| + | |Select item of set operation | Yes | Yes | Yes | Yes | | ||
| + | |Table joins | Yes | Yes | Yes | No | | ||
| + | |WHERE clause | Yes | Yes | Yes | Limited <sup>1</sup> | | ||
| + | |GROUP BY clause | Yes | Yes | Yes | No | | ||
| + | |ORDER BY clause | Yes | Yes | Yes | No | | ||
| + | |HAVING clause | Yes | Yes | Yes | No | | ||
| + | |SELECT INTO clause | Yes | Yes | Yes | No | | ||
| + | |Aggregate functions | Yes | Yes | Yes | No | | ||
| + | |SQL Functions | Yes | Yes | Yes | Limited <sup>2</sup> | | ||
| + | |\\ **Update Characteristics** ||||| | ||
| + | |Inserts, updates and deletes | Yes | Yes | Yes | No | | ||
| + | <sup>1. Limited to use of LIKE, $CONTAINS and IS NULL operators.</sup>\\ | ||
| <sup>2.See individual functions for compatibility with CLOB datatype.</sup> | <sup>2.See individual functions for compatibility with CLOB datatype.</sup> | ||
| + | |||
| ===== ===== | ===== ===== | ||
| - | **[[prev|Prev]]** | **[[next|Next]]** | + | **[[admin:indexing:text:home|Prev]]** | |
| + | **[[admin:indexing:text:retrieve|Next]]** | ||
| ====== Additional Resources ====== | ====== Additional Resources ====== | ||
| Line 52: | Line 72: | ||
| See also: | See also: | ||
| - | {{page>:admin:see_also&nofooter&noeditbtn}} | + | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} |
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||
| + | |||
| + | |||