I created 3 tables T4k ( in 4k blocksize tablepace), T8k in 8k block size and T16k in 16k blocksize respectively.
All the 3 tables are single column based. All the tables are same sized of 760MB(sum(bytes) from dba_segments).
I tested my results (Elapsed Time) by running select * from table_name two times i.e First time with Char(2000) and second time with Char(600).
No. of allocated blocks are same in both the cases of char(600) and char(2000). But with different no. of rows.
Below are the results in case of Char(2000)
, different no. of rows to equalize the table size of 760MB.
T4k= 192000 rows= 00:22:43.70 (elapsed time) = 193595 blocks allocated
T8k=1288800 rows= 00:35:15.11 = 96413 blocks allocated
T16k=337000 rows= 00:39:52.63 = 48333 blocks allocated
Below are the results in case of Char(600)
T4k= 960000 rows = 00:42:25.99 (elapsed time)
T8k=1055000 rows= 00:47:07.55
T16k=1155000 rows= 00:53:26.72
I have 2 very important questions.
First is that 4k block size has faster result as compared to 8k and 16k block size results as shown above. Expert opinion required please
Secondly, If we compare char(2000) results with char(600). There is huge elapsed time difference e.g. in case of T4k, query is taking 22 mins approx, and it's taking 42 mins in case of char(600), taking almost double time.
No. of allocated data blocks are same, table size is same of 760MB, Why double time difference between char(2000) and char(600). Expert opinion required too please.
I'll highly appreciate the reasons for these cases.
---- Please do not think abt the overall results execution time bcoz i'm doing it on my lap top that has 1gb of ram in total, db=11g standard edition. I know it's v/slow but my concern is comparsion
Oracle Certified DBA