Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your example code is so simple that there will be little difference, but in that case the static version would most likely execute better.</p> <p>The main reason to use dynamic SQL for performance is when the SQL statement can vary in a significant way - i.e. you might be able to add extra code to the WHERE clause at runtime based on the state of the system (restrict by a sub-query on Address, if Address entered, etc). </p> <p>Another reason is that sometimes using Bind variables as parameters can be counter-productive. </p> <p>An example is if you have something like a status field, where data is not evenly distributed (but is indexed).</p> <p>Consider the following 3 statements, when 95% of the data is 'P'rocessed</p> <pre><code> SELECT col FROM table WHERE status = 'U'-- unprocessed AND company = :company SELECT col FROM table WHERE status = 'P' -- processed AND company = :company SELECT col FROM table WHERE status = :status AND company = :company </code></pre> <p>In the final version, Oracle will choose a generic explain plan. In the first version, it may decide the best plan is to start with the index on status (knowing that 'U'nprocessed entries are a very small part of the total).</p> <p>You could implement that through different static statements, but where you have more complex statements which only change by a couple of characters, dynamic SQL may be a better option.</p> <p><strong>Downsides</strong></p> <p>Each repetition of the same dynamic SQL statement incurs a soft parse, which is a small overhead compared to a static statement, but still an overhead.</p> <p>Each NEW sql statement (dynamic or static) also incurs a lock on the SGA (shared memory), and can result in pushing 'old' statements out.</p> <p>A bad, but common, system design is for someone to use dynamic SQL to generate simple selects that only vary by key - i.e.</p> <pre><code>SELECT col FROM table WHERE id = 5 SELECT col FROM table WHERE id = 20 SELECT col FROM table WHERE id = 7 </code></pre> <p>The individual statements will be quick, but the overall system performance will deteriorate, as it is killing the shared resources.</p> <p>Also - it is far harder to trap errors at compile time with dynamic SQL. If using PL/SQL this is throwing away a good compilation time check. Even when using something like JDBC (where you move all your database code into strings - good idea!) you can get pre-parsers to validate the JDBC content. Dynamic SQL = runtime testing only.</p> <p><strong>Overheads</strong></p> <p>The overhead of execute immediate is small - it is in the thousandths of a second - however, it can add up if this is inside a loop / on a method called once per object / etc. I once got a 10x speed improvement by replacing dynamic SQL with generated static SQL. However, this complicated the code, and was only done because we required the speed.</p>
    singulars
    1. This table or related slice is empty.
    1. POStatic vs dynamic sql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAPC
    1. USJulesLt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POStatic vs dynamic sql
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COJulesLt has good points in his answer and hints at something in discussing overhead that needs to be said explicitly - putting aside bind variable considerations, parsing, etc., EXECUTE IMMEDIATE in a PL/SQL block will always have a disadvantage over an equivalent PL/SQL call because the execution will involve a context switch to the SQL engine.
      singulars
      1. PO
      1. USdpbradley
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload