Informix Dynamic Server 11: Advanced Functionality for Modern Business

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.