Hasta wrote:
> In article <1194121577.959656_at_bubbleator.drizzle.com>,
> damorgan_at_psoug.org says...
>> Hasta 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));
>>>>
>>>> CREATE INDEX ix_t_person_id
>>>> ON t(person_id);
>>> The main problem is : this table lacks a primary key
>> A primary key has nothing to do with performance. A constraint only
>> affects data integrity. The index, above, does everything that would
>> or could be done by creating a PK.
>>
>>>> That's was the set up. Here's the query identified as the problem.
>>>> SELECT doc_name
>>>> FROM t
>>>> WHERE person_id = 221;
>>>>
>>>> Hint: You can get the cost to 1 with CPU = 0.
>>> I get cost 2 with an index on (person_id, doc_name)
>>>
>>> If more is really needed, I would dig out tom kyte's books
>>> and consider playing with the index, using another table
>>> organization, etc...
>>>
>>> Calmly and very carefully.
>> Not every table should be a heap table. This one shouldn't be
>> if you want the most efficient query.
>>
>> CREATE TABLE t (
>> doc_id VARCHAR2(10),
>> person_id NUMBER(7),
>> doc_name VARCHAR2(45));
>>
>> CREATE INDEX ix_t_person_id
>> ON t(person_id);
>>
>> CREATE SEQUENCE seq_t;
>>
>> DECLARE
>> t_docname dbms_sql.VARCHAR2_TABLE;
>>
>> CURSOR c IS
>> SELECT object_name
>> FROM dba_objects;
>> BEGIN
>> OPEN c;
>> LOOP
>> FETCH c BULK COLLECT INTO t_docname LIMIT 250;
>>
>> FORALL i IN 1..t_docname.COUNT
>> INSERT INTO t
>> (doc_id, person_id, doc_name)
>> VALUES
>> (seq_t.NEXTVAL, MOD(seq_t.CURRVAL, 233), t_docname(i));
>>
>> EXIT WHEN c%NOTFOUND;
>> END LOOP;
>> COMMIT;
>> CLOSE c;
>> END;
>> /
>>
>> exec dbms_stats.gather_table_stats(USER, 'T', CASCADE=>TRUE);
>>
>> EXPLAIN PLAN FOR
>> SELECT doc_name
>> FROM t
>> WHERE person_id = 221;
>>
>> SELECT * FROM TABLE(dbms_xplan.display);
>>
>> --------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost
>> %CPU)| Time |
>> --------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 298 | 8642 | 1
>> (0)| 00:00:01 |
>> |* 1 | INDEX RANGE SCAN| SYS_IOT_TOP_73394 | 298 | 8642 | 1
>> (0)| 00:00:01 |
>> --------------------------------------------------------------------
>>
>> Now give this challenge to all of the developers you know and see how
>> many get it? From my experience it won't be 5 in 100.
>
> Looks like a fair figure. And ? What is your point, Daniel ?
>
> Supplementing Peter's answer, I get cost 3 with the IOT above.
> (recall: cost 2 with an index)
>
> Both may be pretty good or pretty bad. We dont know, since we
> are not given a spec of desired response time nor an upper
> bound on the number of rows.
My point was that while the developers here seemed more than willing to
point fingers at incompetent DBAs from my experience the developers are
equally guilty of not keeping their skills current. Not more so ...
equally so.
--
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 - 14:00:02 CST