Hi Cherie, do you know for sure it is using the index? Have you traced it? Can you post the explain plan? What's the cardinality of account_number in this table?
I can just hear those people over there screaming at you saying it's your problem... Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 > -----Original Message----- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, March 27, 2002 9:33 AM > To: Multiple recipients of list ORACLE-L > Subject: Long-running SQL > > > 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 > > > > -- > 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: Koivu, Lisa 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).
