ORACLE HINT REFERENCE PARALLEL Scanning full table with parallel degree of 8. Parallel always assumes full table scan. SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 8 ) */ last_name FROM employees hr_emp; PARALLEL_INDEX The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned [...]
Archive for the ‘Oracle SQL’ Category
1 May
Custom Script to Identify Invalid Packages
Identifying Invalid Objects SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = ‘INVALID’ ORDER BY owner, object_type, object_name; Manual Approach ALTER PACKAGE my_package COMPILE; ALTER PACKAGE my_package COMPILE BODY; ALTER PROCEDURE my_procedure COMPILE; ALTER FUNCTION my_function COMPILE; ALTER TRIGGER my_trigger COMPILE; ALTER VIEW my_view COMPILE; DBMS_DDL EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’); EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’,’MY_SCHEMA’,’MY_PACKAGE’); EXEC [...]
29 Mar
String Functions:INSTR,SUBSTR, REPLACE,TRANSLATE
Built-in functions/operators available for manipulating Strings The most useful ones are LENGTH, SUBSTR, INSTR, and ||: LENGTH(str) returns the length of str in characters. SUBSTR(str,m,n) returns a portion of str, beginning at character m, n characters long. If n is omitted, all characters to the end of str will be returned. INSTR(str1,str2,n,m) searches str1 beginning [...]
26 Mar
Constraints
Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including: Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to [...]
12 Mar
SQL CASE Statement
he syntax for the case statement is: CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 … WHEN condition_n THEN result_n ELSE result END expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, … condition_n) select [...]
10 Mar
Managing Tables
Creating a Table The CREATE TABLE command is used to create relational tables or object tables. Relational table: This is the basic structure to hold user data. Object table: Is a table that uses an object type for a column definition. An object table is a table that is explicitly defined to hold the object [...]
27 Feb
Recursive Calls
Recursive calls is a call(sql statement) that must be completed before the completion of user’s SQL statement. Basically, recursive calls are generated by Oracle internal sql statements to maintain changes to tables for internal processing. Reasons for Recursive Calls Recursive calls can be generated due to following reasons: Dictionary cache is too small resulting in [...]
27 Feb
Oracle SQL Execution Steps
All Oracle SQL statements must be processed the first time that they execute (unless they are cached in the library cache). and SQL execution steps include: A syntax check – Are all keywords present "select . . . from", etc . . A semantic check against the dictionary – Are all table names spelled correctly, [...]
