Oracle Points - Part-1

1.   Aggregate functions like Min, max, count, avg ignores null values in the columns

select count( department_id) from hr.employees

Gives 106 records where as we actually have 107 records.

2.   On external table we can’t perform the following.

a.   DML operations

b.   Index, Constraints, Trigger creations

Error “ORA-30657: operation not supported on external organized table”

3.   Single row functions

a.   They can be used in SELECT, WHERE, and ORDER BY clauses.

b.   They can modify the data type of the argument that is referenced.

c.    They can accept a column name, expression, variable name, or a user-supplied constant as arguments.

4.   Error - ORA-25154: column part of USING clause cannot have qualifier

5.   Correlated Sub Query

a.   The nested query executes after the outer query returns the row.

b.   Each row returned by the outer query is evaluated for the results returned by the inner query.

6.   GROUPING function is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.

7.   In the same GRANT statement we can’t put more than one table

8.   Flashback version queries provide a mechanism for viewing data as it existed at a particular point in time (a timestamp or SCN). With 10g Release 1, Oracle has extended flashback query to enable us to view different versions of our data in a given range between two timestamps or SCNs.

1.   ROLLUP produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.

2.   Error - ORA-02261: such unique or primary key already exists in the table occurs when we try to create primary key as well as unique key constraint on the same table column

SQL Statement Execution Flow

Suppose we have a following SQL statement.

select  employee_id, first_name

from hr.employees

where employee_id = 120

order by first_name

This statement will be validated in the following order

• Table list in the FROM clause
• Order BY clause columns, expressions
• Where clause conditions
• Finally Select list

Similarly for the following SQL

select employee_id, first_name, sum(salary)

 from hr.emp_data

where salary <> 0

group by employee_id, first_name

having sum(salary) > 0

order by employee_id, first_name

• Table list in the FROM clause
• Order BY clause columns, expressions
• Having Clause
• Group By Clause
• Where clause conditions
• Finally Select list

PARALLEL hint in Oracle

PARALLEL hint allows to create multiple slaves process in the background to perform the single task.

To completely read a table, optimizer uses FULL table scans.

By Using PARALLEL hint in case of FULL table scans we will get better query performance.

 Check for the number of CPU's available for your DB instance using the following

select value from
v$parameter
where name = 'cpu_count'

say output is 4

Example:

Create a table

create table parallel_test (id number, des varchar2(100));

insert sample data

insert into parallel_test (select level, 'desc-'|| level from dual connect by level <=500000);
commit;

Now if you compare the explain plans shown in the below image we can see the performance difference with and with out PARALLEL hint