Re: why administrator refuse to give permission on PLUSTRACE

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. Received on Sun Nov 04 2007 - 01:00:46 CST