Table Size - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Table Size

  1. #11
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Ok you got so many KBs?????? ( for jus 5 rows, confused here )...
    I realy don't understand your remark. Your query is returning that many of KBs (40 Kbytes for just 5 rows, yes). So why are you confused? Your query is reporting those 5 rows occupy 40 KB, while it is evident to everybody with a bit of common sence that those 5 rows actually require about 100 Kb!

    Ok lets see what i get..

    Code:
    US18_DEV_DBA> SELECT value FROM v$parameter
      2  WHERE name = 'db_block_size';
    
    VALUE
    ------------------------------------------------------------------------
    4096
    
    US18_DEV_DBA> SELECT 
      2  COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*4096/1024 
      3  AS "Table Size in KBytes" 
      4  FROM test_2;
    
    Table Size in KBytes
    --------------------
                      20
    The one which should have taken min of 100KB is well within 20KB??
    Thus we need to understand a bit more on how the space is used up....
    There's nothing special to understand about to understand about "how the space is used up". It's like I said in the beginning: "Your query tell's us more or less nothing about the size of a tablespace". It's as simple as that. For the same amount of data that realy takes approximately 100KB of space in a table segment your query reported the size to be 40KB in my case and 20 Kb in your case. Both time plain wrong, so it simply sucks, that's all.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #12
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    You both guys are right the problem here is, concept of getting actual data thru ROWID query will not work if u will have a chained rows since in the given example of block_size 8K -- 1 row will fit into 2.5 blocks and 5 will in 15 blocks but query is reading one row from the table and thowing one block per record ( as size of one rows exceeds 8K), There is now way to get the another 10 link listed chained blocks.

  3. #13
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by bhallar
    You both guys are right the problem here is, concept of getting actual data thru ROWID query will not work if u will have a chained rows since in the given example of block_size 8K -- 1 row will fit into 2.5 blocks and 5 will in 15 blocks but query is reading one row from the table and thowing one block per record ( as size of one rows exceeds 8K), There is now way to get the another 10 link listed chained blocks.
    Still waiting to see how you can do create spfile from pfile after you've shut down the database.
    http://www.dbasupport.com/forums/sho...threadid=39801

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    I realy don't understand your remark.
    In ur post I saw...

    Code:
    Table Size in KBytes
    --------------------
              40,0195313
    So was the remark..did it report 400195313 KB? huh..


    Originally posted by jmodic
    There's nothing special to understand about to understand about "how the space is used up". It's like I said in the beginning: "Your query tell's us more or less nothing about the size of a tablespace". It's as simple as that. For the same amount of data that realy takes approximately 100KB of space in a table segment your query reported the size to be 40KB in my case and 20 Kb in your case. Both time plain wrong, so it simply sucks, that's all.
    Yes there is some thing we need to understand...Row Chaining is what i meant when i said "Thus we need to understand a bit more on how the space is used up" -- Quite though i expected you would have hinted that..

    And Yes as "bhallar" said ROWID really cant be used in cases of too many Row Chains..

    And most often, in any application that i saw, will not have too much of chaining...so one can evaluate almost accurate no of blocks used up and thus size ( PS i aint considering effciency of block utilization )

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    In ur post I saw...

    Code:
    Table Size in KBytes
    --------------------
              40,0195313
    So was the remark..did it report 400195313 KB? huh..
    Ah. There is a concept called "National Language Support" in Oracle. And as you might know, not everyone's got that set to American/America or something like that, where NLS_NUMERIC_CHARACTERS by default is set to '.,'. There are many countries that uses just the opposite combination - "." for thousands separator and "," as decimal character. And I can't see how you drived number "40,0195313" to mean "400195313" - it simply can't be, even if "," would be thousands sepparator. Everyone that saw this number must have clearly realised that ',' could only be a decimal character here, no other possibility. Don't you think? (And everyone could also noticed that I mistyped the 8K block size number in that query - used 8196 instead of 8192).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Ah. There is a concept called "National Language Support" in Oracle. And as you might know, not everyone's got that set to American/America or something like that, where NLS_NUMERIC_CHARACTERS by default is set to '.,'. There are many countries that uses just the opposite combination - "." for thousands separator and "," as decimal character. And I can't see how you drived number "40,0195313" to mean "400195313" - it simply can't be, even if "," would be thousands sepparator. Everyone that saw this number must have clearly realised that ',' could only be a decimal character here, no other possibility. Don't you think? (And everyone could also noticed that I mistyped the 8K block size number in that query - used 8196 instead of 8192).
    I quite really didnt notice u mis typed 8K as 8196..

    and abt the seperator, i was wondering it to be thoushand or some thing like it..so i thought how on earth wud u get so many (PS i did have a feeling that it might be decimal seperator but 8K/1K will be 8 so how on earth will that lead to decimal, and person like u wont do any mistakes in specifing block size so overlooked that one and was wondering) ...

    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width