Re: Strange cost difference
Date: Sat, 19 Feb 2011 15:05:43 +1100
Message-ID: <ijnfif$no7$1_at_news.eternal-september.org>
Mladen Gogala wrote,on my timestamp of 19/02/2011 1:30 PM:
> On Fri, 18 Feb 2011 09:30:16 -0800, joel garry wrote:
>
>> Maybe I'm not understanding something, but isn't the idea for Oracle to
>> figure out the best plan over time for each statement?
>
> Actually no. Oracle 11 will collect statistics and replace the baseline
> in the SYSMAN tablespaces if and only if the plan costs less than the
> existing one. Oracle is not Watson, doesn't play Jeopardy with our SQL,
> it simply saves the plans and replaces them with cheaper plans.
Looks good, but I think the fear there was that the plan will change to a lesser cost one which will result in a longer execution. Happened before, will happen again, plenty of examples of such.
> Although, speaking of Watson and intelligence, I predict that we will > soon see multi-TB NVRAM devices, and the face of the computing will > definitely change. Among other things, B*Trees may become obsolete.
Well, one of the enhancements we did when we started to use partitioning in our DW was to lose quite a few unnecessary indexes. In one case we dropped 6 indexes! Turned out we got nearly an order of magnitude faster in *all* statements involving those tables by just partition pruning and keeping the size of each partition quite small and the PK as a local index.
I've long been a great fan of catalogs as opposed to searching with/without indexes. Way back when, large libraries never indexed books: they just put each book in a catalog category. All you had to do was linearly search one shelf of that category to find what you wanted. After consulting the catalog. The interesting thing of course is that the catalog itself used indexing for the searches.
I think that's what will happen: you'll see two-tier location strategies in future. The first using indexes, the next using a catalog "shelf", pointed to by the index.
And what's best: (re!)building the index will not be a legacy task that only your grand-children will see the end of. (gd&r, vvf) Received on Fri Feb 18 2011 - 22:05:43 CST