Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004

    Unanswered: writing the efficient code


    I have a script which inserts and updates the columns of a table called ctl_date_format. This table contains attributes to store the date values for a calendar date (month_number, month_name, week_number, week_name etc) from year 1967 to 2009 and uses oracle date functions to acheive the values.
    After inserting the data, it updates indicators (ex:last_business_day_of_the_motnh) by checking the calendar date against a business dates (from a different table). I want to write in more efficient way.

    Attached the code for reference.

    I would really appreciate if someone can give me an idea how to write this code more efficient.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Please post EXPLIAN_PLAN using appropriate "code" tags
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Perhaps just a few words ...

    There's no need to use cursors - all they do is a simple SELECT statement which counts records. This could easily be done using, well, that simple SELECT statement directly, without opening cursor, fetching data from it and closing it.

    Commiting after every n records isn't a very good idea - it can lead to a snapshot too old error. However, as you have commented those lines, that really isn't a problem here.

    As for your basic task - inserting data into a table - I guess that LOOPing through all dates between requested start and end date seems to be OK. Perhaps, just perhaps this could be done without using loop, but in a single statement using CONNECT BY LEVEL - all you do is, if I'm not wrong, just one way of row generating techniques. I never tested it, but ... you might try if you have time.

    Updates use subqueries - could you avoid one of them (the largest one) using, for example, such a way: first update indicators for holidays and non-working days, while the rest (if all indicators must be known) can then be updated using a code like this:

    UPDATE table_name SET indicator = 'some_indicator'
    WHERE indicator IS NULL; --> instead of WHERE something IN (SELECT ...)

  4. #4
    Join Date
    Aug 2004
    Thank you very much for the advise.

Posting Permissions

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