Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    6

    Unanswered: Insert taking more time

    Hi All,

    i am inserting data into my time dimension table by using the following query

    insert into time_dimension (
    SELECT SALE_DATE AS TIME_ID ,
    to_char(SALE_DATE,'Day') DAY,
    to_char(SALE_DATE,'Month') MINTH,
    to_char(SALE_DATE,'yyyy') YEAR,
    to_char(SALE_DATE,'HH24') HOUR_OF_DAY,
    to_char(SALE_DATE,'MI') MIN_OF_HOUR,
    to_char(SALE_DATE,'DD') DAY_OF_MONTH,
    to_char(SALE_DATE,'MM') MONTH_OF_YEAR,
    to_char(SALE_DATE,'IW') WEEK_OF_YEAR,
    'Q'||to_char(SALE_DATE,'Q') QUARTER,
    to_char(SALE_DATE,'Mon-YYYY') MONTH_YEAR,
    to_char(SALE_DATE,'DD-Mon-YYYY') FISCAL_PERIOD,
    TRUNC(SALE_DATE) DAY_DATE,
    TO_CHAR(SALE_DATE,'HH24:MIS') TIME_PERIOD,
    TRUNC(SALE_DATE,'MONTH') MON_YEAR,
    to_char(SALE_DATE,'DD-Mon-YYYY HH24:MIS') DATE_TIME
    from
    (
    select distinct sale_date FROM sales WHERE SALE_DATE is not null
    MINUS
    SELECT TIME_ID FROM time_dimension
    )
    )

    the sale_date in the Sales table can contain duplicate data.
    When i executed the above query , with sales table containing 10 Lakhs records., its taking 2 min time.
    i also executed runstats for the above tables.
    Is there any way to optimize the above query??


    Thanks in advance,
    Kashyap

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since NOBODY can optimize SQL just by looking at it, we need a few more details.
    1) DDL for all tables & indexes
    2) EXPLAIN PLAN
    3) output from SQL_TRACE & tkprof
    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
    Mar 2012
    Posts
    6
    Hai Anacedent,

    Thanks for quick reply.
    here are the table scripts.

    CREATE TABLE time_dimension (
    "TIME_ID" date NOT NULL,
    "DAY" VARCHAR(30),
    "MONTH" VARCHAR(25),
    "YEAR" number(5,0),
    "HOUR_OF_DAY" number,
    "MIN_OF_HOUR" number,
    "DAY_OF_MONTH" number,
    "MONTH_OF_YEAR" number,
    "WEEK_OF_YEAR" number,
    "QUARTER" VARCHAR(2),
    "MONTH_YEAR" VARCHAR(15),
    "FISCAL_PERIOD" VARCHAR(30),
    "DAY_DATE" date,
    "TIME_PERIOD" VARCHAR(20),
    "MON_YEAR" date,
    "DATE_TIME" VARCHAR(45)
    ) ;

    ALTER TABLE time_dimension
    ADD PRIMARY KEY ("TIME_ID");

    ==============

    CREATE TABLE sales (
    id number NOT NULL,
    sale_date date,
    ..
    ..
    ..
    ..
    ) ;

    ALTER TABLE id ADD PRIMARY KEY ("TIME_ID");

    create index idx_001 on sales(sale_date);

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One thing which might help is to use the compress option on idx_001 so the index doesnt store repeated values which are the same. This might make your index smaller and thus speed up your query. This assumes there are a reasonable number of duplicate values for sales.sale_date.

    Alan

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just curious, why are you making the time_dimension table when the information can be produced at will by using the sale_date in sales.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    How long does this query take?
    Code:
    select distinct sale_date FROM sales WHERE SALE_DATE is not null 
    MINUS
    SELECT TIME_ID FROM time_dimension

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    More....
    It looks like you are trying to continuously add dates to your time dimension based on the sales records.

    I am guessing that using the MINUS will degrade over time because Oracle must solve the first select, then the second select and then eliminate results from the first select based on the second.
    Please send the explain plan for the statement.

    Also, try using
    Code:
    select distinct sale_date 
     from sales where sale_date is not null
       and not exists(select 'x' from time_dimension where time_id = sale_date)
    Another you could also try depending on your requirements, would be:
    Code:
    select distinct sale_date 
     from sales where sale_date > (select max(TIME_ID) from time_dimension)
    This statement should be the fastest, but assumes that you will never receive historical sales data. For example, you would have to receive sales data up to end of month and then run your statement. All subsequent sales data would have to be for dates newer than that end of month.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you have to have a time_dimension table, why not use merge which is very efficient.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Feb 2005
    Posts
    57
    Why populate on the fly? If you were to follow typical DW modelling techniques then date and time would be held in separate tables and populated once at system setup.

    Date Dimension: at a daily grain this would hold 365 rows for each year (366 for a leap year). That’s just 36,525 rows for 100 years.

    Time Dimension: at a grain of one row per second this table would hold just 86,400 rows - i.e. the number of seconds in a day.

    Regards,
    Outrider

Posting Permissions

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