Oracle FAQ Your Portal to the Oracle Knowledge Grid

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 <>
Date: Sat, 3 Nov 2007 23:54:05 +0100
Message-ID: <>

On 2007-11-03 20:20, DA Morgan <> wrote:
> Peter J. Holzer wrote:
>> On 2007-11-03 14:09, DA Morgan <> wrote:
>>> And just because I'm in the mood here's another one I give my students.
>>> Here's the setup.
>>> 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.

The column names doc_id and doc_name suggest that there's functional dependency between them. Since there are no constraints on the table we can only guess what the primary key is: If it is either doc_id or doc_name, then the table is in 2nd NF. But if it is the combination of either with person_id, then it isn't. The name "t" isn't descriptive enough to tell which is the case, and real-world experience tells me that there are often several persons associated with a single document.

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

I did try an index organized table with key compression (in fact I tried that before the extra index) and I got exactly the same execution plan (well, the name of the index was different :-)) at the same cost as with the extra index on t(person_id, doc_name). If I use your solution (without key compression, the cost is 5.

(Actually, I don't think there's much difference whether the cost is 1 or 3 or 5 - that obviously depends on the amount of data in the table and a number of other factors. My posting was intended as a gentle reminder that "the cost is N" isn't very useful unless you know the data and the system.)


   _  | Peter J. Holzer    | It took a genius to create [TeX],
|_|_) | Sysadmin WSR       | and it takes a genius to maintain it.
| |   |         | That's not engineering, that's art.
__/   | |	-- David Kastrup in comp.text.tex
Received on Sat Nov 03 2007 - 17:54:05 CDT

Original text of this message