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 cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 3.18 13.98 21184 21960 5 0 --- -- -- -- -- -- -- total3 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_REPORTER2SITE0 1418 CASE_WIP2WIPBIN113615 88 SQL desc table_case NameNull?Type --- --- OBJIDNUMBER TITLEVARCHAR2(80) S_TITLE VARCHAR2(80) ID_NUMBERVARCHAR2(255) CREATION_TIMEDATE INTERNAL_CASENUMBER HANGUP_TIME DATE ALT_PHONE_NUMVARCHAR2(20) PHONE_NUMVARCHAR2(20) PICKUP_EXT VARCHAR2(8) CASE_HISTORY LONG TOPICS_TITLE VARCHAR2(255) YANK_FLAGNUMBER SERVER_STATUSVARCHAR2(2) SUPPORT_TYPE VARCHAR2(2) WARRANTY_FLAGVARCHAR2(2) SUPPORT_MSG VARCHAR2(80) ALT_LAST_NAMEVARCHAR2(30) ALT_FAX_NUMBER VARCHAR2(20) ALT_E_MAIL VARCHAR2(80) ALT_SITE_NAMEVARCHAR2(80) ALT_ADDRESS VARCHAR2(200) ALT_CITY VARCHAR2(30) ALT_STATEVARCHAR2(30) ALT_ZIPCODE VARCHAR2(20) FCS_CC_NOTIFYNUMBER SYMPTOM_CODE VARCHAR2(10) CURE_CODEVARCHAR2(10) SITE_TIMEDATE ALT_PROD_SERIAL VARCHAR2(30) MSG_WAIT_COUNT NUMBER REPLY_WAIT_COUNT NUMBER REPLY_STATE NUMBER OPER_SYSTEM VARCHAR2(20) CASE_SUP_TYPEVARCHAR2(2) PAYMENT_METHOD VARCHAR2(30) REF_NUMBER
RE: SQL tuning / optimization problem - IS NOT NULL - Clarify
Alex, I had achieved a full index scan before and was disappointed with the test results because it was showing a full scan on the index. However, after I got your email, I turned on timed_statistics and had the user do a real test for me on the test database and the actual times where significantly faster (at least ten-fold). So even though the explain plan shows it scanning the full number of rows in the table, because it's going against the index only, the real elapsed time was still impressive. Thanks for taking time to reply. Cherie Hillman, Alex Alex.Hillman@usmint.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] treas.gov cc: Sent by: Subject: RE: SQL tuning / optimization problem - IS NOT NULL - [EMAIL PROTECTED] Clarify 08/08/01 02:43 PM Please respond to ORACLE-L 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 cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 3.18 13.98 21184 21960 5 0 --- -- -- -- -- -- -- total3 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_REPORTER2SITE0 1418 CASE_WIP2WIPBIN113615
