Questions on PLAN

From: Anon <anon_at_anon.com>
Date: Sat, 19 Feb 2005 10:41:46 -0000
Message-Id: <1108809689.31229.0@doris.uk.clara.net>

Hi, owning up I have been using Oracle for over 3 years as a middleware developer regularly using an Oracle database and I have never really understood the Oracle Plan table. Despite much effort over the past few days, I am still not sure I do and would like to ask the experts in this newsgroup to clear something up for me.

I have created a dummy script below to try to rationalise things to myself. It produces two PLANs. For the purposes of discussion here I will call them 1 and 2.

In PLAN "1" there is the line " TABLE ACCESS (FULL) T1" for the table (T1) that has the largest number of rows. To me this is the bottleneck in processing the query because it has to process everything in T1 start to finish. T1 is by far the biggest table.

In PLAN "2", a seemingly identical query but where the tables being queried have been swapped, there is no full table scan and an index is being used. Here the line reads "INDEX (FULL SCAN) IDXT3".

Wouldn't query that produces PLAN "1" be more efficient if it used the index? And if this is the case, why isn't it? And why is the query that produces PLAN2 using an index when to me this would be a candidate where a full table scan might be quicker than an index given there are only a handful or rows.

Your comments would be most appreciated.

Thank you
Pete

C:\SQLPLUS SCOTT/TIGER_at_TEST10G SQL*Plus: Release 9.0.1.4.0 - Production on Sat Feb 19 10:21:34 2005

Connected to:
Personal Oracle Database 10g Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL>
SQL> CREATE TABLE t1(a NUMBER);

Table created.

SQL> CREATE TABLE t2(b NUMBER);

Table created.

SQL> CREATE TABLE t3(c NUMBER);

Table created.

SQL>
SQL> DECLARE
  2 BEGIN
  3 DBMS_RANDOM.INITIALIZE(TO_NUMBER(TO_CHAR(SYSDATE,'ss')));   4
  5 INSERT INTO t1

  6     SELECT DBMS_RANDOM.RANDOM()
  7      FROM DUAL
  8       CONNECT BY ROWNUM<=1000000;

9
10 INSERT INTO t2

 11     SELECT DBMS_RANDOM.RANDOM()
 12      FROM DUAL
 13       CONNECT BY ROWNUM<=100;

14
15 INSERT INTO t3

 16     SELECT a
 17      FROM t1
 18       WHERE ROWNUM<=10;

19
20 INSERT INTO t3

 21     SELECT b
 22      FROM t2
 23       WHERE ROWNUM<=10;

24
25
26 DBMS_RANDOM.TERMINATE;
27 END;
28 /

PL/SQL procedure successfully completed.

SQL>
SQL> CREATE INDEX idxT1 ON t1(a);

Index created.

SQL> CREATE INDEX idxT2 ON t2(b);

Index created.

SQL> CREATE INDEX idxT3 ON t3(c);

Index created.

SQL>
SQL>
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> TRUNCATE TABLE plan_table;

Table truncated.

SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM t1
3 WHERE a IN (SELECT b z FROM t2 UNION SELECT c z FROM t3);

Explained.

SQL>
SQL> COLUMN "Operation" FORMAT A30
SQL> COLUMN "Object" FORMAT A30
SQL> SET LINESIZE 80
SQL>
SQL> SELECT SUBSTR(LPAD(' ',LEVEL-1) || operation || ' (' || options || 


')',1,30 ) "Operation",

  2         object_name "Object"

  3 FROM plan_table
  4 START WITH id=0
  5 CONNECT BY PRIOR id=parent_id;

Operation                      Object
------------------------------ ------------------------------

SELECT STATEMENT ()
SORT (AGGREGATE)
NESTED LOOPS ()

   TABLE ACCESS (FULL)         T1
   VIEW ()                     VW_NSO_1
    SORT (UNIQUE)
     UNION-ALL (PARTITION)
      INDEX (RANGE SCAN)       IDXT2
      INDEX (RANGE SCAN)       IDXT3

9 rows selected.

SQL>
SQL> TRUNCATE TABLE plan_table;

Table truncated.

SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM t3
3 WHERE c IN (SELECT a z FROM t1 UNION SELECT b z FROM t2);

Explained.

SQL>
SQL> SELECT SUBSTR(LPAD(' ',LEVEL-1) || operation || ' (' || options ||
')',1,30 ) "Operation",

  2 object_name "Object"
  3 FROM plan_table
  4 START WITH id=0
  5 CONNECT BY PRIOR id=parent_id;

Operation                      Object
------------------------------ ------------------------------

SELECT STATEMENT ()
SORT (AGGREGATE)
NESTED LOOPS ()

   INDEX (FULL SCAN)           IDXT3
   VIEW ()                     VW_NSO_1
    SORT (UNIQUE)
     UNION-ALL (PARTITION)
      INDEX (RANGE SCAN)       IDXT1
      INDEX (RANGE SCAN)       IDXT2

9 rows selected.

SQL>
SQL> Received on Sat Feb 19 2005 - 04:41:46 CST

Questions on PLAN_TABLE