Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why administrator refuse to give permission on PLUSTRACE

Re: why administrator refuse to give permission on PLUSTRACE

From: Peter J. Holzer <hjp-usenet2_at_hjp.at>
Date: Sat, 3 Nov 2007 18:05:59 +0100
Message-ID: <slrnfipajn.5fv.hjp-usenet2@zeno.hjp.at>


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
Received on Sat Nov 03 2007 - 12:05:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US