Hi

I'm working on integration system which periodically retrieves data to
Oracle from BTrieve database using heterogeneous service and Pervasive
ODBC.

Queries from Oracle understand BTrieve indexes through ODBC, even
composite, so everything goes well.

But now I got a problem: Oracle understands and uses NOT all indexes
from BTrieve database. Some indexes are used, but some are not. I can't
understand the reason why index 1 easily used if needed, but index 2
cannot be used in any way.

For example, here's the structure of some table and information about
indexes. Key1 works good, but query against Key6 fields leads to
fullscan.

SQL> select column_name, rpad(data_type,10), rpad(data_length,4) from
all_tab_columns@lux where table_name = 'ininv1_dbt';

COLUMN_NAME RPAD(DATA_TYPE,10) RPAD(DATA_LENGTH,4)
------------------------------ ------------------ -------------------
Autokey NUMBER 4
Ndprt NUMBER 2
Invoice NUMBER 4
Source NUMBER 2
Target NUMBER 2
Date DATE 6
DateSend DATE 6
DebetSign NUMBER 2
iType NUMBER 2
Contract VARCHAR2 10
Receips VARCHAR2 10
Numb_Invoice VARCHAR2 10
RinvDprt NUMBER 2
Oper NUMBER 2
Prim VARCHAR2 79
iDoc_Dprt NUMBER 2
lDoc NUMBER 4
FormWHouse NUMBER 2
iTypeDoc NUMBER 2
Mail DATE 6
Reserve VARCHAR2 111

SQL> select index_name, column_name from all_ind_columns@lux where
table_name = 'ininv1_dbt' order by index_name, column_position;

INDEX_NAME COLUMN_NAME
------------------------------ ----------------------
key0 Autokey
key1 Ndprt
key1 Invoice
key2 Date
key3 DebetSign
key3 Date
key4 FormWHouse
key4 Ndprt
key5 iType
key5 Date
key6 iTypeDoc
key6 iDoc_Dprt
key6 lDoc

Please give me any idea of such behavior.

Oracle is coordinating the retrieval, and mapping the data dictionary
but not actually executing the query.

The remote database (BTrieve) is doing that.

You need to work out why BTrieve does not believe using the index that
you expect is a viable execution path.

ODBC may be a factor in the way the query is being presented, or it may
be something to do with the data.


plimouthrock@mail.ru wrote:
> Hi
>
> I'm working on integration system which periodically retrieves data to
> Oracle from BTrieve database using heterogeneous service and Pervasive
> ODBC.
>
> Queries from Oracle understand BTrieve indexes through ODBC, even
> composite, so everything goes well.
>
> But now I got a problem: Oracle understands and uses NOT all indexes
> from BTrieve database. Some indexes are used, but some are not. I can't
> understand the reason why index 1 easily used if needed, but index 2
> cannot be used in any way.
>
> For example, here's the structure of some table and information about
> indexes. Key1 works good, but query against Key6 fields leads to
> fullscan.
>
> SQL> select column_name, rpad(data_type,10), rpad(data_length,4) from
> all_tab_columns@lux where table_name = 'ininv1_dbt';
>
> COLUMN_NAME RPAD(DATA_TYPE,10) RPAD(DATA_LENGTH,4)
> ------------------------------ ------------------ -------------------
> Autokey NUMBER 4
> Ndprt NUMBER 2
> Invoice NUMBER 4
> Source NUMBER 2
> Target NUMBER 2
> Date DATE 6
> DateSend DATE 6
> DebetSign NUMBER 2
> iType NUMBER 2
> Contract VARCHAR2 10
> Receips VARCHAR2 10
> Numb_Invoice VARCHAR2 10
> RinvDprt NUMBER 2
> Oper NUMBER 2
> Prim VARCHAR2 79
> iDoc_Dprt NUMBER 2
> lDoc NUMBER 4
> FormWHouse NUMBER 2
> iTypeDoc NUMBER 2
> Mail DATE 6
> Reserve VARCHAR2 111
>
> SQL> select index_name, column_name from all_ind_columns@lux where
> table_name = 'ininv1_dbt' order by index_name, column_position;
>
> INDEX_NAME COLUMN_NAME
> ------------------------------ ----------------------
> key0 Autokey
> key1 Ndprt
> key1 Invoice
> key2 Date
> key3 DebetSign
> key3 Date
> key4 FormWHouse
> key4 Ndprt
> key5 iType
> key5 Date
> key6 iTypeDoc
> key6 iDoc_Dprt
> key6 lDoc
>
> Please give me any idea of such behavior.

Btrieve itself uses this index, as I can conclude running test
operation in the native application to this database.

Maybe the problem is in some special index properties which makes
unpossible processing by ODBC driver? Maybe driver bug?

It would be very nice if someone uncovers the workaround.

Which ODBC driver are you using? You must be able to turn on tracing
and find the EXACT statement that is being passed to BTrieve.

Normally you'll find that something crazy has happened in the mapping.

plimouthrock@mail.ru wrote:
> Btrieve itself uses this index, as I can conclude running test
> operation in the native application to this database.
>
> Maybe the problem is in some special index properties which makes
> unpossible processing by ODBC driver? Maybe driver bug?
>
> It would be very nice if someone uncovers the workaround.

Which ODBC driver are you using? You must be able to turn on tracing
and find the EXACT statement that is being passed to BTrieve.

Normally you'll find that something crazy has happened in the mapping.

plimouthrock@mail.ru wrote:
> Btrieve itself uses this index, as I can conclude running test
> operation in the native application to this database.
>
> Maybe the problem is in some special index properties which makes
> unpossible processing by ODBC driver? Maybe driver bug?
>
> It would be very nice if someone uncovers the workaround.