RE: Long-running SQL

Cherie,

Two things.
1. All the ORG_LEVEL_x_VALUE fields leads me to believe that the data isn't normalized. What happens when someone needs to add ORG_LEVEL_9_VALUE?

2. I think all the NVLs make your index worthless. About the only functions an index can work with are MIN and MAX. You might do better setting the default value of those fields to NONE and then getting rid of the NVLs. As you can see below, adding a couple of NVLs slowed the query down from 10 milliseconds to 4 seconds.

select /*+ index(flight_legs PK_PM_FLIGHT_LEGS) */ FIRST_MISSION_ID, ITIN_NUM from phred.flight_legs
where NVL(FIRST_MISSION_ID,'NONE') = 'S12020201085' and NVL(ITIN_NUM,100) = 100; Time 4 seconds on 300,000 records.

select /*+ index(flight_legs PK_PM_FLIGHT_LEGS) */ FIRST_MISSION_ID, ITIN_NUM from phred.flight_legs
where FIRST_MISSION_ID = 'S12020201085' and ITIN_NUM = 100; Time 10 milliseconds

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
>
> 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: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

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).