250 Informix Dynamic Server 11: Advanced Functionality for Modern Business
Example 9-3 Query plan with BTS index
QUERY:
------
SELECT id FROM article_tab WHERE bts_contains (title,'"java"~10')
Estimated Cost: 0
Estimated # of Rows Returned: 1
1) keshav.article_tab: INDEX PATH
(1) VII Index Keys: title (Serial, fragments: ALL)
VII Index Filter:
informix.bts_contains(keshav.article_tab.title,"java"~10 )
UDRs in query:
--------------
UDR id : 400
UDR name: bts_contains
9.3 Binary data types
In IDS, binary data can be stored in BYTE or BLOB data types. Both types are
designed to handle large data volumes (BYTE up to 2 GB and BLOB up to 4 TB),
are stored out of row, and do not support indexes. For applications using smaller
binary strings, IDS 11 has two new types to consider:
binary18 and binaryvar.
The input to these data types are ASCII strings with hexadecimal [0-9A-Fa-f]
digits. Because the data is stored as bytes, the input hexadecimal string must
have an even number of digits. Binary18 is a fixed 18 byte type. Binaryvar is a
variable length and can store 255 bytes. IDS stores these types in-row and
supports B-tree indexes on these types. IDS considers available indexes on
these binary types as viable access paths. The Binary DataBlade module
shipped with IDS provides these binary data types. Register the Binary
DataBlade module with BladeManager and then create tables using the new
types, as shown in Example 9-4.
Example 9-4 Create tables
create table bin18_test (int_col integer, bdt_col binary18);
insert into bin18_test values (0, '0102');
insert into bin18_test values (1, '01020304');
insert into bin18_test values (2, '0102030405060708');
insert into bin18_test values (3, '0102030405060708090A0B0C');
-- The hexadecimal prefix 0x is allowed as a prefix.
Chapter 9. SQL Language 251
insert into bin18_test values (3, '0X0102030405060708090A0B0C');
create table bindata_test (int_col integer, bin_col binaryvar)
insert into bindata_test values (1, '30313233343536373839')
insert into bindata_test values (2, '0X30313233343536373839')
-- create indices on binary types
create index idx_bin18 on bin18_test(bdt_col);
create index idx_binvar on bindata(bin_col);
Both data types are indexable with a B-tree index and, therefore, are considered
during optimization of the query. IDS does not create distributions on binary
columns and the optimizer assumes a selectivity of 0.1 from the binary column.
Use query directives to force the selection of alternative access paths when you
see that these assumptions result in suboptimal plans for queries with binary
data types.
The following new functions operate on binary columns:
򐂰 length(binary_column)
򐂰 octet_length(binary_column)
Here is an example of their use:
select length(bin_col) from bindata_test where int_col=1;
(expression)
10
The following bitwise operations are supported on binary data types:
򐂰 bit_and(arg1, arg2) implements the bitwise AND operator.
򐂰 bit_or(arg1, arg2) implements the bitwise OR operator.
򐂰 bit_xor(arg1, arg2) implements the bitwise XOR (exclusive OR) operator.
򐂰 bit_complement(arg1) implements the bitwise NOT.
Here is an example of their use:
create table bindata_test (int_col integer, bin_col binaryvar)
insert into bindata_test values (1, '00001000');
insert into bindata_test values (2, '00002000');
insert into bindata_test values (3, '00004000');
insert into bindata_test values (4, '023A2DE4');
select bit_or(bin_col, '00004000')
from bindata_test where int_col=2; -- add CASE stmt here.

Get Informix Dynamic Server 11: Advanced Functionality for Modern Business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.