Re: Some indexes not used in JDBC call
- From: Juha Laiho <Juha.Laiho@xxxxxx>
- Date: Mon, 26 Feb 2007 16:02:03 GMT
samuelrollins@xxxxxxxxx said:
I'm having a problem where a JDBC PreparedStatement without bind
parameters can take more than a minute to execute a query that takes
less than a second to execute in SQL*Plus. The query is identical, the
database instance is the same, neither query is cached, and the query
returns only 18 records with 11 columns all of which are either
VARCHAR2 or NUMBER. I'm using Oracle's JDBC 2.0 drivers
(classes12.jar) and Oracle 8i (Release 8.1.7.4.0) database. Oracle DB
is set to use the cost-based optimizer.
I did an explain plan in SQL*Plus and via JDBC. It turns out that some
of the unique indexes that are used when executing the query in
SQL*Plus are not used when executing via JDBC.
Does anyone know why this would happen?
It might be better if you took this to an Oracle-related group, or to
Oracle forums, but one guess, anyway.
With SQL*Plus I expect that you create a complete query with fixed values
whereas with JDBC you create the query with placeholders for values.
This affects Oracle optimizer somewhat, and can make Oracle leave out
any indexes regarding the fields compared with placeholders.
--
Wolf a.k.a. Juha Laiho Espoo, Finland
(GC 3.0) GIT d- s+: a C++ ULSH++++$ P++@ L+++ E- W+$@ N++ !K w !O !M V
PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
"...cancel my subscription to the resurrection!" (Jim Morrison)
.
- References:
- Some indexes not used in JDBC call
- From: samuelrollins
- Some indexes not used in JDBC call
- Prev by Date: Re: SQL that performs division
- Next by Date: Aggregate function error with Access database
- Previous by thread: Some indexes not used in JDBC call
- Next by thread: Re: Some indexes not used in JDBC call
- Index(es):
Relevant Pages
|
