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

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Wed, 25 Apr 2012 12:34:40 -0500
Message-ID: <C5533BD628A9524496D63801704AE56D75B2491433_at_SPOBMEXC14.adprod.directory>

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

Received on Wed Apr 25 2012 - 12:34:40 CDT