6 Replies Latest reply: Aug 15, 2014 1:49 AM by Moazzam RSS

    How to find space required for 2 billions rows.

    user590978

      Hi Guru's,

       

      I am trying to create table using select statement joining tables and some business logic and select returning 2 Billion rows.

      Can you please help me out how much table space  , temp space required for the same.

       

       

       

      Any help would be appreciated!!!!

       

       

      Thanks,

        • 1. Re: How to find space required for 2 billions rows.
          BluShadow

          Q: If you are query the data from tables... why are you trying to, essentially, duplicate that data in yet another table?

           

          We also can't see your tables, or examples of your data or what you're trying to do, and we don't know what version of the database you are using.

          How can we help if we cannot see anything?

           

          Please read: Re: 2. How do I ask a question on the forums?

          • 2. Re: How to find space required for 2 billions rows.
            Dan Jankowski

            If you just want to know how much space is required to store two billion rows, have you tried storing a range of smaller data sets (e.g. 1 million, 5 million, 10 million), measuring the segment sizes, and extrapolating from there? It's probably not linear (particularly index segments), but should give you a rough idea. Also keep in mind the various ways Oracle will (or won't) reuse free space, e.g. conventional vs. direct path inserts, so make sure you are inserting data in a comparable way.

            • 3. Re: How to find space required for 2 billions rows.
              user590978

              First of all thanks for prompt response!

              Its business requirement and they cannot wait so long to returns the data in business hours. so we will populate the table at night so they can filter the data in morning and see it.

               

              This is the data I am getting :

               

              ex:

               

                                        

              PROJEC_123PROJH_BASE_YPROJH_BAD_DESCMAILA_DATEQUANTITYAD_SPEND_AMTPRDAD_TYPEMAIL_FORMATPR_CPAPEAE_CODE1PEAE_CODE2UST_NUMRECEFREYINK_PROTPANYRESAAAAA_NaUaMBOOKLINEEeK_PCT_BKDBASE_MTHBASE_YRBASAE_DAAAEP_DATWEEAAXCUSTAAA_SOAAADISC_AMTDISC_TYPEPUR_STAPUR_ARD_STATUSPRDT_ANKTAPA_RY
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC2541,147,447VA0XXSAABCCL - River Line2,827,77112/17/2012 00:00:00SAAB62,01306/21/2013 00:00:002NON-PROMOTED1,500VAS OFFERCOLLECTSINGLE4365
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC1,4521,433,133VA0XXSAABCCL - River Line2,827,77112/16/2012 00:00:00SAAB52,01305/08/2013 00:00:002NON-PROMOTED1,000FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC2,5541,587,902VA0XXSAGECCL - River Line2,827,77112/04/2012 00:00:00SAGE52,01305/11/2013 00:00:001NON-PROMOTED500POST SALESCOLLECTMULTINULL7
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC2,2221,923,564VA0XXEDRCCL - River Line2,827,77106/29/2012 00:00:00EDR62,01306/25/2013 00:00:002NON-PROMOTED0NON TACTICALCOLLECTMULTINULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC1001,954,196VA0XXSGECCL - River Line2,827,77112/09/2012 00:00:00SGE32,01303/10/2013 00:00:001NON-PROMOTED500FTC OFFERCONFIRMSINGLE1NULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC12,1211,954,860VA0XXLBSACCL - River Line2,827,77112/13/2012 00:00:00LBSA32,01303/18/2013 00:00:002NON-PROMOTED1,500VAS OFFERCOLLECTMULTI660
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC2,2221,954,897VA0XXELBACCL - River Line2,827,77112/14/2012 00:00:00ELBA52,01305/04/2013 00:00:002NON-PROMOTED1,000FTC OFFERCOLLECTSINGLE1NULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC2,1541,955,179VA0XXSABACCL - River Line2,827,77112/17/2012 00:00:00SABA62,01306/01/2013 00:00:001NON-PROMOTED500FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC27,8971,955,180VA0XXELBCCL - River Line2,827,77112/17/2012 00:00:00ELB62,01306/01/2013 00:00:001NON-PROMOTED500FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC4,5851,955,287NTFOTHER0XXSGECCL - River Line2,827,77112/18/2012 00:00:00SGE62,01306/04/2013 00:00:002NON-PROMOTED1,000FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC5841,955,330VA0XXLBSCCL - River Line2,827,77112/20/2012 00:00:00LBS52,01305/29/2013 00:00:001NON-PROMOTED500FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC5371,955,380VA0XXSGAECCL - River Line2,827,77112/19/2012 00:00:00SGAE52,01305/25/2013 00:00:001NON-PROMOTED500FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC5,4581,955,435VA0XXEDARCCL - River Line2,827,77112/20/2012 00:00:00EDAR32,01303/07/2013 00:00:001NON-PROMOTED798FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC5,5481,955,451VA0XXLAABSCCL - River Line2,827,77112/20/2012 00:00:00LAABS62,01306/04/2013 00:00:001NON-PROMOTED700FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC5541,955,453VA0XXLAABSCCL - River Line2,827,77112/20/2012 00:00:00LAABS62,01306/04/2013 00:00:001NON-PROMOTED700FTC OFFERNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC4,8541,955,613VA0XXLAABSCCL - River Line2,827,77112/22/2012 00:00:00LAABS62,01306/04/2013 00:00:001NON-PROMOTED700PRICE REDUCTIONNULLNULLNULLNULL
              34567732,013RCT/PR SPECIAL RIE PRI GATEFOLD SM (312/11/2012 00:00:0039,32425,501ATCSELF-MAILER1RHAC3,6581,961,811NTFOTHER0XXLAABSCCL - River Line2,827,77101/26/2013 00:00:00LAABS62,01306/04/2013 00:00:001NON-PROMOTED700PRICE REDUCTIONNULLNULLNULLNULL

               

              Any help would be appreciated!!!!

               

               

              Thanks,

              • 4. Re: How to find space required for 2 billions rows.
                rp0428
                I am trying to create table using select statement joining tables and some business logic and select returning 2 Billion rows.

                Can you please help me out how much table space  , temp space required for the same.

                Sure - but I have to run an errand right now. Would you help me with that first?

                 

                My wife wants me to go to the Walmart and pick up the 100 items on her shopping list.

                 

                Can you help me out with how much shelf space I will need in the kitchen to store those 100 items? Will 6 inches be enough? Or will I need 6 feet? How can I know how much space I will need? Please hurry - my wife is very impatient.

                 

                While I try to figure that out here are a couple of hints for you to think about:

                 

                1. The amount of space needed for those rows depends on HOW BIG a row is

                 

                2. The amount of 'temp' space needed depends on what you are doing that actually requires temp space.

                • 5. Re: How to find space required for 2 billions rows.
                  michaelrozar17

                  May be you should try with DBMS_SPACE

                  • 6. Re: How to find space required for 2 billions rows.
                    Moazzam

                    Try this:

                    1. Load some representative rows into a table.
                    2. Analyze the table.
                    3. Multiply the blocks column found in user_tables by the factor needed to make your sample as large as the anticipated number of total rows; this will be the estimated table size.

                    Ask Tom: Back to Basics