demonstrated how bad HWM would be for FTS

[DaPi]

Data in buffers from a FTS is the first to be flushed when space is needed - so it's the most sensitive to any other activity on the machine that competes for the buffers. I'd repeat the selects a few times to see what the typical values are.

[learning_bee]

Dapi,

22 sec for FTS to HWM isn't bad at all, I thought it would take way more than 22 sec for 4 million rows.

[DaPi]

Depends on row-length => number of blocks.
Depends on the hardware.

If you read the table once per day, OK.
If you are polling every second for work to do, not OK.

[learning_bee]

if we have HWM on the table and later on we insert a bunch of data into that table, will Oracle reuse to empty segment or it will use the new one and move the HWM????

[nagarjuna]

Originally posted by learning_bee
if we have HWM on the table and later on we insert a bunch of data into that table, will Oracle reuse to empty segment or it will use the new one and move the HWM????

The empty blocks below HWM will be reused. And the method you are following (delete, fill the table and rebuild index) is the only option you have as per your application design.

Dont forget to analyze the table at the end of the rebuild of indexes. This is the additional thing you may have to keep in mind.

[DaPi]

If there is a block which was "full" and is now less full than PCTUSED, new rows will be inserted in it.

http://download-west.oracle.com/docs...rec.htm#635139

[davey23uk]

Originally posted by nagarjuna
The empty blocks below HWM will be reused. And the method you are following (delete, fill the table and rebuild index) is the only option you have as per your application design.

Dont forget to analyze the table at the end of the rebuild of indexes. This is the additional thing you may have to keep in mind.

unless you are doing insert /*+APPEND*/ ...

[DaPi]

Originally posted by davey23uk
unless you are doing insert /*+APPEND*/ ...

AH YES! an excellent way to ensure the HWM gets higher and higher!

[marist89]

Originally posted by learning_bee
the developer won't be convinced that HWM is the big issue here and he think it's just a minor issue.

The developer knows the application best. If he knows a certain amount of data will be inserted into this table, deleted, and the same amount is inserted again, then he is right. IMHO this sounds like a minor issue at best.

[pando]

if this temporary table or no constraints just do

insert the metadata needed to a temporary table

drop the old table

rename the temp table

or migrate to 10g and shrink your segments