Bushy plan vs left-deep

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  March 21, 2013 at 2:09 am

  #291214

  I have read about Bushy plan and left deep in some articles. Bushy plan will consume more memory as it runs parallely. is it advisable to write query which uses bushy plan to resolve?

  Apart from CTE, the below code will also use bushy plan or not. right?

  select * from

  (select * from client a,security_account b where a.cli_id = b.cli_id)a,

  (select * from client a,security_account b where a.cli_id = b.cli_id)b

  where a.cli_id = b.cli_id

  Under which circumstance, we have to write a query which uses bushy plan?

  karthik

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 21, 2013 at 3:02 am

  #1599261

  karthik M (3/21/2013)

  ---

  I have read about Bushy plan and left deep in some articles. Bushy plan will consume more memory as it runs parallely. is it advisable to write query which uses bushy plan to resolve?

  Apart from CTE, the below code will also use bushy plan or not. right?

  select * from

  (select * from client a,security_account b where a.cli_id = b.cli_id)a,

  (select * from client a,security_account b where a.cli_id = b.cli_id)b

  where a.cli_id = b.cli_id

  Under which circumstance, we have to write a query which uses bushy plan?

  This article by Fabiano Amorim[/url] distinguishes between left-deep and bushy plans very well and explains why the optimiser will not usually consider a bushy plan. It includes an easy to follow example of forcing a bushy plan to obtain a significant performance lift. This is one of the two reasons you might force a bushy plan. The other reason is correctness or accuracy, and Paul White has a very good example here.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden