Re: Yet another "why is my index not used" question
Date: Mon, 26 Jan 2009 23:40:23 -0600
Message-ID: <9axfl.9643$pr6.1967_at_flpi149.ffdc.sbc.com>
Radoulov, Dimitre wrote:
>
> Hi all,
>
> our environment: 9i EE 9.2.0.4 64bit, Solaris 8
>
> We're trying to understand why the CBO picks suboptimal
> single partition scan, when there is a more efficient
> index range scan available.
>
> Some details (table name modified):
> segment statistics are gathered with the following command:
>
Probably has something to do with the fact you are using a version that is so far out of support, it is not even funny. There have been MANY improvements in the CBO since 9204. (BTW 9208 was the terminal release and it is not longer supported.) Not even your OS is supported. Your Index may not be as "efficient" as you think it is.
The trick to making the optimizer use the appropriate index depends entirely on the where clause. Having a query that has all of the values "is not null" is asking for a FTS. You need to remember that they are called "hints" for a reason. Sometimes it decides a FTS is much better, ESPECIALLY when using this very poorly crafted query:
12 where
13 "VMENMAG" is not null
14 or "VMEMOIS" is not null 15 or "VMENIV1" is not null 16 or "VMENIV2" is not null 17 or "VMENIV3" is not null
18 ;
Again, You have not given the optimizer anything work with therefore you get a FTS.
"If it ain't important enough to keep it current, it ain't important." Received on Mon Jan 26 2009 - 23:40:23 CST