SQL & PL/SQL

Hello,

Here below a test case.
I have a query which take more than 3 second. It base on a view statement which use a distinct.

SQL> set autot traceonly explain stat
SQL> SELECT FILL.USERID,FILL.WORKLISTNAME,FILL.WLRECNAME,FILL.DESCR
     FROM PS_RB_WF_TRANS_VW FILL
     WHERE USERID IN (SELECT B.RB_WF_GRP_NAME FROM PS_RB_WF_GRP_PLIST B WHERE B.USERID = '326923');

Elapsed: 00:00:03.10

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14043 Card=1813 Bytes=346283)
   1    0   NESTED LOOPS (SEMI) (Cost=14043 Card=1813 Bytes=346283)
   2    1     VIEW OF 'PS_RB_WF_TRANS_VW' (Cost=14043 Card=828664 Bytes=136729560)
   3    2       SORT (UNIQUE) (Cost=14043 Card=828664 Bytes=88667048)
   4    3         HASH JOIN (Cost=715 Card=828664 Bytes=88667048)
   5    4           TABLE ACCESS (FULL) OF 'PS_RB_WL_GRID_DFN' (Cost=2 Card=39 Bytes=1950)
   6    4           INDEX (FAST FULL SCAN) OF 'IDX011' (UNIQUE) (Cost=709 Card=828664 Bytes=47233848)
   7    1     INDEX (UNIQUE SCAN) OF 'PS_RB_WF_GRP_PLIST' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      20188  consistent gets
          0  physical reads
          0  redo size
        866  bytes sent via SQL*Net to client
        650  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> set autot off
SQL> select dbms_metadata.get_ddl('VIEW','PS_RB_WF_TRANS_VW',user) from dual;

DBMS_METADATA.GET_DDL('VIEW','PS_RB_WF_TRANS_VW',USER)
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYSADM"."PS_RB_WF_TRANS_VW" ("USERID", "WORKLISTNAME", "WLRECNAME", "DESCR") AS
  SELECT DISTINCT A.OPRID , A.WORKLISTNAME , B.WLRECNAME , B.DESCR
FROM   PSWORKLIST A , PS_RB_WL_GRID_DFN B
WHERE  B.WORKLISTNAME = A.WORKLISTNAME
AND    A.INSTSTATUS <=2
AND    A.BUSPROCNAME <> 'Administer Workflow'

Because of the time is not low enough, I try to tune it. Just to try, I replace the DISTINCT by a GROUP BY on all the selected columns. The time is now less than 0.1 second.

SQL> CREATE OR REPLACE FORCE VIEW "SYSADM"."PS_RB_WF_TRANS_VW" ("USERID", "WORKLISTNAME", "WLRECNAME", "DESCR") AS
  2  SELECT A.OPRID , A.WORKLISTNAME , B.WLRECNAME , B.DESCR
  3  FROM   PSWORKLIST A , PS_RB_WL_GRID_DFN B
  4  WHERE  B.WORKLISTNAME = A.WORKLISTNAME
  5  AND    A.INSTSTATUS <=2
  6  AND    A.BUSPROCNAME <> 'Administer Workflow'
  7  GROUP BY A.OPRID , A.WORKLISTNAME , B.WLRECNAME , B.DESCR;

View created.

Elapsed: 00:00:00.01
SQL> set autot traceonly explain stat
SQL> SELECT FILL.USERID,FILL.WORKLISTNAME,FILL.WLRECNAME,FILL.DESCR
     FROM PS_RB_WF_TRANS_VW FILL
     WHERE USERID IN (SELECT B.RB_WF_GRP_NAME FROM PS_RB_WF_GRP_PLIST B WHERE B.USERID = '326923');

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=1229 Bytes=163457)
   1    0   SORT (GROUP BY) (Cost=64 Card=1229 Bytes=163457)
   2    1     HASH JOIN (Cost=39 Card=1229 Bytes=163457)
   3    2       TABLE ACCESS (FULL) OF 'PS_RB_WL_GRID_DFN' (Cost=2 Card=39 Bytes=1950)
   4    2       NESTED LOOPS (Cost=36 Card=1229 Bytes=102007)
   5    4         SORT (UNIQUE)
   6    5           INDEX (FAST FULL SCAN) OF 'PS_RB_WF_GRP_PLIST' (UNIQUE) (Cost=2 Card=2 Bytes=52)
   7    4         INDEX (RANGE SCAN) OF 'IDX011' (UNIQUE) (Cost=16 Card=604 Bytes=34428)

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        125  consistent gets
          0  physical reads
          0  redo size
        866  bytes sent via SQL*Net to client
        650  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

The explain plan is a quite different and seems better in the second case. How explain that ?

Thanks,

By the way, Oracle is 9.2.0.8 and stats are up-to-date (tables and indexes).
Message was edited by:
user583229