Re: why administrator refuse to give permission on PLUSTRACE

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

Hasta wrote:
> In article <MPG.21978c87477ba71298969d_at_news.dommel.be>, hasta_l3
> @hotmail.com says...

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

>
> BTW, could we know the cost you get on your system
> with the index solution, Daniel ?
>

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

I am running nothing but 11g in my lab so my numbers will be different but here it is:

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 |

---

-- 
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:01:54 CST