Hi all,
I have some problem. I have created a select statement that will get data from two databases combinied via datase link.
The statement will get some data of tables from the first database and join many data from tables of the second database. The time cost of this statement was heavy. So I have used the orcale hint "DRIVING_SITE":
SELECT /*+ DRIVING_SITE(Table) */
...
When firing the statement in Toad for Oracle it will get the data very fast.
When firing the statment via dotnetConnect for Oracle in a .NET application, I will get an exception:
"ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP\nORA-02063: ..."
How can I solve the problem?
regards,
Ralf
using oracle hints problem
Could you please localize the problem and send us a small test project and the DDL and DML script? We cannot reproduce the problem at the moment. Please specify your dotConnect for Oracle and Oracle server versions when filling our contact form at http://www.devart.com/company/contact.html .
-
seepex_Ralf
- Posts: 2
- Joined: Mon 06 Jul 2009 07:13
Hello Shalex,
thank you for reply.
I will ask our database admin to get the requested information. It will come via contact form soon.
We are using dotConnect version 5.20.29.0 in direct mode.
We have two databases, one 9.1 (db1) and one 10g (db2).
We are connecting only to the 10g database. the 9.1 database will be accessed via a databse link. The most data is stored in the 9.1 database.
We are using the 10g database because of unicode support and migration to.
an example statement ooks like following:
select /*+ DRIVING_SITE(customers_db1) */
offerstatus_db2.status,
offerstatus_db2.nextfollowup,
offers_db1.offerno,
offers_db1.product,
customers_db1.company
.... more fields
from offerstatus_db2
inner join offers_db1 on offers_db1.offerno = offerstatus_db2.offerno
inner join customers_db1 on customer_db1.customerno = offers_db1.customerno
... more joins (inner and left outer)
where (offerstatus_db2.status between x and y)
and (offers_db1.offer_type = 'X')
and (customers_db1.salesregion in ('a', 'b', 'c')
.... more terms
The customer table is sized over more then 100000 entries.
The offerstatus_db2 and offers_db1 tables are sized over more then 200000 entries each table.
Hope this will help for understanding.
regards,
Ralf
thank you for reply.
I will ask our database admin to get the requested information. It will come via contact form soon.
We are using dotConnect version 5.20.29.0 in direct mode.
We have two databases, one 9.1 (db1) and one 10g (db2).
We are connecting only to the 10g database. the 9.1 database will be accessed via a databse link. The most data is stored in the 9.1 database.
We are using the 10g database because of unicode support and migration to.
an example statement ooks like following:
select /*+ DRIVING_SITE(customers_db1) */
offerstatus_db2.status,
offerstatus_db2.nextfollowup,
offers_db1.offerno,
offers_db1.product,
customers_db1.company
.... more fields
from offerstatus_db2
inner join offers_db1 on offers_db1.offerno = offerstatus_db2.offerno
inner join customers_db1 on customer_db1.customerno = offers_db1.customerno
... more joins (inner and left outer)
where (offerstatus_db2.status between x and y)
and (offers_db1.offer_type = 'X')
and (customers_db1.salesregion in ('a', 'b', 'c')
.... more terms
The customer table is sized over more then 100000 entries.
The offerstatus_db2 and offers_db1 tables are sized over more then 200000 entries each table.
Hope this will help for understanding.
regards,
Ralf