SELECT statement efficiency question

[Database Administrator]

I have the following two select statements in some code I've been looking
at. I don't have
access to a computer with Oracle on it currently so I can't determine this
myself.

Both selects do the same thing. The function in the second version simply
checks the
condition what is in the first and send back a 'Y' or 'N' whereas the first
has the
check in the where clause itself.

My question is this: is any one of the two selects more efficient (or less
efficient)
than the other?


SELECT a.mbr_dob,
a.mbr_first_nm,
a.mbr_gender,
b.mbr_key,
a.mbr_last_nm,
b.mbr_sys_key,
b.mbr_updated_dt,
a.mbr_x_fst_nm,
a.mbr_x_lst_nm,
a.person_id,
a.z_person_id
FROM mbr_person a,
mbr_system b
WHERE a.person_id = b.person_id
AND a.deactivate = 0
AND b.deactivate = 0
AND (
a.mbr_last_nm NOT IN ( 'DATA','CORRECTION' )
AND a.mbr_first_nm NOT IN ( 'DATA','CORRECTION' )
);


SELECT a.mbr_dob,
a.mbr_first_nm,
a.mbr_gender,
b.mbr_key,
a.mbr_last_nm,
b.mbr_sys_key,
b.mbr_updated_dt,
a.mbr_x_fst_nm,
a.mbr_x_lst_nm,
a.person_id,
a.z_person_id
FROM mbr_person a,
mbr_system b
WHERE a.person_id = b.person_id
AND a.deactivate = 0
AND b.deactivate = 0
AND not_data_correction( a.mbr_last_nm, a.mbr_first_nm ) = 'Y';

[Database Administrator]

On Sat, 07 Apr 2007 22:52:08 -0500, Dereck L. Dietz wrote:

> I have the following two select statements in some code I've been
> looking at. I don't have
> access to a computer with Oracle on it currently so I can't determine
> this myself.

Interesting situation.

>
> Both selects do the same thing. The function in the second version
> simply checks the
> condition what is in the first and send back a 'Y' or 'N' whereas the
> first has the
> check in the where clause itself.
>
> My question is this: is any one of the two selects more efficient (or
> less efficient)
> than the other?
>
>
> SELECT a.mbr_dob,
> a.mbr_first_nm,

......
The question here is what is more efficient: a "not in" expression that
clearly cannot use an index or the same thing wrapped up in a function,
with a possible use of the functional bitmap index. This question deserves
a closer look. First, if the table is a target for frequent DML
statements, bitmap indexes are out of the question. In this case, the
second method is much less efficient because it cannot use an index and it
has to execute PL/SQL code, something that the first method doesn't have
to do. If it's a DW database and you can create bitmap indexes at will,
then the second method will be faster. You can, however, create a hash
cluster based on that function and speed the retrieval.
What the closer look at the query reveals is a missing object. I don't
know many people named "DATA" (even the Startrek NG character is a machine
which doesn't run Windows) or "CORRECTION" and the columns like the first
and last name are usually reserved for humans. Your data structure is
trying to make distinctly inhuman entities look human. What you're
probably looking for is another column.
The real answer how to speed up this query is to index the remaining
columns and make sure that the optimizer uses available indexes. Then,
it will filter out the non-conforming results in a snap, if the result
set is small enough.
--
http://www.mladen-gogala.com

[Database Administrator]

On Sun, 08 Apr 2007 00:30:14 -0500, Dereck L. Dietz wrote:

> This is a data warehouse load. The input source has some rows with
> "DATA" and "CORRECTION" in the name fields which indicate there needs to
> be a data correction and also that those rows are NOT to be loaded into
> the tables.

May be a flag column defined like this:

should_load char(1) default 'Y' check (col1 in ('Y','N'))

would serve you better then the convention with names that
you currently use?
--
http://www.mladen-gogala.com

[Database Administrator]

"Mladen Gogala" wrote in message
newsan.2007.04.08.03.54.32@verizon.net...
> On Sat, 07 Apr 2007 22:52:08 -0500, Dereck L. Dietz wrote:
>
> What the closer look at the query reveals is a missing object. I don't
> know many people named "DATA" (even the Startrek NG character is a machine
> which doesn't run Windows) or "CORRECTION" and the columns like the first
> and last name are usually reserved for humans. Your data structure is
> trying to make distinctly inhuman entities look human. What you're
> probably looking for is another column.

This is a data warehouse load. The input source has some rows with "DATA"
and "CORRECTION" in the name fields which indicate there needs to be a data
correction and also that those rows are NOT to be loaded into the tables.

Thanks.

[Database Administrator]

On Apr 7, 11:52 pm, "Dereck L. Dietz" wrote:
> I have the following two select statements in some code I've been looking
> at. I don't have
> access to a computer with Oracle on it currently so I can't determine this
> myself.
>
> Both selects do the same thing. The function in the second version simply
> checks the
> condition what is in the first and send back a 'Y' or 'N' whereas the first
> has the
> check in the where clause itself.
>
> My question is this: is any one of the two selects more efficient (or less
> efficient)
> than the other?
>
> SELECT a.mbr_dob,
> a.mbr_first_nm,
> a.mbr_gender,
> b.mbr_key,
> a.mbr_last_nm,
> b.mbr_sys_key,
> b.mbr_updated_dt,
> a.mbr_x_fst_nm,
> a.mbr_x_lst_nm,
> a.person_id,
> a.z_person_id
> FROM mbr_person a,
> mbr_system b
> WHERE a.person_id = b.person_id
> AND a.deactivate = 0
> AND b.deactivate = 0
> AND (
> a.mbr_last_nm NOT IN ( 'DATA','CORRECTION' )
> AND a.mbr_first_nm NOT IN ( 'DATA','CORRECTION' )
> );
>
> SELECT a.mbr_dob,
> a.mbr_first_nm,
> a.mbr_gender,
> b.mbr_key,
> a.mbr_last_nm,
> b.mbr_sys_key,
> b.mbr_updated_dt,
> a.mbr_x_fst_nm,
> a.mbr_x_lst_nm,
> a.person_id,
> a.z_person_id
> FROM mbr_person a,
> mbr_system b
> WHERE a.person_id = b.person_id
> AND a.deactivate = 0
> AND b.deactivate = 0
> AND not_data_correction( a.mbr_last_nm, a.mbr_first_nm ) = 'Y';

In general, anything that you can do strictly in sql ( without using
plsql ) is probably going to be much more efficient and faster. This
should be tested and benchmarked in specific cases of course.

It's always good when you submit a question like this to include
details about what version of oracle.

While the optimizer has evolved and version by version may make better
choices when evaluating a NOT IN phrase at times especially when given
such a small range of things you are checking for alternate coding
should be considered or at least evaluated ( and ( a.mbr_first_nm <>
'DATA' OR a.mbr_first_nm <> 'CORRECTION' ) etc ) but IMHO something
just seems very strange about the design in the first place.

[Database Administrator]

On Apr 7, 11:52 pm, "Dereck L. Dietz" wrote:
> I have the following two select statements in some code I've been looking
> at. I don't have
> access to a computer with Oracle on it currently so I can't determine this
> myself.
>
> Both selects do the same thing. The function in the second version simply
> checks the
> condition what is in the first and send back a 'Y' or 'N' whereas the first
> has the
> check in the where clause itself.
>
> My question is this: is any one of the two selects more efficient (or less
> efficient)
> than the other?
>
> SELECT a.mbr_dob,
> a.mbr_first_nm,
> a.mbr_gender,
> b.mbr_key,
> a.mbr_last_nm,
> b.mbr_sys_key,
> b.mbr_updated_dt,
> a.mbr_x_fst_nm,
> a.mbr_x_lst_nm,
> a.person_id,
> a.z_person_id
> FROM mbr_person a,
> mbr_system b
> WHERE a.person_id = b.person_id
> AND a.deactivate = 0
> AND b.deactivate = 0
> AND (
> a.mbr_last_nm NOT IN ( 'DATA','CORRECTION' )
> AND a.mbr_first_nm NOT IN ( 'DATA','CORRECTION' )
> );
>
> SELECT a.mbr_dob,
> a.mbr_first_nm,
> a.mbr_gender,
> b.mbr_key,
> a.mbr_last_nm,
> b.mbr_sys_key,
> b.mbr_updated_dt,
> a.mbr_x_fst_nm,
> a.mbr_x_lst_nm,
> a.person_id,
> a.z_person_id
> FROM mbr_person a,
> mbr_system b
> WHERE a.person_id = b.person_id
> AND a.deactivate = 0
> AND b.deactivate = 0
> AND not_data_correction( a.mbr_last_nm, a.mbr_first_nm ) = 'Y';

Good advice given so far. I would caution against creating too many
indexes, as this will likely negatively impact performance of other
parts of the system while offering minimal assistance to this SQL
statement. Histograms could be important on the columns
MBR_PERSON.DEACTIVATE, MBR_SYSTEM.DEACTIVATE, MBR_PERSON.MBR_LAST_NM,
and MBR_PERSON.MBR_FIRST_NM. The histograms will give Oracle's cost
based optimizer a better picture of the contents of the columns,
rather than assuming an even spread of the data values between the min
and max for the column.

Keep in mind that Oracle may transform your SQL statement into another
equivalent form, and may use constraints and transitive closure to
generate additional predicates (think entries in the WHERE) clause for
the SQL statement. You may want to perform timing with alternate SQL
syntax:
SELECT a.mbr_dob,
a.mbr_first_nm,
a.mbr_gender,
b.mbr_key,
a.mbr_last_nm,
b.mbr_sys_key,
b.mbr_updated_dt,
a.mbr_x_fst_nm,
a.mbr_x_lst_nm,
a.person_id,
a.z_person_id
FROM mbr_person a,
mbr_system b
WHERE a.person_id = b.person_id
AND a.deactivate = 0
AND b.deactivate = 0
AND A.DEACTIVATE = B.DEACTIVATE
AND A.MBR_LAST_NM NOT IN ('DATA','CORRECTION')
AND A.MBR_FIRST_NM NOT IN ('DATA','CORRECTION');

Indexes on the MBR_PERSON.DEACTIVATE, MBR_SYSTEM.DEACTIVATE columns
may be helpful if most of the rows have something other than 0 in
those columns. Indexes on the MBR_PERSON.MBR_LAST_NM and
MBR_PERSON.MBR_FIRST_NM columns will likely be of limited use. An
index on MBR_PERSON.PERSON_ID, MBR_SYSTEM.PERSON_ID will likely be
very helpful.

My guess is that the above SQL statement will perform a full tablescan
on both tables. The full tablescans may be the most efficient way to
retrieve the rows. However, that will depend greatly on the
composition of the data in the two tables. Oracle may select to
perform a hash join between the two tables, so a large HASH_AREA_SIZE
may help.

As has been mentioned in this thread, avoid using PL/SQL for something
that can be done efficiently in pure SQL. Context switches (and I
suppose on-the-fly compiling of the PL/SQL code if not using Native
code compilation) are expensive on some operating system platforms,
and may greatly decrease performance.

The best way to test the performance of the various methods requires
access to the Oracle database, unless of course you want to dig
through 10046 traces at level 8 or 12.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

[Database Administrator]

On Sun, 08 Apr 2007 06:37:37 -0700, Charles Hooper wrote:

> Good advice given so far. I would caution against creating too many
> indexes, as this will likely negatively impact performance of other
> parts of the system

Charles, this is the line I frequently find in many books, CBT and
manuals and yet I have never seen insert or delete slowed down to the
unacceptable levels because of too many indexes. The only method to
diagnose that this is indeed happening would be to observe significant
increase in average I/O time on the underlying data file.
Again, I've never even seen this happening. I believe that this thing
with too many indexes is dangerous only in the extreme situations and it
is very hard to diagnose because the process that waits for writing the
index blocks is DBWR so the users never wait for the blocks to be
written. Users may wait for checkpoints or log file sync but not for the
index

--
http://www.mladen-gogala.com

[Database Administrator]

Mladen Gogala schrieb:
> On Sun, 08 Apr 2007 06:37:37 -0700, Charles Hooper wrote:
>
>> Good advice given so far. I would caution against creating too many
>> indexes, as this will likely negatively impact performance of other
>> parts of the system
>
> Charles, this is the line I frequently find in many books, CBT and
> manuals and yet I have never seen insert or delete slowed down to the
> unacceptable levels because of too many indexes. The only method to
> diagnose that this is indeed happening would be to observe significant
> increase in average I/O time on the underlying data file.
> Again, I've never even seen this happening. I believe that this thing
> with too many indexes is dangerous only in the extreme situations and it
> is very hard to diagnose because the process that waits for writing the
> index blocks is DBWR so the users never wait for the blocks to be
> written. Users may wait for checkpoints or log file sync but not for the
> index
>

I think, the increased IO activity is negligible compared to increased
latch contention in this case.
Here was an interesting blog entry about this
http://esemrick.blogspot.com/2006/03...alability.html
One can simply run this test to see the difference in 'cache buffers
chains' latches:

create table d as select * from dba_objects where 1=2;
create table d1 as select * from dba_objects where 1=2;
spool d.sql
select 'CREATE INDEX D_'||COLUMN_ID||' ON D('||COLUMN_NAME||');' from
dba_tab_columns where table_name = 'DBA_OBJECTS'
spool off
@d
exec runstats_pkg.rs_start
insert into d1 select * from dba_objects;
exec runstats_pkg.rs_middle
insert into d select * from dba_objects;
exec runstats_pkg.rs_stop

To be consequent, one should run 10046 trace for the indexed case and
compare time spent due to IO related wait events and latch related wait
events...

Some years ago i've to do a big data load into OLTP Siebel system over
weekend - some of very important tables (like s_customer) here often
have about 40-50 indexes per table (they are just illdesigned so, to be
generic) . The only possibility to get it done was to drop all indexes
not required for load and recreate them after it. I suppose, many ERP
systems don't differ much in this regard from Siebel.

Best regards

Maxim

[Database Administrator]

On Apr 8, 1:17 pm, Mladen Gogala
wrote:
> On Sun, 08 Apr 2007 06:37:37 -0700, Charles Hooper wrote:
> > Good advice given so far. I would caution against creating too many
> > indexes, as this will likely negatively impact performance of other
> > parts of the system
>
> Charles, this is the line I frequently find in many books, CBT and
> manuals and yet I have never seen insert or delete slowed down to the
> unacceptable levels because of too many indexes. The only method to
> diagnose that this is indeed happening would be to observe significant
> increase in average I/O time on the underlying data file.
> Again, I've never even seen this happening. I believe that this thing
> with too many indexes is dangerous only in the extreme situations and it
> is very hard to diagnose because the process that waits for writing the
> index blocks is DBWR so the users never wait for the blocks to be
> written. Users may wait for checkpoints or log file sync but not for the
> index
>
> --http://www.mladen-gogala.com

I have also seen the statement written in books, blogs, and articles
stating that having too many indexes on tables causes performance
problems, essentially that a table update that would have required one
I/O is turned into one I/O plus three or four I/Os per index (or maybe
it was two to three I/Os per index). I never tested this, since it
seemed to make sense, after all the I/O system can only handle so many
logical I/O and so many physical I/Os per second. I devised a quick
test earlier today using DBA_OBJECTS, but aborted the test after 15+
minutes, and instead decided to use a physical table for the source
data for testing.

First, I created a very simple table that includes the first 19
columns of my source table:
CREATE TABLE T3 AS
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY,
MINIMUM_ORDER_QTY,
MAXIMUM_ORDER_QTY,
ENGINEERING_MSTR,
PRODUCT_CODE,
COMMODITY_CODE,
MFG_NAME,
MFG_PART_ID,
FABRICATED,
PURCHASED,
STOCKED
FROM
PART
WHERE
0=1;

This is what the test table looks like:
DESC T3

Name Null? Type
----------------------------------------- -------- ------------
ID NOT NULL VARCHAR2(30)
DESCRIPTION VARCHAR2(40)
STOCK_UM NOT NULL VARCHAR2(15)
PLANNING_LEADTIME NOT NULL NUMBER
ORDER_POLICY NOT NULL CHAR(1)
ORDER_POINT NUMBER(14,4)
SAFETY_STOCK_QTY NUMBER(14,4)
FIXED_ORDER_QTY NUMBER(14,4)
DAYS_OF_SUPPLY NUMBER
MINIMUM_ORDER_QTY NUMBER(14,4)
MAXIMUM_ORDER_QTY NUMBER(14,4)
ENGINEERING_MSTR VARCHAR2(3)
PRODUCT_CODE VARCHAR2(15)
COMMODITY_CODE VARCHAR2(15)
MFG_NAME VARCHAR2(30)
MFG_PART_ID VARCHAR2(30)
FABRICATED NOT NULL CHAR(1)
PURCHASED NOT NULL CHAR(1)
STOCKED NOT NULL CHAR(1)

Now, in the database with no other activity, I bring the source
table's blocks into the KEEP pool:
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY,
MINIMUM_ORDER_QTY,
MAXIMUM_ORDER_QTY,
ENGINEERING_MSTR,
PRODUCT_CODE,
COMMODITY_CODE,
MFG_NAME,
MFG_PART_ID,
FABRICATED,
PURCHASED,
STOCKED
FROM
PART
WHERE
ROWNUM<=30000;

Now, I set up for the first test:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_NO_INDEXES';

And just perform a mass insert from the source table:
INSERT INTO
T3
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY,
MINIMUM_ORDER_QTY,
MAXIMUM_ORDER_QTY,
ENGINEERING_MSTR,
PRODUCT_CODE,
COMMODITY_CODE,
MFG_NAME,
MFG_PART_ID,
FABRICATED,
PURCHASED,
STOCKED
FROM
PART
WHERE
ROWNUM<=30000;

Now, I switch to another trace file and reset the test table back to
the starting point:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_CLEAN_UP';
TRUNCATE TABLE T3;

Let's create a simple B*Tree index on each column of our test table:
CREATE INDEX T3_1 ON T3(ID);
CREATE INDEX T3_2 ON T3(DESCRIPTION);
CREATE INDEX T3_3 ON T3(STOCK_UM);
CREATE INDEX T3_4 ON T3(PLANNING_LEADTIME);
CREATE INDEX T3_5 ON T3(ORDER_POLICY);
CREATE INDEX T3_6 ON T3(ORDER_POINT);
CREATE INDEX T3_7 ON T3(SAFETY_STOCK_QTY);
CREATE INDEX T3_8 ON T3(FIXED_ORDER_QTY);
CREATE INDEX T3_9 ON T3(DAYS_OF_SUPPLY);
CREATE INDEX T3_10 ON T3(MINIMUM_ORDER_QTY);
CREATE INDEX T3_11 ON T3(MAXIMUM_ORDER_QTY);
CREATE INDEX T3_12 ON T3(ENGINEERING_MSTR);
CREATE INDEX T3_13 ON T3(PRODUCT_CODE);
CREATE INDEX T3_14 ON T3(COMMODITY_CODE);
CREATE INDEX T3_15 ON T3(MFG_NAME);
CREATE INDEX T3_16 ON T3(MFG_PART_ID);
CREATE INDEX T3_17 ON T3(FABRICATED);
CREATE INDEX T3_18 ON T3(PURCHASED);
CREATE INDEX T3_19 ON T3(STOCKED);

Let's try the test again now that the 19 indexes are on the columns:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_WITH_INDEXES';

INSERT INTO
T3
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY,
MINIMUM_ORDER_QTY,
MAXIMUM_ORDER_QTY,
ENGINEERING_MSTR,
PRODUCT_CODE,
COMMODITY_CODE,
MFG_NAME,
MFG_PART_ID,
FABRICATED,
PURCHASED,
STOCKED
FROM
PART
WHERE
ROWNUM<=30000;

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'DONE';

>From the TEST_NO_INDEXES trace file, the wait events that appeared:
Wait Time Wait Event Wait Event Raw Details
0.036 db file sequential read WAIT #3: nam='db file sequential read'
ela= 36131 file#=4 block#=3 blocks=1 obj#=12373 tim=3652347137
0.036 db file sequential read WAIT #3: nam='db file sequential read'
ela= 36131 file#=4 block#=3 blocks=1 obj#=12373 tim=3652347137
0 db file sequential read WAIT #3: nam='db file sequential read' ela=
234 file#=4 block#=4 blocks=1 obj#=12373 tim=3652347440
0 db file sequential read WAIT #3: nam='db file sequential read' ela=
204 file#=4 block#=5 blocks=1 obj#=12373 tim=3652347707
0 db file sequential read WAIT #3: nam='db file sequential read' ela=
203 file#=4 block#=6 blocks=1 obj#=12373 tim=3652347971
0 db file sequential read WAIT #3: nam='db file sequential read' ela=
171 file#=4 block#=7 blocks=1 obj#=12373 tim=3652348181
0 db file sequential read WAIT #3: nam='db file sequential read' ela=
195 file#=4 block#=8 blocks=1 obj#=12373 tim=3652348434
----------------------
It looks like there were a total of seven single block writes that the
session waited on, for a total of 0.072 seconds
----

>From the TEST_WITH_INDEXES trace file, the wait events that appeared:
Wait Time Wait Event Wait Event Raw Details
0.079 db file sequential read WAIT #4: nam='db file sequential read'
ela= 79485 file#=4 block#=3295081 blocks=1 obj#=15076 tim=3735217458
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
205 file#=4 block#=3295082 blocks=1 obj#=15076 tim=3735218917
0.013 db file scattered read WAIT #4: nam='db file scattered read'
ela= 12548 file#=4 block#=3295340 blocks=5 obj#=15077 tim=3735231979
0.009 db file scattered read WAIT #4: nam='db file scattered read'
ela= 8933 file#=4 block#=3295700 blocks=5 obj#=15078 tim=3735241489
0.012 db file scattered read WAIT #4: nam='db file scattered read'
ela= 11927 file#=4 block#=3295828 blocks=5 obj#=15079 tim=3735253891
0.003 db file scattered read WAIT #4: nam='db file scattered read'
ela= 2692 file#=4 block#=3296028 blocks=5 obj#=15080 tim=3735257040
0.011 db file scattered read WAIT #4: nam='db file scattered read'
ela= 11249 file#=4 block#=3296092 blocks=5 obj#=15081 tim=3735268791
0.013 db file scattered read WAIT #4: nam='db file scattered read'
ela= 12550 file#=4 block#=3296164 blocks=5 obj#=15083 tim=3735281801
0.003 db file scattered read WAIT #4: nam='db file scattered read'
ela= 3421 file#=4 block#=3296188 blocks=5 obj#=15086 tim=3735285609
0.002 db file scattered read WAIT #4: nam='db file scattered read'
ela= 2223 file#=4 block#=3296204 blocks=5 obj#=15087 tim=3735288147
0.002 db file scattered read WAIT #4: nam='db file scattered read'
ela= 2243 file#=4 block#=3296220 blocks=5 obj#=15088 tim=3735290688
0.004 db file scattered read WAIT #4: nam='db file scattered read'
ela= 3825 file#=4 block#=3296236 blocks=5 obj#=15089 tim=3735294815
0.013 db file scattered read WAIT #4: nam='db file scattered read'
ela= 12564 file#=4 block#=3296308 blocks=5 obj#=15090 tim=3735307829
0.016 db file scattered read WAIT #4: nam='db file scattered read'
ela= 16333 file#=4 block#=3296404 blocks=5 obj#=15091 tim=3735324649
0.001 db file scattered read WAIT #4: nam='db file scattered read'
ela= 1026 file#=4 block#=3296412 blocks=5 obj#=15092 tim=3735325925
0.001 db file scattered read WAIT #4: nam='db file scattered read'
ela= 982 file#=4 block#=3296420 blocks=5 obj#=15093 tim=3735327191
0.011 db file scattered read WAIT #4: nam='db file scattered read'
ela= 11291 file#=4 block#=3296484 blocks=5 obj#=15094 tim=3735338933
0.011 db file scattered read WAIT #4: nam='db file scattered read'
ela= 11297 file#=4 block#=3296548 blocks=5 obj#=15095 tim=3735350677
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
458 file#=4 block#=3295698 blocks=1 obj#=15078 tim=3735356766
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
252 file#=4 block#=3295697 blocks=1 obj#=15078 tim=3735357279
0.033 db file sequential read WAIT #4: nam='db file sequential read'
ela= 33085 file#=4 block#=3296306 blocks=1 obj#=15090 tim=3735440346
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
196 file#=4 block#=3296305 blocks=1 obj#=15090 tim=3735440775
0.01 db file sequential read WAIT #4: nam='db file sequential read'
ela= 10447 file#=4 block#=3295338 blocks=1 obj#=15077 tim=3735453346
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
215 file#=4 block#=3295337 blocks=1 obj#=15077 tim=3735453758
0.01 db file sequential read WAIT #4: nam='db file sequential read'
ela= 10359 file#=4 block#=3295826 blocks=1 obj#=15079 tim=3735475243
0.01 db file sequential read WAIT #4: nam='db file sequential read'
ela= 10359 file#=4 block#=3295826 blocks=1 obj#=15079 tim=3735475243
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
253 file#=4 block#=3295825 blocks=1 obj#=15079 tim=3735475696
0.006 db file sequential read WAIT #4: nam='db file sequential read'
ela= 5589 file#=4 block#=3296234 blocks=1 obj#=15089 tim=3735482850
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
218 file#=4 block#=3296233 blocks=1 obj#=15089 tim=3735483299
0.002 db file sequential read WAIT #4: nam='db file sequential read'
ela= 1916 file#=4 block#=3296026 blocks=1 obj#=15080 tim=3735489517
0.011 db file sequential read WAIT #4: nam='db file sequential read'
ela= 10727 file#=4 block#=3296025 blocks=1 obj#=15080 tim=3735500455
0.011 db file sequential read WAIT #4: nam='db file sequential read'
ela= 10945 file#=4 block#=3296090 blocks=1 obj#=15081 tim=3735512137
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
330 file#=4 block#=3296089 blocks=1 obj#=15081 tim=3735512661
0.012 db file sequential read WAIT #4: nam='db file sequential read'
ela= 11793 file#=4 block#=3296418 blocks=1 obj#=15093 tim=3735526329
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
283 file#=4 block#=3296417 blocks=1 obj#=15093 tim=3735526806
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
266 file#=4 block#=3296482 blocks=1 obj#=15094 tim=3735527871
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
271 file#=4 block#=3296481 blocks=1 obj#=15094 tim=3735528379
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
376 file#=4 block#=3296546 blocks=1 obj#=15095 tim=3735529787
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
306 file#=4 block#=3296545 blocks=1 obj#=15095 tim=3735530308
0.023 db file scattered read WAIT #4: nam='db file scattered read'
ela= 22865 file#=4 block#=3296172 blocks=5 obj#=15084 tim=3735560714
0.077 db file scattered read WAIT #4: nam='db file scattered read'
ela= 77052 file#=4 block#=3296156 blocks=5 obj#=15082 tim=3735658131
0.077 db file scattered read WAIT #4: nam='db file scattered read'
ela= 77052 file#=4 block#=3296156 blocks=5 obj#=15082 tim=3735658131
0.123 db file sequential read WAIT #4: nam='db file sequential read'
ela= 122864 file#=4 block#=3296186 blocks=1 obj#=15086 tim=3735917834
0.123 db file sequential read WAIT #4: nam='db file sequential read'
ela= 122864 file#=4 block#=3296186 blocks=1 obj#=15086 tim=3735917834
0.121 db file sequential read WAIT #4: nam='db file sequential read'
ela= 120743 file#=4 block#=3296185 blocks=1 obj#=15086 tim=3736038702
0.135 db file sequential read WAIT #4: nam='db file sequential read'
ela= 134798 file#=4 block#=3296218 blocks=1 obj#=15088 tim=3736222728
0.135 db file sequential read WAIT #4: nam='db file sequential read'
ela= 134798 file#=4 block#=3296218 blocks=1 obj#=15088 tim=3736222728
0.013 db file sequential read WAIT #4: nam='db file sequential read'
ela= 12658 file#=4 block#=3296217 blocks=1 obj#=15088 tim=3736235504
0.27 log buffer space WAIT #4: nam='log buffer space' ela= 270380 p1=0
p2=0 p3=0 obj#=15088 tim=3736880230
0.27 log buffer space WAIT #4: nam='log buffer space' ela= 270380 p1=0
p2=0 p3=0 obj#=15088 tim=3736880230
0.402 log buffer space WAIT #4: nam='log buffer space' ela= 402235
p1=0 p2=0 p3=0 obj#=15088 tim=3737410490
0.402 log buffer space WAIT #4: nam='log buffer space' ela= 402235
p1=0 p2=0 p3=0 obj#=15088 tim=3737410490
0.17 db file sequential read WAIT #4: nam='db file sequential read'
ela= 170460 file#=4 block#=3296202 blocks=1 obj#=15087 tim=3737612923
0.17 db file sequential read WAIT #4: nam='db file sequential read'
ela= 170460 file#=4 block#=3296202 blocks=1 obj#=15087 tim=3737612923
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
346 file#=4 block#=3296201 blocks=1 obj#=15087 tim=3737613373
0.041 log buffer space WAIT #4: nam='log buffer space' ela= 40699 p1=0
p2=0 p3=0 obj#=15087 tim=3737756910
0.041 log buffer space WAIT #4: nam='log buffer space' ela= 40699 p1=0
p2=0 p3=0 obj#=15087 tim=3737756910
0.153 db file sequential read WAIT #4: nam='db file sequential read'
ela= 153445 file#=4 block#=3296154 blocks=1 obj#=15082 tim=3738030523
0.153 db file sequential read WAIT #4: nam='db file sequential read'
ela= 153445 file#=4 block#=3296154 blocks=1 obj#=15082 tim=3738030523
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
316 file#=4 block#=3296153 blocks=1 obj#=15082 tim=3738030946
0.15 log buffer space WAIT #4: nam='log buffer space' ela= 150208 p1=0
p2=0 p3=0 obj#=15082 tim=3738192472
0.018 log buffer space WAIT #4: nam='log buffer space' ela= 18194 p1=0
p2=0 p3=0 obj#=15082 tim=3738587208
0.018 log buffer space WAIT #4: nam='log buffer space' ela= 18194 p1=0
p2=0 p3=0 obj#=15082 tim=3738587208
0.405 db file sequential read WAIT #4: nam='db file sequential read'
ela= 404755 file#=4 block#=3296402 blocks=1 obj#=15091 tim=3739010542
0.405 db file sequential read WAIT #4: nam='db file sequential read'
ela= 404755 file#=4 block#=3296402 blocks=1 obj#=15091 tim=3739010542
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
295 file#=4 block#=3296401 blocks=1 obj#=15091 tim=3739010977
0.178 db file sequential read WAIT #4: nam='db file sequential read'
ela= 178125 file#=4 block#=3296410 blocks=1 obj#=15092 tim=3739204175
0.178 db file sequential read WAIT #4: nam='db file sequential read'
ela= 178125 file#=4 block#=3296410 blocks=1 obj#=15092 tim=3739204175
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
284 file#=4 block#=3296409 blocks=1 obj#=15092 tim=3739204575
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
466 file#=4 block#=3296162 blocks=1 obj#=15083 tim=3739212628
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
466 file#=4 block#=3296162 blocks=1 obj#=15083 tim=3739212628
0 db file sequential read WAIT #4: nam='db file sequential read' ela=
206 file#=4 block#=3296161 blocks=1 obj#=15083 tim=3739212885
0.298 log buffer space WAIT #4: nam='log buffer space' ela= 297733
p1=0 p2=0 p3=0 obj#=15083 tim=3739742943
0.298 log buffer space WAIT #4: nam='log buffer space' ela= 297733
p1=0 p2=0 p3=0 obj#=15083 tim=3739742943
0.933 log buffer space WAIT #4: nam='log buffer space' ela= 932660
p1=0 p2=0 p3=0 obj#=15083 tim=3740714395
0.587 log buffer space WAIT #4: nam='log buffer space' ela= 586746
p1=0 p2=0 p3=0 obj#=15083 tim=3741542848
0.587 log buffer space WAIT #4: nam='log buffer space' ela= 586746
p1=0 p2=0 p3=0 obj#=15083 tim=3741542848
0.818 log buffer space WAIT #4: nam='log buffer space' ela= 817764
p1=0 p2=0 p3=0 obj#=15083 tim=3742397903
0.818 log buffer space WAIT #4: nam='log buffer space' ela= 817764
p1=0 p2=0 p3=0 obj#=15083 tim=3742397903
1 log buffer space WAIT #4: nam='log buffer space' ela= 1000039 p1=0
p2=0 p3=0 obj#=15083 tim=3743638629
0.031 log buffer space WAIT #4: nam='log buffer space' ela= 31071 p1=0
p2=0 p3=0 obj#=15083 tim=3743839418
0.845 log buffer space WAIT #4: nam='log buffer space' ela= 844796
p1=0 p2=0 p3=0 obj#=15083 tim=3744726107
0.845 log buffer space WAIT #4: nam='log buffer space' ela= 844796
p1=0 p2=0 p3=0 obj#=15083 tim=3744726107
----------------------------------------
Totals from the above:
2.653 db file sequential read
0.302 db file scattered read
8.671 log buffer space

0.072 seconds is definitely less that 11.626 seconds. This looks like
a severe penalty from having the indexes in place - 161.5 times longer
to insert the same data. I did not test update performance, although
I would anticipate individual updates of a single row on all columns
in this table by a "screen painter" that automatically updates all
columns regardless of whether or not the columns changed to take
roughly 57 times longer than if no indexes existed on the table: 1:
(1+19*3). This increased time may be small compared to improved query
performance by end users.

The above was an isolated test case, and may very well exibit
different bahavior in other environments.

Mladen, thanks for prompting the test. I was curious about this also.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

[Database Administrator]

Charles Hooper wrote:

> I have also seen the statement written in books, blogs, and articles
> stating that having too many indexes on tables causes performance
> problems, essentially that a table update that would have required one
> I/O is turned into one I/O plus three or four I/Os per index (or maybe
> it was two to three I/Os per index).

And if you want to see it really get ugly ... add a bunch of indexes
and rollback the transaction.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org