Design matters

One of our customers has asked us to review the performance of one of their legacy data warehouses. The daily data load takes far too long. There seems to be a lot we can suggest here to improve things - for a start, we need move to an incremental load and not the complete reload of every table! Even the tables that won't change are re-built, one example is the time dimension table, here we have 40 years of dates at day level denormalized to around 30 columns, the date range is hard-coded into the table create (and this goes 15 years into the future....) The other problem is that the original developers seem to have had little idea how Oracle works and what is in-built as functions; finding the date of the first of a month is coded as taking the month and year from a date (as two VARCHAR2 strings) and appending them to '01-' to make a date string then converting back to a date - whatever happened to TRUNC(p_date,'MM'), simple, to the point and only one line of code? Likewise why code a hexadecimal to decimal conversion in 20 lines of if statements (and not even CASE or DECODE) when TO_NUMBER(p_hex_string, 'XX') does the job and I don't have all those lines of code to maintain.
And as for the idea of writing a procedure to insert one row into a table and call it for each iteration of a cursor loop... just don't go there.