Peter J. Holzer wrote:
> On 2007-11-04 19:54, DA Morgan <damorgan_at_psoug.org> wrote:
>> Peter J. Holzer wrote:
>>> On 2007-11-03 20:20, DA Morgan <damorgan_at_psoug.org> wrote:
>>>> Peter J. Holzer wrote:
>>>>> On 2007-11-03 14:09, DA Morgan <damorgan_at_psoug.org> wrote:
>>>>>> SELECT doc_name
>>>>>> FROM t
>>>>>> WHERE person_id = 221;
>>>>>>
>>>>>> Hint: You can get the cost to 1 with CPU = 0.
>>>>> I admit defeat on that one. On the system I tested this it does a full
>>>>> table scan with COST=75 and CPU_COST=2029329. I can easily get the cost
>>>>> down to 3 and CPU_COST to 64364 by slapping an index on
>>>>> t(person_id, doc_name).
>>>>> But beyond that I run out of ideas.
>>>> See Hasta's response.
>>> Hasta got a cost of 2, not 1. Do you mean your response to Hasta?
>> If the table is an IOT on all of my systems, granted they are all Oracle
>> 11g the cost is 1. Given the alterations of the CBO with every release
>> your mileage may vary.
>
> It's 10.2 in my case.
>
>> Did you consider trying either a single table hash cluster or a sorted
>> hash cluster? I'd like to know how those turn out for you. If you need
>> the syntax you will find it here:
>> http://www.psoug.org/reference/clusters.html
>
> You mean like this?
>
> CREATE cluster cl_t (
> person_id NUMBER(7)
> )
> single table
> hashkeys 16
> hash is (mod(person_id, 16))
>
> create table t (
> doc_id VARCHAR2(10),
> person_id NUMBER(7),
> doc_name VARCHAR2(45)
> )
> cluster cl_t (person_id)
>
> No, I didn't think of that.
>
> Ok, I think it is now time to write a script which exercises all the
> possibilities I have thought of. See
> http://www.hjp.at/databases/oracle_index_comparison_results.pl and
> http://www.hjp.at/databases/oracle_index_comparison_results.txt for the
> results on my system.
>
> The cost reported by explain plan is 0 for the single table hash cluster
> which can't be beaten. But in terms of elapsed time the IOT and the
> index on (person_id, doc_name) seem to be about 10% faster, although it's
> hard to be sure since the times are so small - you would have to try
> that with a more realistic sample.
>
> hp
I like sorted hash clusters. They are not the generic solution to every
problem any more than penicillin cures all illnesses. But they
definitely have their place and should be considered more often than
they are.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Nov 04 2007 - 22:05:12 CST
