Re: why administrator refuse to give permission on PLUSTRACE

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

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

        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