Unlike an SQL statement, the database engine need not optimize the procedure before it is executed.Īvoid using the logical operator OR in a query if possible. Procedures are compiled by the database engine and then executed. A procedure is a potentially large group of SQL statements. For the same reason, a leading wildcard %COL effectively prevents the entire filter from being used.įine tune your SQL queries examining the structure of the queries (and subqueries), the SQL syntax, to discover whether you have designed your tables to support fast data manipulation and written the query in an optimum manner, allowing your DBMS to manipulate the data efficiently.įor queries that are executed on a regular basis, try to use procedures. In this case, the COL% is used, but the %Y is thrown away. The effort to do the evaluation is too large to be considered. However, COL%Y does not further reduce the returned results set since %Y cannot be effectively evaluated. LIKE COL% is a valid WHERE condition, reducing the returned set to only those records with data starting with the string COL. Both of these can have small differences that are not obvious to the eye but that make an exact match impossible, thus preventing your queries from ever returning rows. You can avoid a full-table scan by creating an index on columns that are used as conditions in the WHERE clause of an SQL statement.īe very careful of equality operators with real numbers and date/time values.
ORACLE DATABASE TUTORIALS UPDATE
Avoid index on the tables where you have less number of search operations and more number of insert and update operations.Ī full-table scan occurs when the columns in the WHERE clause do not have an index associated with them. While using SELECT statement, only fetch whatever information is required and avoid using * in your SELECT queries because it would load the system unnecessarily.Ĭreate your indexes carefully on all the tables where you have frequent search operations. Use 3BNF database design explained in this tutorial in RDBMS Concepts chapter.Īvoid number-to-character conversions because numbers and characters compare differently and lead to performance downgrade. This all comes with lot of experience in various database designs and good trainings.īut the following list may be helpful for the beginners to have a nice database performance − It takes time to become a Database Expert or an expert Database Administrator.
0 kommentar(er)
