Oracle Database Discussions

Hi Experts,

I am on Oracle 11.2.0.2 on Solaris 10. I have an issue with one query that does a full table scan in a table that has 62k rows in development environment, and about 220k rows in FIT/QA environment. The query has a NOT EXISTS clause. I will be thankful on any pointers/suggestions on what can be done to tune the query. The table definition ddl, indexes etc, the query and the query plan are as follows:

CREATE TABLE TEST_CLIENT_APP
(
  APP_ID         VARCHAR2(50 BYTE)              NOT NULL,
  CLNT_OID       VARCHAR2(16 BYTE)              NOT NULL,
  CREATED_BY     VARCHAR2(80 BYTE),
  CREATED_DATE   DATE,
  MODIFIED_BY    VARCHAR2(80 BYTE),
  MODIFIED_DATE  DATE
);


CREATE TABLE TEST_MSG_USER
(
  MESSAGE_USER_ID           NUMBER(12)          NOT NULL,
  MESSAGE_INSTANCE_ID       NUMBER(12)          NOT NULL,
  CLNT_OID                  VARCHAR2(16 BYTE)   NOT NULL,
  PROCESS_STEP_ID           NUMBER(12),
  USER_OID                  VARCHAR2(16 BYTE)   NOT NULL,
  MESSAGE_STATUS            VARCHAR2(3 BYTE)    NOT NULL,
  SEND_REMINDER_DATE        DATE,
  SEND_REMINDER_COUNT       NUMBER(2),
  HIDE_DETAILS_LINK         NUMBER(1),
  ARCHIVED                  NUMBER(1)           NOT NULL,
  READ                      NUMBER(1)           NOT NULL,
  START_DATE                DATE                NOT NULL,
  END_DATE                  DATE,
  MESSAGE_TYPE_NAME         VARCHAR2(80 BYTE)   NOT NULL,
  BASE_TYPE                 CHAR(3 CHAR)        NOT NULL,
  GROUP_OID                 NUMBER(12),
  DUE_DATE                  DATE,
  REMINDER_TYPE             NUMBER(1),
  IS_DUE_DATE_SYS_ASSIGNED  NUMBER(1),
  TRACKED                   NUMBER(1)           NOT NULL
);


CREATE TABLE TEST_PROPERTIES
(
  PROPERTY_NAME           VARCHAR2(30 BYTE)     NOT NULL,
  CLIENT_OID              VARCHAR2(16 BYTE)     NOT NULL,
  PROPERTY_VALUE          VARCHAR2(1024 BYTE),
  APP_ID                  VARCHAR2(50 BYTE)     NOT NULL,
  PROPERTY_VALUE_LANG_ID  NUMBER(12)
);


CREATE UNIQUE INDEX PK_TEST_CLIENT_APP ON TEST_CLIENT_APP
(APP_ID, CLNT_OID);


CREATE UNIQUE INDEX PK_TEST_PROPERTIES ON TEST_PROPERTIES
(PROPERTY_NAME, CLIENT_OID);


CREATE INDEX TEST_MSG_USER_IDX01 ON TEST_MSG_USER
(USER_OID, MESSAGE_STATUS, CLNT_OID);


CREATE INDEX TEST_MSG_USER_IDX02 ON TEST_MSG_USER
(SEND_REMINDER_DATE);


CREATE INDEX TEST_MSG_USER_IDX03 ON TEST_MSG_USER
(PROCESS_STEP_ID);


CREATE INDEX TEST_MSG_USER_IDX04 ON TEST_MSG_USER
(CLNT_OID);


CREATE INDEX TEST_MSG_USER_IDX05 ON TEST_MSG_USER
(MESSAGE_TYPE_NAME);


CREATE INDEX TEST_MSG_USER_IDX06 ON TEST_MSG_USER
(MESSAGE_INSTANCE_ID);


CREATE INDEX TEST_MSG_USER_IDX07 ON TEST_MSG_USER
(CLNT_OID, USER_OID, MESSAGE_INSTANCE_ID);


ALTER TABLE TEST_CLIENT_APP ADD (
  CONSTRAINT PK_TEST_CLIENT_APP
  PRIMARY KEY
  (APP_ID, CLNT_OID)
  USING INDEX PK_TEST_CLIENT_APP
  ENABLE VALIDATE);


ALTER TABLE TEST_PROPERTIES ADD (
  CONSTRAINT PK_TEST_PROPERTIES
  PRIMARY KEY
  (PROPERTY_NAME, CLIENT_OID)
  USING INDEX PK_TEST_PROPERTIES
  ENABLE VALIDATE);

--following are the count of rows in these three tables:
SQL> select count(*) from test_msg_user;

  COUNT(*)
----------
     62701

SQL> select count(*) from test_properties;

  COUNT(*)
----------
         8

SQL> select count(*) from test_client_app;

  COUNT(*)
----------
       174


--the sql query is as follows:
variable SYS_B_1 VARCHAR2(30);
variable SYS_B_2 NUMBER;
variable SYS_B_3 VARCHAR2(30);
variable SYS_B_4 VARCHAR2(30);

											
exec :SYS_B_1:='COM' ;
exec :SYS_B_2:=180;
exec :SYS_B_3:='mcEnhanced';
exec :SYS_B_4:='true';


SELECT TMU.MESSAGE_USER_ID
  FROM    TEST_MSG_USER TMU
        WHERE TMU.ARCHIVED = 0 AND TMU.MESSAGE_STATUS = :SYS_B_1
       AND NOT EXISTS
                  (SELECT 1
                     FROM TEST_PROPERTIES tp, TEST_CLIENT_APP tca
                    WHERE     tp.app_id = tca.app_id
                          AND tp.property_name = :SYS_B_3
                          AND tp.property_value = :SYS_B_4
                          AND tca.CLNT_OID = TMU.CLNT_OID);

--Note the query returns 251 rows as output.
The query plan:
Plan hash value: 2291474835

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |       |       |   254 (100)|          |
|*  1 |  HASH JOIN RIGHT ANTI          |                    | 10223 |   409K|   254   (1)| 00:00:04 |
|   2 |   VIEW                         | VW_SQ_1            |     1 |    10 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                    |     1 |    59 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| TEST_PROPERTIES    |     1 |    33 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | PK_TEST_PROPERTIES |     1 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  6 |     INDEX RANGE SCAN           | PK_TEST_CLIENT_APP |     7 |   182 |     1   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL            | TEST_MSG_USER      | 10450 |   316K|   251   (1)| 00:00:04 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_1"="TMU"."CLNT_OID")
   4 - filter("TP"."PROPERTY_VALUE"=:SYS_B_4)
   5 - access("TP"."PROPERTY_NAME"=:SYS_B_3)
   6 - access("TP"."APP_ID"="TCA"."APP_ID")
   7 - filter(("TMU"."MESSAGE_STATUS"=:SYS_B_1 AND "TMU"."ARCHIVED"=:B0))


34 rows selected.

The issue is the full table scan on TEST_MSG_USER. Is there any way to avoid it?

Thanks,
OrauserN

Edited by: orausern on Feb 12, 2013 10:57 AM