Learn Database Concepts

Inserting Data with DML Error Logging: When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature. To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time. DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements. -------------------------------------------------------- --  DDL for Table ATEST1 ---------------------...

Hints : Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements. Hints should only be used as a last-resort if statistics were gathered and the query is still following a sub-optimal execution plan. Example of the correct syntax for an index hint: select /*+ index(TEST_IDX IDX_OS_USR) */ * from TEST_IDX; If we alias the table (A in below case), you must use the alias in the index hint: select /*+ index(A IDX_OS_USR) */ * from TEST_IDX A; Note : Oracle decides to use weather to use this hint or not, of oracle finds that it has faster execution plan without using hint it ignores it. You might think that an index may be helpfull and provide it as hint but oracle may still ignore it. In below case you...

INVISIBLE INDEX: Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level CREATE AN INVISIBLE INDEX: CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE; lets check the newly created index : SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR'; OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY        --------      -----------                                    ------...