Oracle Database 10g SQL Tuning Hint and Tips

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.


About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root, Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s