A good table structure can avoid all tunings.

       is the recommendation i give for this topic.

Table structure design is the first step for people to create an application. However becuase some company / people has limited knowledge on database, when their application was put in production, It has bugs and errors here and there. And the oracle is keep releasing from  8i 9i 10G 11G 12C onwards, some bugs has been fixed , some algorithums has been improved, they are still blur on that . 

One obvious case in my work,  when in 10G , the data type char and Varchar2 have some efficiency issues which make these 2 data type different . Thus DBA in oracle 10G are more apt to char rather than varchar2, However, in 11G which is the version i am current using , this problem has been fixed by Oracle , so varchar2 has a better performance than char and also can tolerate a longer String length. And char has been put in the deprecated list in later releases. When I see the table design by our DBA , they are still using char to improve certain performance. Similar cases appear very often . 

In this article , I won't dig so much on these details ,  I will share some important points on table design and how these points can affect queries.

Index Foreign key : 

If tables linked by pri-for key, then don't remember add index on foreign key.

3 ways of Oracle partitioning : 

• range partitioning
• list partitioning
• hash partitioning

3 combined ways of partitioning:

• range-range
• list-list
• list-hash

2 types of index :

• local index , index on partitons : create index .... local;
• global index, index on tables

Why do we need to create partitions?

Because , we can narrow down the scan range when we query so as to improve the speed, Also, It's easier to clean those partitons (truncate, drop, split,add,exchange), Delete will occupy lots of rollback segments writing into undo tablespace those deleted data. so when we clean partitions, we don't need to delete the whole table , so it is very convenient. 

eg:

Alter table t truncate/drop p1; 
Alter table t split partition p_default at 3000 into (partition p3,partition p4);
Alter table t add partition p6 values less than (6000); // delete maxvalue before do it
Alter table t1 exchange partition p1 with table t2 including indexes update global indexes; // exchange data between 2 tables. 

partition table & rowid : when you do an update in partition table, the rowid will be changed as well.
Global tempprary table : auto-cleased when session is closed or transaction is commited
IOT and cluster table : when query an IOT , it won't query from your original table , add index in columns with order by and foreign key.

Primary key : this is just (an index + a constrain), we can directly change an index into a primary key.

Alter table t add  constraint ord_pk primary key(order_id,item_id) using index ord_idx;

Table compression : This will decrease the logic read but increase the CPU.

Alter table t move compress;
Execute dbms.gather_table_stats(ownname=>user,tabname=>'T');
Select table_name, blocks, cp,ression from user_tables where table_name='T';

Never use char and long .

Drop truncate add spilt exchange // this will make index invalidated
Alter table table1 drop partition p1 update global indexes; // this can avoid index invalidated
Alter index idx_par_1 rebuild partition p3; //rebuild local index

collect statistics :

Alter session set statstics_level=all;
Select * from t1,t2 where t1.id=t2.t1_id;
Select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Replace delete table with global temporary table:

This can reduce the blow of redo log .

Create global temporary table t_global (id int, col2 int) on commit delete rows;
Insert into t_global.....
//other manipulations ....
commit;

In real situation , we wrote some script to monitor rather than wait for the problems to come out . Scripts are too long to share here, if you have any problem ,please leave a msg.

How to intervene the explain plan !

Sometimes , the explain plan of Oracle is not accurate ,  the way we change the explain plan is called hint, if you randomly write something inside /*asas*/ , well this is just a annotation . Hint will not work at all . if the table has a alias, you must use the alias to hint eg :  (index(t))

Select * from v$sql_hint; // check all the hints

/* + leading(t2) */       // visit t2 firstly
/* + use_nl(t1) */        // t1 is visited lastly after jpin
/* + index(id,object_id) */    // run index, but the index can't be null

Insert all into table1 into table2 select * from t // insert into multiple tables

// write pagination in this way

select * from (select t.*, rownum as rn from t t where rownum <=10) a where a.rn >= 1 

Update t set object_name = 'abc' where object_id = 8 and t.rowid='agsjgdgsgdhs'// with rowid, it will be very fast

Select /* + result_cache */ count(*) from t //cache results will increase the efficiency very much, it directly read from share pool，if query twice or triple times , logic read is 0

Create materialized view mv_count build immediate refresh on commit enable query rewrite as select count(*) from t; // create materilised view , the speed is faster

select count(*) from t; // this is super fast

Tuning Table join in Oracle

      is the recomnendation i give. 

Join tables to query in oracle is the most common manipulation that people do in their daily work. However, most of those slow queries or slow applications are due to the some bad joined tables . As software engineer, most of them don't know how to check how their query goes . It's ok. I have learned some when i studied and worked before.

I have summarise some notes to share with you how to tune your query in your work.

Oracle Join types :

• Nested loops join
• Hash Join
• Merge Sort Join

Nested loop join : loop to find and match one by one . It's used in small range scan in OLTP app.

Merge sort/Hash Join : Combine 2 tables to find and match , throw aways the leftover. It is used in massive range scan in OLTP application.

Check number of visit :  set statistic-level = all 

Under nested loop join : 

Starts : how many times this table is visited , very important param

Under Hash join/Merge sort join : The number of visit is alwayse 0 or 1. 

Table sequence and efficiency

When you notice, small table size in nexted loop join should be put before, the bigger table size one should be  put behind.  So  you use small table to find the big table , the number of visit will be less. 

But for Hash Join and Merge join, this doesn't make a difference. 

Table join is ordered ?

Nested loop join and hash join will not sort / order the result (0 sort), Merge sort will do sorting twice and merge in the end. 

Join Limitation ?

Nested loop has no limitation , For Hash join, you can't use < or > or like , only can use = , For Merge sort join, you can't use != , or like . but you can use others like < or >. 

Others :

Nested loop join : create an index in the constraint column where n = 2 ( create index here) , create an index in the constraint of connected table , where t1.id = t2.id ( create an index)

Hash Join : create indexes in all linking constraints in both tables.

Merge sort join : the same as hash join. if the table is visited tons of times , please consider to choose hash join as this will reduce the cost or sortting . 

 

Oracle "Explain Plan"

You really want to tune your slow SQL? Then firstly you must know how it runs. That's is explain plan/statistics!

Table statistics and column statistics' collections by default are setted t to be runned 10 p.m everyday. At a non-collecting period for example 9 a.m, oracle can conduct a dynamic sampling to collect some statistics in memory temporarily. but When you are creating index in oracle , it will auto-collect statistics.

Normally there're 6 ways to run & see explain plan/statistics :

1 explain plan for select * from t1; select * from table(dbms_xplan.display()); // not so accurate

2 set autotrace on(result and statistics) //It will really run the SQL , but you can't see how many times a table is visited

3 select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) //really run sql as well, but if it runs for quite long time, we must wait for it to finish, then we can see the result, time-consuming

4 select * from table(dbms_xplan.display_cursor('sql_id')); // NOTE: sql_id comes from "select * from v$sql"

5 // 10046trace

Alter session set events ‘10046 trace name context forever, level 2’; // open trace
 //run your sql here
        Alter session set events ‘10046 trace name context off’ // close trace
 //find the file produced

6 @?/rdbms/admin/awrsqrpt.sql // this is a tool in oracle

Recommend :

• Normally you can use method 1 and 2,  
• if multiple sqls are to be runned, you can use 4 and 6.
• if lots of functions will be triggered and called, you can only use 5. 
• if you want to check how many time table is visited , please use 3

anything unclear, please leave msgs!

Using index is not always good

Index can let your select very very fast. but can also let your update/insert very very slow, (Index is ordered, if update or insert is fired, it will re-order which is very costly), If your environment is more opt to system throughput, I will recommend you to use full can (full table scan + full index scan).

index logic failure:  index is ok to be used , but your query doesn't go by index.:)
index physical failure: index is removed.

Reverse index is good sometime becuase if so many indexes involved in this table , reverse index can avoid the hot block competing each other.

create index idx_id t(id) reverse;

Partition table insert : however, if index is not added, insert into partition table will be more costly than insert into normal table.

Aviod type conversion : put the specifc value into the column type specifed, type coversion will be very costly with index added.

Substr() trunc() : substr(column) and trunc(column) won't go by index , if these 2 functions are applied to columns.

Alter table t shrink space; //release high water mark . but index is still valid

Composite index : if query by a composite index, you gotta follow the order of indexes to fire the select query. reversed order or wrong order won't go by index

Hint way to create index : this part i think most developers are not clear . the hint way to create index is use /" "/,  this is very fast, try to use the hint way to create index rather than the parallel way.

Like : like "%peter" // won't go by index, like "per%" = like reverse("%per") // will go by index

Flashback :

flashback table t to before drop; // restore table from recyclebin，

                                     but all index & constrains are gone

Function index : if asc and decs are both involed like "order by col1 asc col2 desc" , try to create a function index (col1 asc, col2 desc)

Dummy index/virtual index : dummy index is not existed in reality , however it can deceive the oracle expain plan and simulate the result with indexs, if the result is good, then create a real index, if not ,don't need to do anything.

Alter session set "use nosegment indexes = true"
Create index idx_id on t(object_id) nosegment; //create dummy index

Finally please take notes :

• check whether you add indexes in some big tables in oracle
• check whether there are some indexes which never are used
• check whether you add some indexes on foreign key columns in tables
• check which tables have too many indexes
• check whether you overuse too many composite indexes
• check whether there are some foreign key constraint invalidated
• check which indexes have a high height
• check whether there are some index invalidated
• check whether your indexes are duplicated with composite index
• check which indexes have parallel setted
• check which statistic infor is too old

Why index is so fast ?

        is my recommendation for sql tuning. 

Index is a storing unit which can store column values . It is ordered,  low height and root-leaf structure. It retrives the column values orderly with rowid and put them together into oracle blocks called index block. Index structure is a physical structure. Index will take effect in columns in where statement eg : ( select * from person where name ='peter').

1. Index tree's height is low : because index blocks won't be full in reality , layer is often few (3 layers are 3 system IO firing), In big data query, if query 1 or 2 records, it will be fast. however it won't just querying 1 or 2 records , so system IO takes more query cost (slowly). If order by , group by and sum() all these functions are involved, it will be very slow.Thus distibuted file system like hadoop comes into the picture.

Query the first 100 records : use "as content from dual connect by level <= 100";

Select index_name, blevel,, leaf_blocks,num_rows, distinct_keys,clustering_factor from user_ind_statistics where table_name in ('T1','T2'); //check the table's index height，

blevel is the index layer number/index height，0 is less than 1 layer。

2.Index stores column values : index is consist of column value and rowid.

Index won't store null value:  if indexed columns have null value, even though it has index, but it will still go full table scan. So , in case of this occasion, try to use : 

select count(*) from t where object_id is not null;

or before table creation do :

alter table modify object_id not null;

Tuning sum() avg() etc : columns in all these functions must have indexes added so as to reduce logic read.

3.Index is ordered itself: 

optimise order by :"order by"'s cost is very high so try to add index in columns with order by .  because index is ordered itself , so it won't re-order which saves the cost and memory but cost CPU a lot.

optimise max() : add index in the column with max function. because index is ordered and store column values with tree structure, so if index is added, it will go to the leaf and find the last child directly which can greatly reduce the logic read.

optimise distinct() : distinct() doesn't go through index but by default go by hash unique algorithsm causing lots of logic read and PGA memory, so my way to query is : 

Select /* index(t) */ distinct object_id from it;  //add index to distinct columns

Don't optimise union : 

select object id from t1 union select object_id from t2;

Look at the above query using union, it will auto-filter the duplicated records , so It definately produces sorting,. Thus union + index is useless ,however, you can add index to union all.

composite index:  if multiple columns should be selected, you can create a composite index, if there exist where statement with "= , < or >", you must put the equaled columns before select.

if composite index is introduced(id, name), you don't need to create index seperately like (name). try to use "in" rather than > or < :

Select /* + index_desc(a,idx_t) */ * from t a order by owner asc ,object_type desc;

index in partition tables : your query will be very very fast !!

query min() and max() : you can't write select max(num), min(num) from ..., that will be very slow. because indexes in this occasion can't go to leaves to find the max and min at the same time, it can't look at 2 directions at the same time. thus optimised query will be :

Select max ,min from (select max(num_id) max from t) a,(select min(num_id) min from t) b;

Index scan methods 

In OCP 11G exam, when i studied it , there are 5 ways to scan the index,  I am not sure in 12C , these 5 still exist or have their name changed. So i just share some of my experience in 11G :

index range scan : range scan, it will refer to other indexes to cross-validate whether find the index specified.

index unique scan : unique scan, create unique index...; it's slightly faster than the range scan, because it won't refer to other indexes, as it is unique as defined.

table access by index rowid : no nedd to create index, this is the fastest way, directly locate the row!

index full scan & fast full scan : fast full scan is slightly faster, if order by is added , it will go index full scan , read one block once, ordered. without order by, it will go fast full scan , read multiple blocks once, not ordered.

index skip scan : if data is small , and limited in one column , it will jump, shuffle and fetch. eg : there is one column called "status", first 100 rows are "view" last 100 rows are "unread". in this situation, it will go skip scan.

Hope my share can help you improve your application . leave msgs if anything unclear

 

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