Re: why administrator refuse to give permission on PLUSTRACE

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.

> 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

-- 
   _  | 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