SQL tuning basic

SQL Tuning is a experience based technique , only when you experience it in your work then you will know it and summarise into your brain . it's a gradual process of accumulation . Here, I will share some basic techniques I summarised and used in my work;
Notes : please open the explain plan to check the result if you're not sure.

count(*) and count(column) , who is faster ? 

The column offset is the key factor of query speed. the righter the column located , the more cost the query make will be. So count(*) has no relation with columns . count(*) is the fastest. count(the last column) will be visited with the slowest speed. So try to put the column visited least frequently behind is a wise choice.

not in() and not exist(), who is faster ?

In Oracle 10G, not in() use the filter algorithsm , not exist use hash join anti algorithsm which is faster. However in 11G, both are the same now.

Optimise global temporary table

Previsously , the prodcution system log increase enormously , and system IO increase as well. After checking the log , we noticed that the new module installed last night with a sql "delete from t_mind" was runned hundreads of thousand times . so DBA terminated this application. when we debug , we notice that the t_mind is a temporary table which stored some temporary data in convenience of other business logics. After the business logics,  this table will be cleared each time. So we may ask "do we really need to delete so many times?" The cost of delete is huge and occupy lots of rollback segments generating lots of logs as well. Actually , there is a table called global temporay table ( used for storing data temporarily);

session based global temporary table : when quiting this user session, all table data will be auto-cleared;

create global temporary table ljb_tmp_session on commit preserve rows as select  * from dba_objects where 1=2;
select table_name,temporary,duration from user_tables  where table_name='LJB_TMP_SESSION';

transaction based global temporary table: when transaction is commited, the table will be auto-cleared

create global temporary table ljb_tmp_transaction on commit delete rows as select * from dba_objects where 1=2;
select table_name, temporary, DURATION from user_tables  where table_name='LJB_TMP_TRANSACTION';

Partition brings efficiency

After creating the partition tables , we insert the data and then select the data, it will be faster than query from normal table without partitions

Try not to use functions

If we create a function like :" create function f_deal2....", and then we call the function in our query "select f_deal2(t1.object_name) from t1 where object_id=999" This will be very slow, try to use table joins and not use function if it can be avoided;

Try write sql in collection way not in procedure way

Insert into t select rownum from dual connect by level <= 100000; // very fast

Begin 
for i in 1 ..100000 loop
   Insert into t values(i) end loop ; commit; 

end;/  // very slow

Only fetch the columns you need from a View

Select * from view1;  // slow 			 

select object_id from view1 // fast

Create index on the columns you need

Create index idx_object_id on t( object_id,object_name); // create index
Select object_id,object_name from t where id =28; // query 2 columns with index, fast
Select * from t where id =28; // query all columns without going via index, slow

When to create/open index

Index will bring down the efficiency of insert. so try to do read/write splitting. Index will lock the table. Index will trigger a sort as well.

The fastest way to create a table(parallelsim)

Create table t nologging parallel 64 as select rownum x from dual connect by level < 10000; // close the log and use 64 cpu threads

In the end, some tips need to be given by me :

1. try not to use order by , reduce the resource waste from sorting.
2. try to use in more , use > and < less

Insert all into table1 into table2 select * from dba_tables; // multiple table insert, you can write in this way, very fast

Select max , min from (select max(object_id) max from t) a, (select min(object_id) min from t) b; // this will go via index, very fast