Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, P.

Asked: May 12, 2020 - 9:26 am UTC

Last updated: May 13, 2020 - 9:46 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hello Team

We have Index organized table with 1.5Bil rows. We never delete or update any row in this table. Only INSERTs. Environment is DW not OLTP.

Table has 2 columns. 1st column is varchar2(130) is primary key. This is column is used in all searches.
2nd column is number and gets number generated from sequence from trigger on this IOT table. 2nd column also has index. ( we never search on this column)

We suspect inserts are slow in specific cases which ranges from 20 mins for 30k rows and sometimes 60+ minutes for 30k rows , same day. There is possibility that multiple jobs inserting rows into this table.

We have decided to perform housekeeping on this table and keep only required rows. After house keeping row count will be less than 100Mil.

Do you think reducing table size will be help to gain performance?

2nd question is, as part of housekeeping we will create new IOT with 100Mil rows and drop existing IOT and rename new to old IOT name. Is this ideal solution?

Many Thanks for support.

and Chris said...

Do you think reducing table size will be help to gain performance?

Maybe?

It depends on why the inserts are slow! This may be a symptom of some other underlying problem.

Before jumping in, analyze why the current inserts are slow. Do this by tracing the execution:

exec SYS.dbms_monitor.session_trace_enable ( waits => true, binds => true );

... run you process ...
  
exec SYS.dbms_monitor.session_trace_disable ();


You can also start tracing from another session - pass in the sid and serial number of the session you want to trace. Then review the resulting trace file. This will help you determine what the issue is.

If you're not sure what this means post the plan & waits from the (TKPROF formatted) file here.

2nd question is, as part of housekeeping we will create new IOT with 100Mil rows and drop existing IOT and rename new to old IOT name. Is this ideal solution?

If you mean "is this the best way to remove most of the rows from a huge table", then yes; create-table-as-select then switching the tables over is one of the fastest ways. I discuss other options at:

https://blogs.oracle.com/sql/how-to-delete-millions-of-rows-fast-with-sql

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Try increasing the cache size of the sequence

A reader, May 13, 2020 - 6:40 am UTC

.. have seen cases where sequence contention can also slow it down owing to frequent inserts.

Cheers!
Chris Saxon
May 13, 2020 - 9:46 am UTC

Could be - getting the SQL trace would confirm/deny this.

Martin ROSE, July 17, 2020 - 3:48 pm UTC

> Table has 2 columns. 1st column is varchar2(130) is primary key. This is column is used in all searches.
> 2nd column is number and gets number generated from sequence from trigger on this IOT table. 2nd column also has index. ( we never search on this column).


If the 2nd column is never searched on, why have the index? Remove it & your INSERTs will be faster.
Also, don't derive the seq. no. using a trigger. Reference it in the INSERT statement instead. Row-level triggers slow down everything done in bulk.

But my biggest question is what purpose does the 2nd column even serve? You're essentially just storing a random number against your PK.
In fact, what is the point of the entire table ?!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.