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.