Create index containing 3 columns in the view. After that this query will
read only index and not table - fast full index scan - should be much
faster - especially if you are on 8.1.6 or higher and can use compress -
CASE_WIP2WIPBIN is a very good candidate. I would created index like -
create index xxx on table table_case (CASE_WIP2WIPBIN, CASE_REPORTER2SITE,
objid)
compress 1 (if you are on 8.1.6 or higher)
Alex Hillman
-----Original Message-----
Sent: Wednesday, August 08, 2001 2:48 PM
To: Multiple recipients of list ORACLE-L
I have been struggling with a SQL statement that is generated by a
help desk application called Clarify. The code is all canned so I
can't change it (well maybe the view if that's the only way). So far
I have been tuning this application by adding indexes, histograms,
etc. However, I've hit the wall with the following SQL statement.
I've been messing around with adding indexes to the table to no
avail. The best I've been able to get it to do is a full index scan.
The situation is complicated by the bind variable, the existence
of the view, and the IS NOT NULL clause which I haven't tuned before
(and haven't been able to find much tuning documentation on).
Version is 8.0.4 and Sun Solaris 2.6. CASE table has about 115,000 rows
in it.
>From tkprof output file:
select wip_objid, elm_objid, site_objid
from table_site2case_view
WHERE ( site_objid = :B1 )
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.01 0.03 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 1 3.18 13.98 21184 21960 5
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 3.19 14.01 21184 21960 5
0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96 (SA)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
114904 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TABLE_CASE'
SQL> select text from dba_views where view_name='TABLE_SITE2CASE_VIEW';
TEXT
----------------------------------------------------------------------------
----
select table_case.case_wip2wipbin, table_case.objid,
table_case.case_reporter2site
from table_case
where table_case.case_wip2wipbin IS NOT NULL
AND table_case.case_reporter2site IS NOT NULL
COLUMN_NAME NUM_NULLS NUM_DISTINCT
------------------------------ ---------- ------------
OBJID 0 114450
CASE_REPORTER2SITE 0 1418
CASE_WIP2WIPBIN 113615 88
SQL> desc table_case
Name Null? Type
------------------------------- -------- ----
------------------------------- -------- ----
OBJID NUMBER
TITLE VARCHAR2(80)
S_TITLE VARCHAR2(80)
ID_NUMBER VARCHAR2(255)
CREATION_TIME DATE
INTERNAL_CASE NUMBER
HANGUP_TIME DATE
ALT_PHONE_NUM VARCHAR2(20)
PHONE_NUM VARCHAR2(20)
PICKUP_EXT VARCHAR2(8)
CASE_HISTORY LONG
TOPICS_TITLE VARCHAR2(255)
YANK_FLAG NUMBER
SERVER_STATUS VARCHAR2(2)
SUPPORT_TYPE VARCHAR2(2)
WARRANTY_FLAG VARCHAR2(2)
SUPPORT_MSG VARCHAR2(80)
ALT_LAST_NAME VARCHAR2(30)
ALT_FAX_NUMBER VARCHAR2(20)
ALT_E_MAIL VARCHAR2(80)
ALT_SITE_NAME VARCHAR2(80)
ALT_ADDRESS VARCHAR2(200)
ALT_CITY VARCHAR2(30)
ALT_STATE VARCHAR2(30)
ALT_ZIPCODE VARCHAR2(20)
FCS_CC_NOTIFY NUMBER
SYMPTOM_CODE VARCHAR2(10)
CURE_CODE VARCHAR2(10)
SITE_TIME DATE
ALT_PROD_SERIAL VARCHAR2(30)
MSG_WAIT_COUNT NUMBER
REPLY_WAIT_COUNT NUMBER
REPLY_STATE NUMBER
OPER_SYSTEM VARCHAR2(20)
CASE_SUP_TYPE VARCHAR2(2)
PAYMENT_METHOD VARCHAR2(30)
REF_NUMBER VARCHAR2(80)
DOA_CHECK_BOX NUMBER
CUSTOMER_SATIS NUMBER
CUSTOMER_CODE VARCHAR2(20)
SERVICE_ID VARCHAR2(30)
ALT_PHONE VARCHAR2(20)
FORWARD_CHECK NUMBER
CCLIST1 VARCHAR2(255)
CCLIST2 VARCHAR2(255)
KEYWORDS VARCHAR2(255)
OWNERSHIP_STMP DATE
MODIFY_STMP DATE
DIST NUMBER
ARCH_IND NUMBER
IS_SUPERCASE NUMBER
DEV NUMBER
CASE_SOLN2WORKAROUND NUMBER(38)
CASE_PREVQ2QUEUE NUMBER(38)
CASE_CURRQ2QUEUE NUMBER(38)
CASE_WIP2WIPBIN NUMBER(38)
CASE_LOGIC2PROG_LOGIC NUMBER(38)
CASE_OWNER2USER NUMBER(38)
CASE_STATE2CONDITION NUMBER(38)
CASE_ORIGINATOR2USER NUMBER(38)
CASE_EMPL2EMPLOYEE NUMBER(38)
CALLTYPE2GBST_ELM NUMBER(38)
RESPPRTY2GBST_ELM NUMBER(38)
RESPSVRTY2GBST_ELM NUMBER(38)
CASE_PROD2SITE_PART NUMBER(38)
CASE_REPORTER2SITE NUMBER(38)
CASE_REPORTER2CONTACT NUMBER(38)
ENTITLEMENT2CONTRACT NUMBER(38)
CASESTS2GBST_ELM NUMBER(38)
CASE_RIP2RIPBIN NUMBER(38)
COVRD_PPI2SITE_PART NUMBER(38)
CASE_DISTR2SITE NUMBER(38)
CASE2ADDRESS NUMBER(38)
CASE_NODE2SITE_PART NUMBER(38)
DE_PRODUCT2SITE_PART NUMBER(38)
CASE_PRT2PART_INFO NUMBER(38)
DE_PRT2PART_INFO NUMBER(38)
ALT_CONTACT2CONTACT NUMBER(38)
TASK2OPPORTUNITY NUMBER(38)
CASE2LIFE_CYCLE NUMBER(38)
CASE_VICTIM2CASE NUMBER(38)
ENTITLE2CONTR_ITM NUMBER(38)
X_DIAGNOSIS VARCHAR2(255)
X_EXPERIENCE NUMBER
X_OS VARCHAR2(30)
X_PROBLEM VARCHAR2(255)
X_RESOLUTION VARCHAR2(255)
X_WORKGROUP VARCHAR2(30)
X_FIRST_CLOSE VARCHAR2(1)
X_FCLOSED NUMBER
X_FCLOSEDATE DATE
Following indexes exist on the TABLE_CASE table:
Index_name Column_name
CASE_OBJINDEX OBJID
IND_ALT_FIRST_NAME ALT_FIRST_NAME
IND_ALT_LAST_NAME ALT_LAST_NAME
IND_CASE_CREATION_TIME CREATION_TIME
CASE_CASE_INDEX ID_NO
IND_CASE_OWNER2USER CASE_OWNER2USER, CASE_WIP2WIPBIN
IND_ENTITLEMENT2CONTRACT ENTITLEMENT2CONTRACT
IND_CASE_REPORTER2CONTACT CASE_REPORTER2CONTACT
IND_CASE_STATE2CONDITION CASE_STATE2CONDITION
IND_CASE_PROD2SITE_PART CASE_PROD2SITE_PART
IND_ENTITLE2CONTR_ITM ENTITLE2CONTR_ITM
INDEX_NAME
------------------------------
IND_CASE_VICTIM2CASE CASE_VICTIM2CASE
IND_S_CASE_TITLE S_TITLE
IND_CASE_PREVQ2QUEUE CASE_PREVQ2QUEUE
IND_CASE_CURRQ2QUEUE CASE_CURRQ2QUEUE
CASE_WIP2WIPBIN_INDEX CASE_WIP2WIPBIN
IND_CASESTS2GBST_ELM CASESTS2GBST_ELM
I hope that's everything that anyone might need to look at this problem.
I'm frustrated after pounding away at it off and on the last couple of
days.
The users are getting frustrated and I've run out of ideas. In another day
or two they are going to revolt and then I'll have my boss breathing down
my neck. So if anyone has any ideas of what I might try, please let me
know.
Thanks,
Cherie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).