The problem with this approach is its nondeterministic behavior: the values from the first execution affect all executions. The last option is REOPT(ONCE) which will peek the bind parameters for the first execution only. That is effectively turning off execution plan caching for that statement. REOPT(ALWAYS) will tell the optimizer to always peek the actual bind variables to produce the best plan for each execution. The default is NONE, which produces a generic execution plan and suffers from the problem described above. It just proves that the database can resolve these conditions.ĭB2 uses a shared execution plan cache and is fully exposed to the problem described in this section.ĭB2 allows to specify the re-optimization approach using the REOPT hint. |*2 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | 1 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | WHERE( subsidiary_id = NULL OR NULL IS NULL )ĪND( employee_id = NULL OR NULL IS NULL )ĪND( UPPER(last_name) = 'WINAND' OR 'WINAND' IS NULL ).
If we do not use bind parameters but write the actual values in the SQL statement, the optimizer selects the proper index for the active filter: SELECT first_name, last_name, subsidiary_id, employee_id It creates the generic execution plan due to the use of bind parameters so it can be cached and re-used with other values later on. It is not that the database cannot resolve the “smart” logic.
Apex sql indexes full#
Predicate Information (identified by operation id):ġ - filter((:NAME IS NULL OR UPPER("LAST_NAME")=:NAME)ĪND (:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID)ĪND (:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID))Īs a consequence, the database uses a full table scan even if there is an index for each column. |* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 | The database needs to prepare for the worst case-if all filters are disabled:. The database cannot optimize the execution plan for a particular filter because any of them could be canceled out at runtime. Nevertheless it is one of the worst performance anti-patterns of all. The use of NULL is even in line with its definition according to the three-valued logic of SQL. It is a perfectly reasonable SQL statement. Whenever a filter isn’t needed, you just use NULL instead of a search term: it disables the condition via the OR logic. All possible filter expressions are statically coded in the statement. The query uses named bind variables for better readability. WHERE ( subsidiary_id = :sub_id OR :sub_id IS NULL )ĪND ( employee_id = :emp_id OR :emp_id IS NULL )ĪND ( UPPER(last_name) = :name OR :name IS NULL ) SELECT first_name, last_name, subsidiary_id, employee_id It is still possible to write a single query that covers all cases by using “smart” logic. The application allows searching for subsidiary id, employee id and last name (case-insensitive) in any combination. This practice does more harm than good if the database uses a shared execution plan cache like DB2, the Oracle database, or SQL Server.įor the sake of demonstration, imagine an application that queries the EMPLOYEES table. Nevertheless there is a widely used practice that avoids dynamic SQL in favor of static SQL-often because of the “ dynamic SQL is slow” myth. ORDER BY ius.user_seeks + ius.user_scans + ius.I make my living from training, other SQL related services and selling my book. ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id Ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed get most used tablesĭb_name(ius.database_id) AS DatabaseName, Index usage stats are going to be your best option, for determining heaviest hit tables and indexes.
0 kommentar(er)
