Re: performance tuning question

I have an index on ID field, it is not unique though. It seems that the index is ignored during the query, is there anything that could cause that??? How do I make sure that the index is used?

"Edwin" <vanmeerendonk_at_wxs.nl> wrote in message news:31354201.0205030245.47a06f60_at_posting.google.com...
> besides that,
> when you create an unique index on the id field, You could try
> reversing the logic:
> begin
> insert into yourtable
> exception
> when dup_val_on_index
> then
> update yourtable
> end;
>
> The problem is in the select(1) from yourtable where id = .... Without
> an index you will hit every time a Full Table Scan, so the index on ID
> is necessary.
>
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
 news:<0qpA8.11463$xv1.421_at_rwcrnsc51.ops.asp.att.net>...
> > You need to use bind variables. Read the manual on that. Also just
 select
> > 1 not * .
> > That will help a lot.
> > Jim
> > "Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message
> > news:gbpA8.4450$M7.1797776_at_twister.socal.rr.com...
> > > Hi there,
> > >
> > > I'm looking for an advice what configuration parameters I need to look
 at
> > > first to boost Oracle 8i performance for the following simple
 scenario.
 I'm
> > > doing a batch update of a table containing about 65k records. The
 table
> > > structure is rather simple:
> > >
> > > MY_TABLE: ID, COL1, COL2, COL3
> > >
> > > all fields are integers except COL3 which is VARCHAR 2000. There is no
> > > primary key set on the table during update. The program is Java and
 its
> > > logic is (in pseudocode):
> > >
> > > for (each id in update)
> > > {
> > > if (exists(SELECT * FROM MY_TABLE WHERE ID=id))
> > > UPDATE MY_TABLE SET COL1=val1, COL2=val2, COL3=val3 WHERE
 ID=id
> > > else
> > > INSERT INTO MY_TABLE id,val1,val2,val3
> > > }
> > >
> > > The update is going painfully slow and takes about 6 hours, and as the
> > > matter of fact it is about 7 times slower than MS SQL or DB2 on both
 AIX
 and
> > > Win2k (can't really tell about MS SQL on AIX ...). This is not a
 network
> > > problem or other environment related issue since load into empty table
 using
> > > only inserts without selects is pretty fast and takes 10-15 minutes,
 so
 this
> > > is full table scans that slow things down. I get 20% performance gain
 if
> > > index is created on ID field which doesn't help a lot. So, what do I
 need
 to
> > > look at first? buffers? shared pool? block size? do I need to supply
 any
> > > optimizer hints in my query so the index is actually used? Just want
 to
 get
> > > some info before I start poking around...
> > >
> > > Thanks for any clues.
> > > Vladimir.
> > >
> > >
Received on Sat May 04 2002 - 09:10:22 CEST