Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unhappy Unanswered: Newbie..need help with year and month manipulation.

    heres my code..I m trying to generete 6 months of data in orcale..the dataset is huge..in billions..what query should i use to extract only last 6months from the database..pls help...
    my code

    select
    a.sku_id,
    a.subclass_id,
    b.trans_dt
    from enttsku a, cirttrn b
    where b.trans_dt between to <WHT SHOULD I USE TO GENERATE JUST LAST 6 MONTHS OF DATA > and a.sku_id = b.sku_id
    and a.subclass_id in (915,287,701,719,256)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    where b.trans_dt >= add_months(sysdate,-6)

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    With that number of rows you really should be using Oracle Partitioning. Since you are querying transaction data I would assume it's partitionable by year+month, or maybe year+quarter. When you issue the query, Oracle will detect which partitions apply and will prune the other partitions from the query plan. Much faster.

    Just a thought. Looks like Andrew already answered the real question.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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