Skip to main content

Posts

Oracle SQL Tuning Tips

Consideration when writing an SQL statement is that it returns a correct result. The second is that it be the most efficient for a given situation.  You can use many different SQL statements to achieve the same result. It is often the case that only one statement will be the most efficient choice in a given situation. Remember that processing SQL is a sequence of Parse (syntax check and object resolution), Execution (required reads and writes), and Fetch (row results retrieved, listed, sorted, and returned). SQL “tuning” consists, quite simply, of reducing one or more of them. Note: generally Parse is the greatest time and resource hog. Parse overhead can be minimized by the use of Procedures, Functions, Packages, Views, etc. Inadequate performance can have a significant cost impact on your business. A poor performing system and application can result in customer dissatisfaction, reduced productivity, and high costs. It is absolutely critical that the system’s performance is oper
Recent posts

17 Best practices for writing optimized SQL queries

  Follow 17 Best practices for writing optimized SQL queries: 1. Use EXISTS in place of IN wherever possible 2.  Use table aliases with columns when you are joining multiple tables 3. Use groupby instead of distinct. 4. Add useful comments wherever you write complex logic and avoid too many comments.   5. Use joins instead of subqueries when possible for better performance. 6. Use WHERE instead of HAVING to define filters on non-aggregate fields   7. Avoid wildcards at beginning of predicates (something like '%abc' will cause full table scan to get the results)   8. Considering cardinality within GROUP BY can make it faster (try to consider unique column first in group by list)   9. Write SQL keywords in capital letters.   10. Never use select *, always mention list of columns in select clause. 11.  Create CTEs instead of multiple sub queries , it will make your query easy to read.   12. Join tables using JOIN keywords instead of writing join condition i

SQL Query Execution Order

What's the logical order of SQL query operations? SQL Query Execution Order: 1. FROM & JOIN: The DB looks at the tables we specified in FROM and in the JOINs, and then merges this data - this merged data is our starting "working set". 2. WHERE: Perhaps we don't want the whole merged working set - perhaps we want to filter this data, so this means we end up with a subset of our working set - we've retained some rows and discarded other rows. 3. GROUP BY: Time to make some buckets! When we're grouping data, we're creating buckets (groups). Each bucket has a key and the associated rows. These buckets help us perform aggregations. 4. HAVING: Perhaps we don't want all our buckets. This is where we can specify some conditions, so that we choose just some buckets, and discard others. (This is similar to the WHERE clause - in WHERE, we are filtering rows, whereas in HAVING we are filtering buckets). 5. SELECT: We can select column names as well

Oracle Indexes and types of indexes in oracle with example

What are Oracle Indexes? Just like we have index present in the textbooks to help us find the particular topic in the book, Oracle index behaves the same way.we have different types of indexes in oracle. Indexes are used to search the rows in the table quickly.  If the index is not present the select query has to read the whole table and returns the rows. With Index, the rows can be retrieved quickly We should create Indexes when selecting a small percentage of rows from a table (less than 2-4%). If the % of rows returned is high then index scan will be slow. It also depends on the data distribution i.e clustering factor Indexes are logically and physically independent of the data in the associate table. Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Indexes are the primary means of reducing disk I/O w