Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 04 Nov 2007 11:54:05 -0800
Message-ID: <1194206042.590270@bubbleator.drizzle.com>

Peter J. Holzer wrote:
> 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.

Please explain this conclusion to me. Perhaps some of my students are, by some strange logic, doing the same thing and I would truly like to understand this. In truth the design should be leading you to consider this table to be an intersecting entity ... possibly between one table with documents ... pk = doc_id and a second table named person ... pk person_id. If that is what you mean by denormalization yes it could be interpreted that way.

But given that it contains the doc_name, which would normally be in the documents table I think that is a leap not necessarily justified. Again I'd like to know how you derived your assumption.

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

Perhaps it is a DW table. <g> Either way the slow query is the slow query and the table is the table for better or worse.

>
>

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

If the table is an IOT on all of my systems, granted they are all Oracle 11g the cost is 1. Given the alterations of the CBO with every release your mileage may vary.

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

I like 11g better. <g>

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

I agree. The only reason I posted my cost was to encourage people to try to be creative an achieve it. If you received a different answer with an IOT then that is probably the best that can be attained.

Did you consider trying either a single table hash cluster or a sorted hash cluster? I'd like to know how those turn out for you. If you need the syntax you will find it here:
http://www.psoug.org/reference/clusters.html

Thanks.

-- 
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 Sun Nov 04 2007 - 13:54:05 CST