Jerome,
Yes, this data is not normalized. You are correct. I am not sure why the
table was denormalized like this but it is not an option to change the
underlying table at this point in time. The application is in the process
of being redesigned but in the meantime, I have to keep the current
production application working.
Yes, I am concerned that the NVL function is adding a lot of time. The
setting of default value to NONE might be a viable option. I will pursue
that with the application owner. I did find that sometimes the SQL ran for
more than 10 seconds and sometimes it ran for less than a second. It
would make sense that the number of NVLS it had would make a difference.
Thanks for this helpful reply.
Cherie Machler
Oracle DBA
Gelco Information Network
"Whittle Jerome
Contr NCI" To: <[EMAIL PROTECTED]>
<Jerome.Whittle@sco cc: <[EMAIL PROTECTED]>
tt.af.mil> Subject: RE: Long-running SQL
03/27/02 10:26 AM
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.
[EMAIL PROTECTED]
618-622-4145
> -----Original Message-----
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
>
> 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).
