new stuff in oracle

I read this something which is new to me :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1273740600346432580

Driving Site hint

I will first define DRIVING SITE Hint ( Oracle Performance Tuning manual ) :

DRIVING_SITEThe DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-basedoptimization.driving_site_hint::=where table is the name or alias for the table at which site the execution shouldtake place.
For example:SELECT /*+DRIVING_SITE(departments)*/ *
FROM employees, departments@rsite

WHERE
employees.department_id = departments.department_id;

If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there. With the hint, the rows from employees are sent to the remote site, and the query is executed there, returningthe result to the local site.This hint is useful if you are using distributed query optimization.

and then finally for some more clarity please read -

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:456820211101

and please add your views..i will update this with more examples and real life examples.

useful scripts

- In this post ,I will put several tiny tiny scripts, which will be useful for many developers...most of these scripts are avaiable in books, different places in the oracle world , metalink..but this is an attempt to create a group of scripts which will be helpful to oracle developers and performance tuniners..
1) How to get a formatted easiet ( in my experience) explain plan : - Source of this script is
askTom - http://asktom.oracle.com/pls/asktom/f?p=100:1:3230070367714767

set serveroutput on size 1000000
column plan_plus_exp format a108 ( You can increase it as per your requirements)
set linesize 131 ( You can increase/decrease it as per your requirements)
set autotrace traceonly explain
--Here goes your SQL

and thats it...you will see the nice explain plan
2) How to turn the trace on and locate your tracefile in a easy way
alter session set tracefile_identifier='abc' ;
alter session set statistics_level = 'ALL' ;
alter session set sql_trace=true ;
--run your SQL or program

then
exit ;
the go to your udump directoy ( to see it from SQL plus - use show parameter udump )

and there look for ls -lrt *abc*.trc and thats it.

also to take a trace of level 12 then please use this
alter session set events '10046 trace name context forever, level 12';
- How to see chained rows count
select chain_cnt from user_tables where table_name = 'T';
Thank You and Please feel free to add the scripts in this repository.

index rebuild ...a long story

- Almost all big shops spent quite some time, resources and "outage" on reorganization of the tables and then of course indexes on those table. In this blog (which will take quite some time and effort to complete and make it useful for community ) , I will publish my experience , I will point you all to the best known discussions on web and many more things..

To start with please read this , understand it.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35336203098853
this is a big post and discussion in the above POST of askTom . You take your call...you understand and tell here..what is your experience..of course backup with data..that will make it more useful.
How about the link:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6601312252730
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2913600659112#24034747883219



Thanks
Ajeet

How to use an index on a nullable field - in Oracle database

If You have a table T and there is a field x which is nullable then an index on that column (b*tree) index would not be used by oracle in a query like below

select * from t where x = :bv1.

but there is a workaround for this

you can create an index like this

create index t_b1 on t(0,x) ;

then oracle will use that index.