Given the table:
create table address(The simplest case is to create a composite index on all these columns as if it were one column. With a single column, we specify a column and opclass pair. With a composite index, 2 or more column and opclass pairs are specified and the strings in each column are concatenated together to form one string and indexed in the default contents field. As with a single column, the opclass must match the data type of its column. Here is what the create index looks like:
create index bts_idx on address(A search may use any of the columns in the bts_contains() predicate:
postalcode bts_char_ops) using bts;
select * from address where bts_contains(fname, 'john');would produce the same results as
select * from address where bts_contains(address1, 'john');
Now with an XML document we can index the tags as fields in the BTS index. Why not do the same with composite indexes and have BTS use the column in the bts_contains() as the field to search? Well you can!
Lets step back for a moment. When the clucene index is created on unstructured text, its indexed under a field called contents. This is the default field that is used when a query term does not specify a field. So the predicate: bts_contains(mycol, 'john') is equivalent as bts_contains(mycol, 'contents:john').
With XML, we index the tag information in a number of different fields. We can list a specific set of tags to index with the xmltags parameter. These tags are indexed as tags and by default, the first tag in that list is the default field for searching (instead of the field named contents). For example, xmltags="(name,address,comment)", the default field is name. In this case, bts_contains(mycol, 'john') is equivalent as bts_contains(mycol, 'name:john'). In the case of all_xmltags, there is no definitive list of tags so the default field for queries defaults back to contents. However, BTS provides a query_default_field parameter to explicitly specify the default field used for queries.
Now back to composite indexes. With a query, we need to specify the column name. In the simple case all columns are indexed into the contents field, so the column name is not used. But if you want to index each column as a field and used the field designated as the default column to search, then you can by specifying query_default_field="*". The * tells BTS to build an index with each the text in each column indexed as a separate field after the name of the column and that also becomes the default field on queires.
create index bts_idx on address(The query:
postalcode bts_char_ops) using bts(query_default_field="*");
select * from address where bts_contains(fname, 'john');is equivalent to:
select * from address where bts_contains(fname, 'fname:john');This will only search text in the fname field and would not for example find any street name john in the address1 or address2 field.
Finally, the search is not limited to one column, like with XML,you can search different tags, you can do the same with composite indexes. Just specify the column name as a field to search.
select * from address where bts_contains(fname, 'nancy ANDcity:nipigon');Will search for a row that contains nancy in the fname column and nipigon in the city column.
A closing thought, what about composite BTS indexes on columns of XML. It does work and perhaps a topic for another blog entry.