Re: why administrator refuse to give permission on PLUSTRACE

From: Peter J. Holzer <hjp-usenet2_at_hjp.at>
Date: Sun, 4 Nov 2007 23:31:43 +0100
Message-ID: <slrnfisi2f.j4u.hjp-usenet2@zeno.hjp.at>

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.

-- 
   _  | Peter J. Holzer    | It took a genius to create [TeX],
|_|_) | Sysadmin WSR       | and it takes a genius to maintain it.
| |   | hjp_at_hjp.at         | That's not engineering, that's art.
__/   | http://www.hjp.at/ |	-- David Kastrup in comp.text.tex

Received on Sun Nov 04 2007 - 16:31:43 CST