Oracle Tips and Tricks

 1) When modifying a database objekt remember to check for all other objects that might have been made invalid due to this change. All objects that reference the changed object will need to be recompiled. (SELECT * FROM ALL_OBJECTS WHERE STATUS = ‘INVALID’)

2) Try to replace NOT IN statements in queries that take a long time to run with either EXISTS or outer joins. Refer to the Oracle Complete Reference book on how to do this. 

3) For slow queries you can also check to see if the table has appropriate indexes on its columns. 

4) Remember that if you use a SELECT statement with an implicit cursor in a procedure then it must return a record or an exception error will be raised. In general it is better to use explicit cursors if you are not sure that a row will result. Only use implicit SELECT statements when you are doing something like a COUNT on a column. Explicit cursors are also faster because they don’t have to do two queries. One to see if a missing data exception should be raised and one to get the actual data. 

5) You cannot say "where column = NULL" in Oracle. You must say "where column is NULL" or "where column is not NULL."

 6) Don’t use a function directly in an IF statement. Instead set a variable equal to the function and then use that variable in the IF statement. Oracle seems to have problems if you don’t do it this way. 

7) If a PL/SQL variable is going to be used to store a value from a database column then try to use the table.column_name%TYPE syntax for declaring this variable. This way the variable will automatically be the correct type if the column’s data type or length is updated. 

8) If you use the DISTINCT clause in a select statement then you are only allowed to ORDER BY one of the selected values. Ordering by a column that is not selected will return the ‘Not a SELECTed expression...’ error message.

 9) DECODE is a very useful command available in Oracle. The syntax is: DECODE( value, if1, then1, elseif2, then2, ..., else ) So instead of writing a statement like: SELECT * FROM COLORS WHERE ((COLOR = ‘RED’ AND TYPE = ‘BRIGHT’) OR (COLOR = ‘BLACK’ AND TYPE = ‘DARK’) OR (TYPE = ‘ALL’ ) ); We can write: SELECT * FROM COLORS WHERE COLOR = DECODE(TYPE, ‘BRIGHT’, ‘RED’, ‘DARK’, ‘BLACK’, COLOR ); 

10) Remember that a cursor FOR loop can be very efficient. Instead of having to open a cursor, fetch the records, exit from the loop, and close the cursor you can use a cursor FOR loop instead and it will do all of that for you. An added benefit is that you can refer to the items that are being selected as elements of a record instead of having to declare variable and fetch the data into them. Example: CURSOR GET_NAME IS SELECT name FROM customer; FOR record IN GET_NAME LOOP IF record.name = ‘Greg’ THEN NULL; END LOOP; Otherwise you would declare the cursor and do something like this: OPEN GET_NAME; LOOP FETCH GET_NAME INTO sNameTemp; EXIT WHEN GET_NAME%NOTFOUND OR GET_NAME%NOTFOUND IS NULL; IF record.name = ‘Greg’ THEN NULL; END LOOP; CLOSE GET_NAME; 

11) Oracle has added an extension to the SQL language that allows for selecting data that is related in a hierarchal or tree-like manner. Here is a sample select statement from the submission application that fetches data from the Xs or Pr table. LEVEL is a pseudo column like ROW_ID and it holds the value for at what "depth" we are in the tree structure with the current row. SELECT XOP_XS_OR_PR_CODE, XOP_XS_OR_PR_NAME, XOP_PARENT_CODE, LEVEL FROM XS_OR_PR_TYPE START WITH XOP_XS_OR_PR_CODE = 0 CONNECT BY XOP_PARENT_CODE = PRIOR XOP_XS_OR_PR_CODE ORDER BY LEVEL, XOP_XS_OR_PR_NAME, XOP_XS_OR_PR_CODE 

12) The query optimizer that oracle uses bases its decisions on the statistics that it has available about the tables used in the query. Row size, row count, etc.... These statistics are not updated automatically by oracle during normal database usage. The DBA must explicitly tell oracle to update these statistics for each table and index when the composition of the table’s data has changed. To do this the DBA uses the ANALYZE command. This command can also be used to check for data corruption by using the VALIDATE STRUCTURE option. If Oracle does not have current statistics for a table used in a query it may choose a very inefficient access method to determine the result set. The oracle procedure DBMS_UTILITY.ANALYZE_SCHEMA can be used to run analyze on an entire database schema. 

13) Use Varchar2() data types for all string columns. Varchar2 is Oracles variable length string. Currently Varchar2 is the same as Varchar but It is not recommended that Varchar be used because it might be changed in a later version of Oracle.