Re: stupid question on FTS
From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Fri, 24 Aug 2012 11:01:23 +0800
Message-ID: <CAMNBsZs1sfdQHXketsBAE+wQk6hd4u30=kQPgafZ+PhJu1+k6A_at_mail.gmail.com>
It can make sense to do a FTS if the Optimizer thinks that the query submitted will fetch a very large number of rows OR if it simply can't use any available index for a particularly query. You'd have to look at the Table definition, available indexes and the queries being submitted. Evaluate the execution plans for the queries against this table. See if the optimizer estimate on the number of rows ("cardinality") it will fetch from the table is correct or wildly off the mark where an index could be used and much fewer rows need to be fetched.
Date: Fri, 24 Aug 2012 11:01:23 +0800
Message-ID: <CAMNBsZs1sfdQHXketsBAE+wQk6hd4u30=kQPgafZ+PhJu1+k6A_at_mail.gmail.com>
It can make sense to do a FTS if the Optimizer thinks that the query submitted will fetch a very large number of rows OR if it simply can't use any available index for a particularly query. You'd have to look at the Table definition, available indexes and the queries being submitted. Evaluate the execution plans for the queries against this table. See if the optimizer estimate on the number of rows ("cardinality") it will fetch from the table is correct or wildly off the mark where an index could be used and much fewer rows need to be fetched.
Hemant K Chitale
On Fri, Aug 24, 2012 at 8:34 AM, Vasu <vasudevanr_at_gmail.com> wrote:
> I have a huge table in my OLTP DB, that has 100 million+ rows (pls don't
> ask why), and is partitioned.
> I know it doesn't make sense for my App to ever do a FTS on it.. But Oracle
> at times picks up FTS as the best access path (and our STATS is not at it
> best yet).
>
> It just wished for a setting..that "I never want to have a FTS on my
> table..Unless otherwise explicitly told thru a Hint" .
>
> yes, a Hint/setting can't compensate for lack of STATS.. but just that my
> desparate situation/laziness forced me to think that way.
>
> Any thoughts?
>
> Thanks,
> Vasu
>
>
Hemant K Chitale
http://hemantoracledba.blogspot.com
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 23 2012 - 22:01:23 CDT