Author: tom

What is the size of an Oracle table? A very rough approximation is to multiply the number of rows with the average row length in bytes. Fortunately, Oracle directly provide you with such information. Take this query: select table_name , num_rows , avg_row_len ,to_char((num_rows * avg_row_len)/1000000,’9999999D999′,’NLS_NUMERIC_CHARACTERS=”. ”’) as sizeMB , blocks from dba_tab_statistics where owner…

Oracle has a nice possibility to calculate running totals. Assume we have a sales system where transactions are stored over time. We would like to know the running total – what is sold until a certain transaction. The code to calculate this is: SELECT trans_id,to_char(trans_date,’dd-mm-yyyy hh24:mi:ss’ ) trans_date , product_code ,qty* price amount, sum(qty* price)…

Once, I wrote a completely useless programme – see below. I like to retain it as I spent quite some time on it. But it is absolutely useless. The idea it that we have some kind of a counter that is incremented by 1 if a subsequent record has the same value for a particular…

Oracle has 3 analytical functions to rank data. They provide approximately the same results, but with little deviations. First the code: select salary, rank() over (order by salary) rank, dense_rank() over (order by salary) dense_rank, row_number() over (order by salary) row_number from hr.employees order by salary; Inspecting the results shows the deviations. Look at the…

In previous versions of Oracle, we had to retrieve a top10 rows via a sub query. In the sub query, we ordered and retrieved the data whereas in the main query, we could retrieve 10 top rows. Something like: select * from (select * from hr.employees order by salary desc) where rownum <=10; Oracle 12…