Prev: * Tight Teens Getting Plonked
Next: disable all RMAN jobs in Grid Contol
From: HansP on 28 Apr 2010 06:39
Hi,

Perhaps there is someone with knowledge about MSACCESS and ORACLE.
A new application of one of my customers uses MSACCESS and ORACLE with
ODBC between them.

During 10046 level 12 tracing I saw bind variables type float
(oacdty=101).
The referenced columns are type number so a conversion takes place.
This causes the optimizer to ignore the index.

Are there any issues regarding floats and numbers and ODBC to Oracle?

Regards Hans-Peter
From: joel garry on 28 Apr 2010 11:54
On Apr 28, 3:39 am, HansP <Hans-Peter.Sl...(a)atosorigin.com> wrote:
> Hi,
>
> Perhaps there is someone with knowledge about MSACCESS and ORACLE.
> A new application of one of my customers uses MSACCESS and ORACLE with
> ODBC between them.
>
> During 10046 level 12 tracing I saw bind variables type float
> (oacdty=101).
> The referenced columns are type number so a conversion takes place.
> This causes the optimizer to ignore the index.
>
> Are there any issues regarding floats and numbers and ODBC to Oracle?
>
> Regards Hans-Peter

Perhaps. 11.1 handles the mapping differently than earlier versions,
but they are all likely to convert. Which version/patch level of
everything are you using? Check the docs for odbc numeric
conversion. You may be able to make an appropriate index anyways, not
sure which side you are referring to has "type number."

Of course, we could be skeptical of your assertion that this is
causing the optimizer to ignore the index, without further evidence.

jg
--
@home.com is bogus.
http://blog.taragana.com/law/2010/04/28/jurors-in-sarah-palin-e-mail-hacking-case-to-begin-2nd-day-of-deliberations-in-tenn-21747/
From: Mark D Powell on 28 Apr 2010 12:17
On Apr 28, 6:39 am, HansP <Hans-Peter.Sl...(a)atosorigin.com> wrote:
> Hi,
>
> Perhaps there is someone with knowledge about MSACCESS and ORACLE.
> A new application of one of my customers uses MSACCESS and ORACLE with
> ODBC between them.
>
> During 10046 level 12 tracing I saw bind variables type float
> (oacdty=101).
> The referenced columns are type number so a conversion takes place.
> This causes the optimizer to ignore the index.
>
> Are there any issues regarding floats and numbers and ODBC to Oracle?
>
> Regards Hans-Peter

Prior to 11g Oracle stores all numeric data as data type number. If
you created a table in Oracle with a column such as scientific_value
float then interally it was a number data type that was constrained by
Oracle to only handle values that fit in a float.

When passing float data via ODBC/OLE conversion takes place.

What does the explain plan look like?

HTH -- Mark D Powell --
From: HansP on 29 Apr 2010 03:23
On 28 apr, 18:17, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
> On Apr 28, 6:39 am, HansP <Hans-Peter.Sl...(a)atosorigin.com> wrote:
>
> > Hi,
>
> > Perhaps there is someone with knowledge about MSACCESS and ORACLE.
> > A new application of one of my customers uses MSACCESS and ORACLE with
> > ODBC between them.
>
> > During 10046 level 12 tracing I saw bind variables type float
> > (oacdty=101).
> > The referenced columns are type number so a conversion takes place.
> > This causes the optimizer to ignore the index.
>
> > Are there any issues regarding floats and numbers and ODBC to Oracle?
>
> > Regards Hans-Peter
>
> Prior to 11g Oracle stores all numeric data as data type number.  If
> you created a table in Oracle with a column such as scientific_value
> float then interally it was a number data type that was constrained by
> Oracle to only handle values that fit in a float.
>
> When passing float data via ODBC/OLE conversion takes place.
>
> What does the explain plan look like?
>
> HTH -- Mark D Powell --


It seems to have to do with the ODBC driver see;
Full Index Scan in Execution Plan With Access and Double or Float as
Primary Key [ID 403198.1] on Metalink

 | 
Pages: 1
Prev: * Tight Teens Getting Plonked
Next: disable all RMAN jobs in Grid Contol