Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 03 Nov 2007 13:20:55 -0700
Message-ID: <1194121252.374655@bubbleator.drizzle.com>

Peter J. Holzer wrote:
> On 2007-11-03 14:09, DA Morgan <damorgan_at_psoug.org> wrote:

>> And just because I'm in the mood here's another one I give my students.
>> Here's the setup.
>>
>> CREATE TABLE t (
>> doc_id    VARCHAR2(10),
>> person_id NUMBER(7),
>> doc_name  VARCHAR2(45));

>
> That table looks denormalized, which may be good for performance, but
> bad for consistency.

Nothing about the table description that indicates any denormalization.

>> CREATE INDEX ix_t_person_id
>> ON t(person_id);
>>

> [fill table with test data and analyze it]
>

>> 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.
>
> hp

See Hasta's response.

-- 
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 Sat Nov 03 2007 - 15:20:55 CDT