This post is just inspired by some reflections and doubts triggered by a session on SQL (Structured Query Language) Query Optimization I attended today.
The speaker had mentioned that earlier versions of Oracle used Rule based Optimization whereas the present versions of Oracle RDBMS uses Cost Based Optimization (CBO).
From my limited knowledge of SQL I had doubts about the way SQL will display results when we use GROUP BY clause along with aggregate functions.
Indeed, I am intrigued when the DBA said that JOINs are less costly than SUB QUERY.
Indexes in RDBMS always amused me. In my earlier project, where we had plenty of data integrity issues caused by PRIMARY KEY CONSTRAINT related exceptions. Somehow or other I had managed to eliminate them by clearing the PRIMARY KEY related indexes.
In today's session, a DBA with SQL Server expertise mentioned about Clustered Indexes. It was a wild character to my limited knowledge of database systems!
The presenter mentioned that indexes are mainly used when we are selecting rows from columns. This statement was really impressive to me.
The DBA highlighted that each developer can play his / her part in SQL optimization and query fine tuning by analysing the EXPLAIN PLAN table. They should ask for access to EXPLAIN PLAN table. Then they should run a kind of util command that will add EXPLAIN PLAN table on their own schema. This table will help us to optimize the queries before the real execution.
SQL profiler is a tool from Oracle that can help in analyzing the Queries. I need to know more about it.
Finally, DBA mentioned that sometimes the third party tools and applications that we install on the RDBMS can spoil the entire performance of the system. The Data backup tools, Search and Indexing engines etc are some examples for this. The main reason for the impact on the performance is the share of CPU resources demanded by these tools. These tools end up eating up the OS resources making RDBMS engine weak and unstable.
Let me end this brief note with the expectations that I can carry forward this exploration further and further ...
The speaker had mentioned that earlier versions of Oracle used Rule based Optimization whereas the present versions of Oracle RDBMS uses Cost Based Optimization (CBO).
From my limited knowledge of SQL I had doubts about the way SQL will display results when we use GROUP BY clause along with aggregate functions.
Indeed, I am intrigued when the DBA said that JOINs are less costly than SUB QUERY.
Indexes in RDBMS always amused me. In my earlier project, where we had plenty of data integrity issues caused by PRIMARY KEY CONSTRAINT related exceptions. Somehow or other I had managed to eliminate them by clearing the PRIMARY KEY related indexes.
In today's session, a DBA with SQL Server expertise mentioned about Clustered Indexes. It was a wild character to my limited knowledge of database systems!
The presenter mentioned that indexes are mainly used when we are selecting rows from columns. This statement was really impressive to me.
The DBA highlighted that each developer can play his / her part in SQL optimization and query fine tuning by analysing the EXPLAIN PLAN table. They should ask for access to EXPLAIN PLAN table. Then they should run a kind of util command that will add EXPLAIN PLAN table on their own schema. This table will help us to optimize the queries before the real execution.
SQL profiler is a tool from Oracle that can help in analyzing the Queries. I need to know more about it.
Finally, DBA mentioned that sometimes the third party tools and applications that we install on the RDBMS can spoil the entire performance of the system. The Data backup tools, Search and Indexing engines etc are some examples for this. The main reason for the impact on the performance is the share of CPU resources demanded by these tools. These tools end up eating up the OS resources making RDBMS engine weak and unstable.
Let me end this brief note with the expectations that I can carry forward this exploration further and further ...
