Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db

From: Carlos Sierra <carlos.sierra.usa_at_gmail.com>
Date: Wed, 25 Apr 2012 14:26:49 -0400
Message-ID: <CAGzKQQe9D_-q7FOdFUXammV7hn_ciOL+R1RH69_mFVBWz9YoEg_at_mail.gmail.com>

Chris,
If you try SQLTXPLAIN (SQLT) MOS 215187.1 please use SQLT XTRACT in both and send me output zip files. I can do a quick review. Or you can also use SQLT COMPARE from both. There are so many things to compare manually. An automated mechanism reduces the human effort and error.

Cheers -- Carlos

On Wed, Apr 25, 2012 at 1:34 PM, Taylor, Chris David < ChrisDavid.Taylor_at_ingrambarge.com> wrote:

> Oracle will often favor a full tablescan when the data you want is spread
> out across multiple Oracle blocks - so say the table only has 50 Oracle
> blocks, and your data occupies 45 of them then it's 'cheaper' to do a FTS
> since it has to read most of the Oracle blocks anyway.
>
> That's the point I was trying to find floating around in my head...
>
>
> Chris
>
> -----Original Message-----
> From: Taylor, Chris David
> Sent: Wednesday, April 25, 2012 12:09 PM
> To: 'Li Li'
> Cc: 'oracle-l_at_freelists.org'
> Subject: RE: query uses function based index in DEV1 db but does NOT use
> it in DEV2 db
>
> <snip>
> There are times where if you've asked for some percentage of a table's
> rows, but because that percentage is spread out across the whole table,
> that Oracle will favor a FTS even when asked for a small percentage of the
> table's rows. (I was trying to find an example of this but can't put my
> fingers on it at the moment...)
> </snip>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 25 2012 - 13:26:49 CDT