Stephane,
Thanks for your reply. I am going to check into setting the nulls with the application owner to see if that is an acceptable solution. I am unable to create a function-based index as this is a version 8.0.4 database. Thanks, Cherie Stephane Faroult To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> <sfaroult@orio cc: le.com> Subject: Re: Long-running SQL Sent by: [EMAIL PROTECTED] om 03/27/02 03:03 PM Please respond to ORACLE-L [EMAIL PROTECTED] wrote: > > We have a statement that I feel takes too long to run in a nightly data > load. > The table it runs against has 386,000 records. It runs for about 10 > seconds > on average. We're only loading about 50,000 records a night but this > statement > is running during the majority of the 9-hour load time. This is causing > the > load to run longer than our allowable window and causing me untold > headaches. > If anyone has any suggestions to make this run faster, I'd be greatly > appreciative. > > The columns in the where statement are all part of an index. However, > the functions on the columns add additional execution time and complexity. > > This is an 8.0.4 database so I can not make this a function-based index. > > I put this in a couple of SQL tuning tools and came up with no valid > alternatives. > I can't help thinking that the statement could be rewritten into a couple > of statements > so that it would be more efficient. However, I'm not skilled enough with > SQL to > do it. Perhaps someone else is. Here's the code. > > SELECT /*+ INDEX(EXP_COST_CENTER_DIM EXP_COST_CENTER_DIM_IDX1) + */ > EXP_COST_CENTER_KEY > FROM EXP_COST_CENTER_DIM > > WHERE ACCOUNT_NUMBER = :b1 AND > NVL(ORG_LEVEL_1_VALUE,'NONE') = NVL(:b2,'NONE') AND > NVL(ORG_LEVEL_2_VALUE,'NONE') = NVL(:b3,'NONE') AND > NVL(ORG_LEVEL_3_VALUE,'NONE') = NVL(:b4,'NONE') AND > NVL(ORG_LEVEL_4_VALUE,'NONE') = NVL(:b5,'NONE') AND > NVL(ORG_LEVEL_5_VALUE,'NONE') = NVL(:b6,'NONE') AND > NVL(ORG_LEVEL_6_VALUE,'NONE') = NVL(:b7,'NONE') AND > NVL(ORG_LEVEL_7_VALUE,'NONE') = NVL(:b8,'NONE') AND > NVL(ORG_LEVEL_8_VALUE, 'NONE')= NVL(:b9,'NONE') AND > ROWNUM = 1 > > SQL> desc exp_cost_center_dim > Name Null? Type > ------------------------------- -------- ---- > EXP_COST_CENTER_KEY NOT NULL NUMBER(7) > ACCOUNT_NUMBER NOT NULL NUMBER(9) > BATCH_WINDOW_DATE_KEY NOT NULL NUMBER(5) > ORG_LEVEL_1_VALUE VARCHAR2(20) > ORG_LEVEL_2_VALUE VARCHAR2(20) > ORG_LEVEL_3_VALUE VARCHAR2(20) > ORG_LEVEL_4_VALUE VARCHAR2(20) > ORG_LEVEL_5_VALUE VARCHAR2(20) > ORG_LEVEL_6_VALUE VARCHAR2(20) > ORG_LEVEL_7_VALUE VARCHAR2(20) > ORG_LEVEL_8_VALUE VARCHAR2(20) > DATA_SOURCE_MOD_DATETIME NOT NULL DATE > DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE > DATA_MART_MOD_DATETIME NOT NULL DATE > > SQL> select column_name from dba_ind_columns where index_name > ='EXP_COST_CENTER_D > IM_IDX1'; > > COLUMN_NAME > -------------------------------------------------------------------------------- > ACCOUNT_NUMBER > ORG_LEVEL_1_VALUE > ORG_LEVEL_2_VALUE > ORG_LEVEL_3_VALUE > ORG_LEVEL_4_VALUE > ORG_LEVEL_5_VALUE > ORG_LEVEL_6_VALUE > ORG_LEVEL_7_VALUE > ORG_LEVEL_8_VALUE > > SQL> select column_name from dba_ind_columns where index_name > ='EXP_COST_CENTER_D > IM_PK'; > > COLUMN_NAME > -------------------------------------------------------------------------------- > EXP_COST_CENTER_KEY > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > Cherie, I have only seen pieces of what I was expecting, so I am stepping in somewhat belatedly : o as several people underlined, your NVL functions are performance killers. Basically, the only usable column in your concatenated index happens to be ACCOUNT_NUMBER. Whether Oracle decides or not to use it is not very relevant, the point is that it's a rather useless index for this query. o I see two ways out : - Either you make all your ORG_LEVEL_n_VALUE columns NOT NULL, and modify the table so as to define NONE (or anything else) as default value. It may, though, not be totally acceptable and will certainly cause a good deal of internal fragmentation when you'll do it. That said, your table is not enormous and can be reorganized afterwards. You will then be able to remove all left-hand side NVLs and the index will be usable and, in all likelihood, used. - Or you create a function-based index on (ACCOUNT_NUMBER, NVL(ORG_LEVEL_1_VALUE,'NONE'), NVL(ORG_LEVEL_2_VALUE,'NONE'), NVL(ORG_LEVEL_3_VALUE,'NONE'), NVL(ORG_LEVEL_4_VALUE,'NONE'), NVL(ORG_LEVEL_5_VALUE,'NONE'), NVL(ORG_LEVEL_6_VALUE,'NONE') = NVL(:b7,'NONE'), NVL(ORG_LEVEL_7_VALUE,'NONE'), NVL(ORG_LEVEL_8_VALUE, 'NONE')) which will take more space than the curent index but will be directly usable by the query. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: 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).