7 Replies Latest reply: Jan 20, 2010 2:34 PM by Maran Viswarayar RSS

    one row per oracle block


      Is it possible to create a table such a way that .. it holds 1 table row per oracle block

      table average row size is around 150 bytes and oracle block size is 8K

      oracle database version is and os is hpux.

      Please suggest.
        • 1. Re: one row per oracle block
          Anurag Tibrewal

          I doubt it would be possible in Oracle.
          Still just interested to know what bussiness requirement you need to fulfill by having one row per oracle block? Is not that wastage of space.

          • 2. Re: one row per oracle block
            Hemant K Chitale
            Yes, specify a high PCTFREE in your CREATE TABLE statement.
            (PCTFREE still works in ASSM tablespaces !)

            However, the question would be : Why you want to do this ?
            Your table would "grow" much larger than necessary, FullTableScans will become very expensive.

            If you want to reduce INSERT contention, ASSM is precisely implemented for that reason.

            Hemant K Chitale
            • 3. Re: one row per oracle block
              Maran Viswarayar
              Thats bit tricky but you can acheive that but that requires some calculations but jsut the PCTFREE parameters

              Assume you have 8k block(which you have) and only 150 bytes are used meaning you are just using less 2 % of the block...

              You should have atleast 50% of the rowlength for future updates meaning 150+75 bytes which is 225 bytes

              Then your PCTFREE must be around 95 %

              alter table employee pctfree 95; will do it..

              BUt there could be serious issues with space occupied byt the DB becuase it is going to have 95% free
              • 4. Re: one row per oracle block
                Yes, it should be possible. And, it can be done without messing with storage parameters, such as PCTFREE.

                Something like this should do it:
                create table blah(a number, b varchar2(10));
                insert into blah values(1,'Hello');
                alter table blah minimize records_per_block;
                --From this point on, number of rows per block will be limited to 1.
                alter table ... minimize records_per_block;
                functionality sets the Hakan factor. This limits the number of rows per block to the max of the largest number of rows in any block in the table. This functionality was originally conceived to help optimize builds of bitmap indexes, but it can be used anytime you want to 'artificially' limit the number of rows per block.

                Hope that helps,

                • 5. Re: one row per oracle block
                  Maran Viswarayar
                  Wow..Thats a new thing...

                  Thank you
                  • 6. Re: one row per oracle block
                    Jonathan Lewis
                    Maran Viswarayar wrote:
                    Wow..Thats a new thing...
                    Not all that new. From "Practical Oracle 8i" chapter 5 page

                    With 8.1 there is an interesting (and probably accidental) solution to this problem in the command:
                    <ul>alter table tabX minimize records_per_block;*</ul>

                    Jonathan Lewis

                    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                    fixed format
                    "Science is more than a body of knowledge; it is a way of thinking" 
                    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                    • 7. Re: one row per oracle block
                      Maran Viswarayar
                      I mean I haven't heard about that but did had a look at It after that