Roels Blog

Last week we encoutered the problem that reading a 10,000 record csv-file took 45 minutes from a location in Belgium. From The Netherlands it took about 20 minutes. The Application Server and Database Server is located in the UK. The difference is due to available band width. The performance from both locations were not acceptable. How did we solve this problem? First we stripped down the code so that only the lines in the csv-file were read and no validation and processing (insert into table) took place. From Belgium only this took even 17 minutes! Then we changed the code so that the file got transferred to the Application Server (using webutil_file_transfer) and replaced the use of the webutil package client_text_io with 'regular' text_io. The results were astonishing: The transfer of the file is done in a split second and just reading the file line-by-line took less than a minute! After adding the validation and processing the process could finish in just 2 minutes. So a li

Let's consider the contents of the famous dual table: SQL>select * from dual; D - X SQL>select rownum from dual; ROWNUM ---------- 1 Now let's try to get multiple rows returned form dual: SQL>select rownum from dual connect by rownum < = 5; ROWNUM ---------- 1 So there's still one row, whichs results in a rownum of 1. Now let's ask the max of rownum: SQL>select max(rownum) from dual connect by rownum < = 5; MAX(ROWNUM) ----------- 5 Kinda strange isn't it? Now let's try to get these 5 rows: SQL>select * from (select rownum from dual connect by rownum < = 5); ROWNUM ---------- 1 2 3 4 5 Another strange feature is that in TOAD select rownum from dual connect by rownum < = 5 returns 5 rows (and in SQL*Plus only one as you can see above). Does this kind of behaviour also occurs on other tables/views? In my application I've got one materialized view, so