| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL tuning / optimization problem - IS NOT NULL - Clarify
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
------- ------ -------- ---------- ---------- ---------- ----------
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
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: Cherie_Machler_at_gelco.com 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: ListGuru_at_fatcity.com (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: Alex.Hillman_at_usmint.treas.gov 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: ListGuru_at_fatcity.com (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).Received on Wed Aug 08 2001 - 13:39:15 CDT
 |
 |