1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using with

Discussion in 'SQL PL/SQL' started by DTSIGuy, Jul 29, 2015.

  1. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Howdy Everybody!

    I was reflecting upon an article about the WITH feature and the ability in Oracle 12c to stack your WITHs. The result is called a "Statement Tower" by the author.

    This got me to thinking about a response from ocprep on a separate post :

    Whereupon I focused on the "performance advantage"...and THAT got me to wondering if anybody had some empirical performance data for using the WITH. And perhaps the impact of the aforementioned "Statement Tower".

    From my, admittedly narrow point of view, using a WITH and/or getting to use the Statement Tower in the near future goes a long way toward increasing the readability (and therefore maintainability) of the SQL statement.

    So I just wondering if anybody has an opinion, pro/con so I can digest it.

    Thanks all!

    CJ
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Empirical performance data (whether it demonstrates an improvement or not) isn't particularly useful. You can only determine for certain whether or not WITH provides a performance advantage for a given SQL statement by trying the statement with and without the WITH. Tim Hall provides a really good set of examples in the following article -- but even he notes: "You should assess the performance implications of the WITH clause on a case-by-case basis."

    https://oracle-base.com/articles/misc/with-clause
     
    DTSIGuy likes this.
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Not entirely unanticipated...sigh. But, many thanks for the link. I was unaware of that site and am currently trolling around. ;-)

    CJ
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Another Oracle 'luminary' who uses WITH on a regular basis is Jonathan Lewis. Depending on the example he can also use the /*+ materialize */ hint in the WITH query which can sometimes reduce costs, but that may not be entirely accurate. He discusses that here:

    https://jonathanlewis.wordpress.com/2015/07/24/subquery-factoring-9/

    You might want to search his blog for the previous 8 installments on what he calls subquery factoring.
     
    Bharat and DTSIGuy like this.
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Thanks much! ;-)