oracle 11g new concepts

Oracle 11g new concepts

Adaptive cursor sharing :

depends on the selectivity of a query different execution plan is created for a single sql query with bind variable .

This concept is introduced in 11g and prior to that a new concept called peeking was used where the sameplan will be generated and used for queries with the bind variables.

when the selectivity differs very much this will increase the performance many fold:

eg:

first query returns 50 rows.
2nd query returns 10000 rows out of 12000

so an index scan is needed for the first and a full table scan for the second.

in adaptive cursor sharing two different plans will be generated when running both the queries seperately.

two types:

bind sensitive:
bind aware

The cursor will be bind sensitive if the selectivity  remains the same for the queries.
the cursor will become bind aware when  there needs 2 seperate plans needed for both the plans .
whe a new plan is generated the old plan will become unusable .

Cursor merging :

when both the cursor is open for the same plan it will merge both into a single cursor to reduce the memory usage .

Baselines:

This is yet another concept introduced in 11g .when it generates the execution  plan ,it compares the plan with the old ones and if it is better than the old one ,it will add this in the list .if the performance is not better then the old plan is used .this is called as baselines

Large Objects

There are four data types  in this catagory

Clob -->character large objects (cannot store images)
Blob -->binary large objects(can be used to store anything like image,media files etc)
Nclob-->multibyte character set
Bfile -->this also can store any objects like image etc
clob,blob,nclob are stored inside the database.
Bfile is external to the database and the table only contains a pointer to the location of the file.
Eg:
crate table t1(col1 bfile);
insert into t1 values(bfilename('p_dir','a.txt'));
select * form t1;
p_dir/a.txt will be the o/p

The Bfile is read only .
The others large objects are editable.

LONG:

A table can have only one long column.
The datas are stored in the same block .

LOB:

Any number of LOB columns possible.
The datas can be kept anywhere in the tbl space in any other segments also.

Aggregate and Analytic Functions in oracle

Aggregate functions :

1.Aggregate functions return one value within a group if you specify the GROUP BY clause in the query and it returns a single value if group by is not specified in the oracle statements.

eg :

select department_id,sum(salary)
from department
group by department_id;

This would return one row for each department id avalable .

another example is

select sum(salary) from department;

This would return just one row and that is the count .

There are several aggregate functions available in oracle .lets glance at them briefly