Re: Index use on data query from BTrieve through HS ODBC

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_at_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_at_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_at_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.
Received on Sun Oct 08 2006 - 05:45:23 CDT