Re: Large in memory DW table

I would imagine that your best chance of performance gain would be to compress this table, to make sure that the physical row ordering is optimised for the best compression ratio, to apply appropriate partitioning for optimum partition pruning, and to consider implementing other tables with subsets of rows and columns to reduce the logical reads. Have you already considered these steps?

Rob Dempsey <Rob.Dempsey_at_5one.co.uk> wrote:                Hi
   

  Setup    

• Large read only table (100 million rows) in a DW that is required to be based mainly in memory for performance reason.
• Version 10.2.0.1

  I have some queries that I can not find information on, wonder if anyone knew    

• Setting the tablespace to READ ONLY. Would this improve performance? Would oracle know that the block is read-only hence not have to worry about checking whether SCN is current, mess around with undo information (I guess I am asking whether a different code path would be taken)
• Do select statements need ITL slots? If not I am thinking of setting INITTRANS and MAXTRANS to 1 so that I can squeeze as much data into a block as possible? Disadvantages? (It is a table for only reading). Only one slot would be needed to load the data via a single process

  Rob    

--
http://www.freelists.org/webpage/oracle-l