Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 03 Nov 2007 13:26:20 -0700
Message-ID: <1194121577.959656@bubbleator.drizzle.com>

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 | 103 (1)| 00:00:02 |* 1 | TABLE ACCESS FULL| T | 298 | 8642 | 103 (1)| 00:00:02

---

drop table t purge;

CREATE TABLE t (
doc_id VARCHAR2(10),
person_id NUMBER(7),
doc_name VARCHAR2(45),
PRIMARY KEY (person_id, doc_id))
ORGANIZATION INDEX; 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.

-- 
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 Sat Nov 03 2007 - 15:26:20 CDT