Re: IOT, memory and transaction time

On 17.04.2007 11:26, deja@xxxxxxxxxxxx wrote:
We have a table with 4 columns - col1 int, col2 int, col3 char(32),
col4 char(32).

Previously there were 2 indexes
unique index1 on col3, col1
index2 on col2, col1

Reads always read all columns.

We have recently changed the way this table works. The columns are all
the same but because of the way we put data in we have changed the
indexes.

Please provide complete DDL for old and new table layout. You can easily generate it with DBMS_METADATA.GET_DDL if your verison of Oracle includes it.

Now the table is an Index Ordered table with col2,col3 as PRIMARY KEY.

IOT = Index Organized Table.

There is an additional index on col3,col2.

Why do you put another index on the same set of columns?

Reads have improved quite a lot but there is a problem with the
writes. Previously, when adding 40,000 records at a time, write times
were pretty consistent no matter how big the table (this transaction
used to actually involve 200 updates of the same table followed by
40,000 inserts). With the new indexes, they get progressively worse
with every write plateauing at around 10 minutes!! (this new
transaction involves updating a dummy table with one record and one
column 200 times (using a where clause that references the other
table), followed by 40,000 inserts). The update part of the
transaction basically checks that no-one else has updated since you
read.

Why have write times changed so dramatically? Why do they get
progressively worse? If it was the update statement that references
the other table with a where clause, then why have Read times improved
(using pretty much the same clause)? So I figured it probably wasn't
the Update statement. So, looking at the Insert, why has this become
so different. The columns are the same, it is just that one is Index
Ordered and one isn't. The 40000 inserts will all have a col2 value
that is the same but is greater than any previous write of col2. The
col3 values may not be in order when written back - so is it just a
memory issue? If we allocate more memory to the Oracle instance will
that solve it? Does the memory have to be allocated to a specific area
or is the SGA sufficient?

My first guess would be that because of the changed physical structure inserts are generally more complex and generate more IO.

Kind regards

robert
.

Relevant Pages

  • IOT, memory and transaction time
    ... unique index1 on col3, col1 ... If it was the update statement that references ... The 40000 inserts will all have a col2 value ...
    (comp.databases.oracle.misc)
  • Dynamic PreparedStatements with Variable In Parameters
    ... AND col2=? ... AND col3=? ... I have to start coding tomorrow so I'm eager ...
    (comp.lang.java.programmer)
  • Re: Strange effects of Cast
    ... Cast to tuncate strings. ... In the query below although I get the expected result in COL1 and COL2 ... COL3 and COL4 return only three characters. ...
    (comp.databases.oracle.misc)
  • RE: DataValidation Check in cell of excel
    ... range where the Data Validation is, it is wiped out and anything can be ... when user enters in col1, it should be allowed only if data is not ... present in col2 and col3. ...
    (microsoft.public.excel.misc)
  • Re: slecting multiple distinct columns..
    ... this will bring back records that have duplicate records ... Col1, Col2, Col3, COUNTas NumberDups ...
    (comp.databases.ms-sqlserver)