Evaluate the following tips when writing queries on Oracle DB query will make better performance.
+ Using Oracle Tuning Advisor is the first step to improve performance of querries.
+ If you are querrying inequallity condition on index column, Oracle wouldnt use index thanks to this condition. Therefore your querry couldnt show better performance than you would except.
+ Distinct and group by queries that contain these statements need to sort the returning result set by query. Therefore it will run slower. Distinct and group by nesting of expressions, it may be a result of operating performance may be lower than you expect it.
+ If you have query that contain aggreagte operations, you should not miss use available index.
+ If you are using a function on a index column in where clause. You cant use this index normally. You should create function based index If you want to your query use index in this situation.
Eg: where upper(colA) = ‘hebele’ –> If colA has a function based index (upper(colA)) , this query will use an index.
+ Querries that have weak selectivity do not use index.
+ Using a union all instead of union, your query performance will improve again. Because union needs to sorting result to get distinct value. Therefore Union works slower than Union All. If your query result doesnt change when you use both them. You should choose union all to improve query performance.
+ Should be avoided as much as possible to write nested queries. Because nesting dramatically reduces the performance of queries.
+ When we are checking boolean values, we should use EXISTS instead of IN.
+ Using OR and IN statement of query generally poor performance.
+ = or AND statements work better performance.