Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: Selection criteria for nested SQL view

    If you are looking for an easy question, please move on to the next question.

    My overall goal is to retrieve the data faster from a SQL view.
    My overall question is how detailed do I make the selection criteria for a nested view if I am going to add more restrictive selection criteria to the outer view.

    Here's the setup ...

    Case 1: selection criteria for date column is 1 year

    CREATE OR REPLACE VIEW nested_view
    ( ct_date, a, b, c )
    AS
    SELECT table_x_date, a, b, c
    FROM table_x
    WHERE table_x_date BETWEEN '1/1/2006' and '12/31/2006'

    UNION ALL

    SELECT table_y_date, d, e, f
    FROM table_y
    WHERE table_y_date BETWEEN '1/1/2006' and '12/31/2006'
    ;


    CREATE OR REPLACE VIEW outer_view
    ( ct_date, new_col )
    AS
    SELECT nv.ct_date, t1.new_col
    FROM nested_view nv
    JOIN table_1 t1
    ON nv.a = t1.a
    WHERE nv.ct_date = '6/22/2006'

    UNION ALL

    SELECT nv.ct_date, t2.new_col
    FROM nested_view nv
    JOIN table_2 t2
    ON nv.b = t2.b
    WHERE nv.ct_date = '6/22/2006'
    ;

    Question 1: When I run outer_view will a year's worth of data be retrieved from nested_view first, and then searched through for the data for the one day?
    Question 2: ... or will the restriction of 1 day be added to the selection criteria for nested_view before any data is retrieved?

    Question 3: If the answer to question 2 is 'yes', then possibly the selection criteria for the date column in nested_view could be removed (as is done below) with no adverse affect on performance. Correct?

    Case 2: no selection criteria for date column

    CREATE OR REPLACE VIEW nested_view
    ( ct_date, a, b, c )
    AS
    SELECT table_x_date, a, b, c
    FROM table_x

    UNION ALL

    SELECT table_y_date, d, e, f
    FROM table_y
    ;


    I am in the process of looking at the EXPLAIN PLAN info to get some clues there.

    Thanks for your help.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Oracle will attempt to merge the SQL from all views. I would remove the date range and just keep it at the single date you are looking for.

    Simple answer to improve performance: Index table_x_date and table_y_date!
    And if your tables contain millions of rows then consider partitioning on the date values. I would think an anual partition would be appropriate.

    Also, I hope your date values are Oracle Date columns and not varchar columns. If they are varchar, then your "between" criteria wouldn't work correctly anyway. Always store dates as dates and not formatted strings. And if they are dates, then don't do "between '01/01/2007' and '02/01/2007'", rather do "between to_date('01/01/2207', 'dd/mm/yyyy') and ....", otherwise Oracle won't use the index even if one exists.

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Smile

    Thanks.

    When you say 'Oracle will "attempt" to merge the SQL from all views', are you saying that sometimes Oracle will not be able to merge the SQL?

    There are indexes where the table_x_date and table_y_date columns are the leading key columns.

    Concerning the date columns they are Oracle Date columns and not varchar columns. Just trying to make the SQL easy to read.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I dont know if view merging is adecuate in this situation, as I see you're only interested in predicate pushing (i.e. pushing down the predicate on the dates right down on the table itself) and yes, there are time when Oracle is unable to predicate push (i.e. when it could change the 'answer', the use of rownum > 1, etc.).

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    When using a union in the nested view, you should use all possible filters at that level because Oracle will attempt to get all rows at that level, then sort them and then compare to make sure they're unique (thats lots of work). To see this in action, create 2 tables, create a view something like
    select * from tablea where dtcol between sysdate - 60 and sysdate
    union
    select * from tableb where dtcol between sysdate - 60 and sysdate

    then write a select from the view something like

    select * from viewa where dtcol = sysdate

    and run explain plan on this sql. As you will see, it can be nasty if tables a and b are large.

    PS. I think JMartinez is right. predicate push... thats what it's called.... From testing here it seems that when performing union all Oracle is not able to predicate push. (at least in the example above).

    Some resources that make for interesting reading:
    http://people.aapt.net.au/roxsco/tun...redicates.html
    http://www.oracle.com/technology/ora...ch_tuning.html
    http://www.oracledba.co.uk/tips/forcing_order.htm
    Last edited by dayneo; 05-01-07 at 13:35.

Posting Permissions

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