Blogs Archives - Page 6 of 10 - Tosska Technologies Limited

2021-04-292023-02-19

Query Performance Tuning: Making an SQL Monitor Report

Creating a SQL Monitor Report plays an important role in database optimization as it helps the user observe other occurrences during the execution of long-running statements.

In this post, we’ll discuss how to create one such report that may help you during query performance tuning.

SQL Monitor Report: Bringing DBAs One Step Closer to Database Query Optimization

To begin with, you need to make sure your database has the tuning and diagnostic pack. Otherwise, Oracle will not authorize the creation of SQL Monitor Reports.

Also, such reports can be made after an adequate amount of time has passed. The wait is to allow query bottlenecks to reveal themselves. This is typically done for seemingly endless queries that run for long periods of time. However, in general, creating SQL monitor reports is recommended for completed queries.

Let’s look at an example: A DBA has a simple plan with a hash join involving two big tables. Suppose one of these tables takes two seconds to undergo a complete table scan, whereas the second one takes nine seconds.

Although only around two seconds out of a total of eleven seconds are sent on the first table, it will appear as though a hundred percent of the query time is being spent on it if you create a SQL Monitor report during the first two seconds.

Creating Reports for Excessively Long Execution Plans

Really long execution plans – those that exceed three hundred lines – don’t have a SQL monitor report generated for them by default. This gets cumbersome because long execution plans are where these reports are needed the most!

In such cases, there are two things you can do to make the database generate a report. These are –

Prior to issuing the query in question, generate the following in the session operating the query:

alter session set “_sqlmon_max_planlines” = 800;

2. Apply the following hint while executing the query:

/* + monitor */

How to Create an HTML Version of the Monitor Report

The HTML version of a SQL Monitor report offers some more details as compared to its text report. This is why it is often recommended by database professionals, with the help of the following query:

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘HTML’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;

Not every variable needs to be plugged in – you just require variables sufficient to enable Oracle to recognize the particular SQL\session combination. And if there is only a single session executing the statement on the entire database, only the sql_id is enough.

Creating a Text Monitor Report Instead

In case you’d rather make a text report – whether if it’s due to some problems with an HTML report, or simply preference – here’s how to do it –

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘TEXT’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;

2021-04-232023-02-19

Optimization in SQL: Determining Stale Statistics in Oracle

You can determine whether statistics are stale in Oracle using two methods. The first is to make Oracle tell you if it considers the stats are stale.

The second involves a comparison of the statistics of what the DBMS assumes a table to be – and what it really is. Here, we’ll help you understand and determine whether the stats are stale.

Other Reasons Why Oracle May Pick a Bad Plan (Aside from Stale Statistics)

Good statistics aren’t necessarily always perfect – they only have to be correct to a degree for the information in the table. In case your statements are running sluggishly because Oracle picked a bad plan, you may also take it as a fair sign that your statistics are stale.

That said, stale statistics aren’t the only culprits behind Oracle selecting a bad plan, triggering the need for optimization in SQL. There are others, such as the following:

If your data doesn’t include enough histograms, extended statistics, or joins (both correlated and anti-correlated ones), it may not be uniform enough for Oracle to perceive it as it should.
Oracle might find it hard to calculate the amount of data to expect. This generally happens when a query is written in a way that confuses the DBMS.
When Oracle lacks a precise representation of the duration involving the completion of one or more operations. The system statistics may be incorrect in such cases.
The data set may not be sufficiently represented in Oracle’s version of the statistics. This usually happens in large tables with highly varied data that a histogram cannot accurately represent.
Certain indexes typically used by Oracle may have become invalid.

Coaxing Oracle to Determine Stale Statistics

You can have Oracle tell you about stale stats easily if your priority is to improve performance of SQL query. The catch is, you won’t be able to determine how stale those stats are. However, you will know if there have been enough changes in a table for Oracle to consider regathering statistics on it.

To find out if that’s the case, you’ll need to view the stale stats column in DBA_STATISTICS which you can do with the following query:

select stale_stats

from dba_statistics

where owner = ‘<name of table owner>’’

AND table_name = ‘<name of table>’

The column may return “YES”, “NO” or other results, indicating Oracle’s stance on the stats. “YES” means Oracle is ready to re-gather statistics, whereas “NO” shows Oracle believes the statistics don’t need updating.

The column may return null, indicating incomplete or absent stats altogether. Take care to enter the correct table owner and table names as the query won’t return any rows otherwise.

Checking Stats on Your Own: What to Do in Oracle Database and SQL

When you do this manually, you can find out “how stale” your stats are. That’s because you’ll be able to collect stats on the “stalest” tables first, reducing the number of changes needed to be made to the database. This way, you could also avoid accumulating stats in situations that could lead to contention.

The goal here is to draw a comparison between Oracle’s values and the table’s actual values. Typically, a difference of up to 10 per cent between the two is acceptable. Also, this method requires us to check two separate kinds of statistics –

Table Level Statistics – As the name suggests, you can verify various aspects of the table like the following:

A number of rows and empty blocks – You can use a query like this:

DBA_TAB_STATISTICS.NUM_ROWS

select count(*)

from <table name>;

Number of Empty blocks and
A number of blocks taken up by the table – The statement below will help you retrieve the number of “occupied blocks”:

DBA_TAB_STATISTICS.BLOCKS –

DBA_TAB_STATISTICS.EMPTY_BLOCKS

select count(distinct substr(rowid, 7, 9))

from <table name>;

Column Level Statistics – These will help you determine things like:

How many distinct values exist in a column – This proves useful for expressions that include “COLUMN = <any value>”.

Try the following:

select count(distinct <columnname>)

from <table name>;

The column’s high and low values – These values prove useful for situations that need range-based predicates, such as those involving COLUMN <= <any value> or COLUMN between <START> and <END>.

The number of nulls in a column – This query should help if you want to view the stats of a particular column:

with cte (x)

(

select /*+ inline */ <column name>

from <table owner name>.<table name that you want to check>

)

select (select approx_count_distinct(x) from cte) distinct_values

, (select count(*) – count(x) from cte) num_nulls

, (select min(x) from cte) low_value

, (select max(x) from cte) high_value

from dual

2021-04-21

How to Tune SQL Statement with EXISTS Subquery for SQL Server II ?

In my last article that a SQL statement with an Exists subquery was improved 90 times by the following rewrite.

SELECT *
FROM DEPARTMENT
where exists (select ‘x’
  from employee
  where emp_id > 2700000
  and emp_dept=DPT_ID)

Query Plan:

Rewritten SQL syntax:

select  *
from DEPARTMENT
where  DPT_ID in (select    isnull(emp_dept,emp_dept)
from      employee
where   emp_id > 2700000
group by emp_dept)

Query Plan:

Syntax Rewrite Solution
Syntax rewrite technique to improve SQL statements are commonly used by DBA or developers especially for Oracle or MySQL databases, but syntax rewrite is not easy to be applied by users who are using MS SQL Server or IBM Db2 LUW. The reason is that MS SQL Server and IBM Db2 LUW have a strong internal rewrite engine in their SQL optimizer. The internal SQL rewrite engine will try to rewrite a SQL syntax to their internal canonical syntax. It means that no matter how you rewrite your SQL statement, MS SQL Server and IBM Db2 LUW will try to rewrite the SQL back to their internal presumed good syntax, so it is difficult to tune a SQL if the so-called presumed good syntax is not good, since users are not easy to influence database SQL optimizer to generate a better query plan by simple SQL syntax rewrite.

Query Hints Injection Solution
To solve this problem, SQL Server provides Query Hints feature for users to help its SQL optimizer generate a better query plan. It is not like the SQL syntax rewrite method, experienced developers may tell what the final query plan will be for a rewritten syntax, Query Hints is a pinpoint solution that a query hint injection is normally applied to the specific step of the entire query plan, but a change to a plan step will incur domino effect to other plan steps in the entire query plan since MS SQL Server must adjust other plan steps to achieve what the user’s expectation for the query hint in the SQL statement. So, the final query plan is not easy to predict by users, especially for complex SQL statements.

The following SQL with Hints injection generated by Tosska SQL Tuning Expert is around 4 times better than the original SQL and takes 0.639 seconds.

select *
from DEPARTMENT
where exists ( select ‘x’
from employee
where emp_id > 2700000
and emp_dept = DPT_ID) OPTION(LOOP JOIN,HASH GROUP)

There is an even better SQL with Hints injected, it is around 50 times better than original SQL and takes 0.055 seconds. This query plan is pretty close to the rewrite tuning in my last article.

select *
from DEPARTMENT
where exists ( select ‘x’
from employee WITH(INDEX(EMPS_DPT_INX))
where emp_id > 2700000
and emp_dept = DPT_ID)

Syntax Rewrite plus Hints Injection Solution
For some SQL statements, a separate syntax rewrite method or a hints injection method may not be able to solve a complex SQL performance problem individually, some people may think that will it be possible if we rewrite a SQL and apply hints at the same time to improve a SQL statement? Yes, it is possible in the Tosska SQL Tuning Expert A.I. engine, this technology can solve more SQL performance problems by a computer algorithm ever before. I will discuss this technology later in my blog.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

The following screen show Tosska SQL Tuning Expert can generate 178 distinguished query plans after investigated 300 SQL Hints injection, it is far out of what a human expert can achieve within 10 minutes. MS SQL Server is the most sensitive to Query Hints Injection database in the market, SQL Server query hints are normally able to influence SQL optimizer to generate a specific query plan, so the SQL tuning for MS SQL Server is far more challenging than other databases.

2021-04-212021-04-21

How to Tune SQL Statement with EXISTS Subquery for SQL Server I ?

The following is an example that shows a SQL statement with an Exists subquery. The SQL retrieves records from the DEPARTMENT table that DPT_ID is found in emp_dept of employee table with emp_id > 2700000.

SELECT *
FROM DEPARTMENT
where exists (select ‘x’
  from employee
  where emp_id > 2700000
  and emp_dept=DPT_ID)

Here the following is the query plan in the Tosska proprietary tree format, it takes 2.23 seconds to finish.

The query plan shows two Hash Match from [EMPLOYEE].[EMPLOYEE_PK] to [EMPLOYEE].[EMPS_DPT_INX] and then Merge Join to a sorted [DEPARTMENT] table. This query plan looks reasonable, but the number of records scan from [EMPLOYEE] is too expensive at the first stage, can we use the small [DEPARTMENT] table to scan back the [EMPLOYEE] table to improve the SQL.

Let me rewrite the EXISTS subquery into an IN subquery in the following, but the query plan is not changed as expected.

select  *
from DEPARTMENT
where  DPT_ID in (select   emp_dept
from     employee
where  emp_id > 2700000)

I further rewrite the SQL and add the dummy function “isnull(emp_dept,emp_dept)” in the select list, but it cannot stop the operation of Hash Match to [EMPLOYEE].[EMPS_DPT_INX].

select  *
from DEPARTMENT
where  DPT_ID in (select    isnull(emp_dept,emp_dept)
from      employee
where   emp_id > 2700000)

To further enforce the restriction for stoping the operation “Hash Match to [EMPLOYEE].[EMPS_DPT_INX]”, I try to add a dummy “group by emp_dept” operation in the subquery.

select  *
from DEPARTMENT
where  DPT_ID in (select    isnull(emp_dept,emp_dept)
from      employee
where   emp_id > 2700000
group by emp_dept)

Here the following is the query plan after the final rewrite, SQL server first uses a Table Scan of [DEPARTMENT] table and Nested Loop of “EMPS_DPT_INX index seek to RID Lookup of [EMPLOYEE]” with the Top 1 operation, so each record from [DEPARTMENT] table will match at most one record from [EMPLOYEE] only. The speed now is 0.024 seconds and is much faster than the original SQL.

Although the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 90 times faster than the original SQL.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

2021-04-082021-04-08

How to Tune SQL Statement with IN Operator with an Expression List for SQL Server?

The following is an example shows a SQL statement with an IN List expression. The SQL retrieves records from EMPLOYEE table that EMP_DEPT should match any value in a list of values.

select EMP_ID
from EMPLOYEE
WHERE EMP_DEPT IN (‘AAD’,‘COM’,‘AAA’)
AND EMP_SALARY<10000000

Here the following are the query plans in the Tosska proprietary tree format, it takes 2.4 seconds to finish.

The query plan shows three Hash Match with EMPLOYEE’s indexes. For indexes EMPLOYEE_PK and emps_salary_inx are processing with EstimateRows up to 3000000, it seems too expensive since this condition EMP_DEPT IN (‘AAD’,’COM’,’AAA’) should rapidly trim down the return records. Let me rewrite the IN list into multiple UNION conditions in the following:

select  EMP_ID
from     EMPLOYEE E1
where exists ( select ‘x’
             where E1.EMP_DEPT = ‘AAD’
             union
             select ‘x’
             where E1.EMP_DEPT = ‘COM’
             union
             select ‘x’
             where E1.EMP_DEPT = ‘AAA’)
   and EMP_SALARY < 10000000

This rewrite can force the IN list operation to be processed first before the condition EMP_SALARY < 10000000 takes place. Here the following is the query plan after rewrite, SQL server now can utilize Merge Join of 3 Nested Loop of “EMPS_DPT_INX index seek to RID Lookup of employee”. The speed now is 0.191 seconds and is much faster than the original SQL.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 12 times faster than the original SQL.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

2021-04-082021-04-08

How to Tune SQL Statements with CONCAT Operator for MySQL?

There may be some business requirements that need to compare concatenate strings and column with a given unknown length of the bind variable. Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables where employee’s department ID must concatenate two strings before it is compared to an unknown length of variable @dpt_var

select * from employee,department
where concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
and emp_dept= dpt_id

Here the following are the query plans of this SQL, it takes 23.8 seconds to finish. The query shows a “Full Table Scan Employee” to nested loop Department table.

You can see that this SQL cannot utilize index scan even the emp_dept is an indexed field. Let me add a “force index(EMPS_DPT_INX) hints to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition emp_dept >= ” , it is an always true condition that emp_dept should be greater or equal to a smallest empty character. It is to fool MySQL SQL optimizer that emp_dept’s index is a reasonable step.

select *
from employee force index(EMPS_DPT_INX),
  department
where concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
  and emp_dept >= ”
  and emp_dept = dpt_id

Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used for Employee table first and then nested loop Department table.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2021-03-242023-02-19

6 Query Related Problems to Avoid in Oracle Database

Insufficient database performance is a fly in every DBA’s ointment. To make matters worse, finding the root cause of performance-related problems can get really difficult.

One of the best methods to get rid of performance issues is through performance tuning. However, you may not always be sure where to begin with the optimization process.

At times, you may find your database performance lacking even after you have eliminated certain factors that adversely impact hardware and network performance, such as memory and disk space. In such cases, you need to examine your queries.

Watch Out for These 6 Query Related Mistakes!

Unoptimized queries can lead to multiple performance related difficulties for your systems. Here, we’ll analyze six common query mistakes that typically form the cause of database performance deterioration.

Prepare yourself for a good amount of tuning, if you detect any of these problems in your queries –

Query 1: Like Queries that Contain Leading Wildcards

SQL may not be able to utilize indexes the way it should due to leading wildcards. As a result, a full table scan will take place regardless of any indexed fields present in the table. Scanning each and every row in a table every single time means it’ll take more time to fetch query results. Therefore, it is advisable to remove the leading wildcard to boost efficiency.

Query 2: WHERE or GROUP BY Clauses that Have Non-Indexed Columns

When a column is indexed, it returns query results more quickly. This is because there is no need to conduct a complete table scan due to the presence of an index.

Arranging records also becomes much simpler with indexing at the time of return and ensures that the records can be uniquely identified.

Query 3: The Presence of the ‘OR’ Operator in Like Statements

The ‘OR’ operator is used while comparing fields or columns in a table. However, doing it too often in a WHERE clause is another way to invite unwanted full table scans.

If you want to improve the speed of your Oracle SQL queries, try using a union clause instead. They succeed in making SQL queries run faster, particularly in the presence of separate indexes at each end of the query. Basically, the union clause combines the results of two quick, indexed queries.

Query 4: Unavoidable Wildcard Searches

If you find that a wildcard search is unavoidable but would rather avoid the performance hit, see if you can conduct a SQL full-text search. This type of search is considerably faster than performing searches using wildcard characters. Additionally, you obtain more relevant results when the searches take place in huge databases.

Query 5: Inefficient Database Schema

The efforts you make to improve SQL query performance will only take you so far, unless you also work on optimizing your database schema. Consider the following tips to do so:

Normalizing Tables – Duplicate data slows down performance. So, make sure that a fact is represented just once in the database. For instance, use “cust_name” just one time if you’re referencing its data in multiple tables, use another column such as “cust_ID” for subsequent references.

Using Suitable Data Types – Given below are a few important points to keep in mind regarding data types:

Try to create tables with data types of shorter sizes, such as a “user_id” field with “TINYINT” data type – in case there are fewer than a hundred users.
If one or more fields will need a date value, consider using a “date_time” data type for those fields. This way, you won’t have to convert the records to date format afterwards.
SQL functions more favorably with integer values in comparison with text data types, including varchar.

Don’t Use Null Values – The presence of multiple null values in a column negatively affects your query results. Instead, it is better to set a default value for fields in which a mandatory value is not needed.

Try to Use Fewer Columns – Tables that contain over a hundred columns are considered too wide because they take up a considerable amount of processing power and resources. See whether you can split a wide table into smaller tables that are also logical – unless a wide table is unavoidable.

Optimize Joins – SQL queries that contain an excessive number of joins as well as joins that include several tables have a negative effect on performance. Aim for queries with at most twelve joins.

Query 6: Un-Cached SQL Statements

Caching SQL queries proves advantageous for websites and applications that perform a lot of select queries. SQL query caching increases performance speed when read operations are performed. This is because it caches the select statement along with the resulting data set. Also, it retrieves the information from memory rather than the disk in case the statement is carried out more than once.

Final Thoughts

Performance tuning is vital for keeping your database available and preserving user satisfaction. Unfortunately, it isn’t always immediately clear where you need to perform tuning. If you have ruled out the typical hardware and network sources for performance issues, start examining your queries.

As a DBA, you are likely to face one or multiple of the problem queries mentioned above. Now that you are aware of the possible sources of the problem, keep an eye out for these and work on your performance improvement efforts. You can do this by correcting these common query problem areas so you can make the most of Oracle SQL database query optimization.

2021-02-262023-02-19

MySQL SQL Performance Tuning: 8 Great Monitoring Practices

DBAs don’t mind getting the attention of the management, as long as it’s for a positive reason. I mean once in a while, but not all the time; many would still prefer to remain off-radar, quietly ensuring the organization’s databases stay running without a hitch.

Since they have significant accountability in an organization, it is essential for them to beware of all the things that could go south. They can do this by following certain excellent practices for monitoring and MySQL SQL performance tuning, which we will explain in this post.

Best MySQL Database and SQL Monitoring Practices

With these practices, you can avoid being recognized by management for the database outage that took four days to fix, instead of your people skills.

Conduct Regular Health Checks of Your Database

A database administrator knows how important it is to schedule regular health checks for their database. Note that every database has its own maintenance requirements, and that the health checks should be geared toward particular functional needs.

Databases that are non-critical do not require as frequent checks as mission-critical or life-critical databases. A local bookshop’s customer rewarding app failing isn’t as severe as the failure of a missile defense system, for instance.

Monitor MySQL Availability

This is perhaps the most essential metric to follow, since the unavailability of the database won’t leave much choice, nor will the other metrics matter until this particular issue is resolved. Use the Run dialog to check availability. You need to type in “ -mysqladmin -h 192.168.1.95 -u root -p” to do so, and initiate diagnostics in case there’s a problem.

Check for Unsuccessful Connections & Error Logs

Monitoring the list of unsuccessful connections can slowly but surely help you identify both malicious activity and errors that aren’t as serious (caused by human error like incorrect id\password or misapplied permissions), in the application.

You are likely to get a broader picture in this manner, which will enable you to recognize larger recurring problems so that you can address them appropriately. You can keep track of failed connections by running the following command –

SHOW GLOBAL STATUS LIKE ‘aborted_connects’;

– to know the number of aborted connection attempts on the database in a provided time range.

Identify Deadlocks in InnoDB

In MySQL database and SQL, a deadlock takes place when multiple transactions put a lock on a resource required by another transaction. Deadlocks lead to retarded processing, increased timeouts – and unhappy users. Using the query “SHOW ENGINE INNODB STATUS;” will help you find deadlocks and fix them.

Observe Configuration Changes

An abrupt decline in performance can be the result of any number of causes. However, checking for any recent configuration changes can help you spot any adversely affecting ones and save you a considerable amount of time.

Keep an Eye On the Slow Queries Log

Queries that are slow make the database operate slower as well. This is due to an increase in CPU and memory usage. Assess the Slow Queries log from time to time to know if any queries are taking excessive time to run. You can then proceed towards identifying the root cause and resolving it.

Maintain Visibility to Comprehend the Main Reason Behind Performance Issues

Although regular health checks are important for MySQL SQL performance tuning from the perspective of maintaining high availability, they are not as useful in terms of overall system troubleshooting.

This is because periodic performance concerns may not appear during a routine health check, which is also why a consistent visibility must be established with the MySQL environment.

The quicker you can uncover and fix performance related problems, the fewer the users that will be affected, since downtime will be considerably decreased.

Setting alerts for critical occurrences can give you the chance to react as quickly as possible, in case a threshold is surpassed. You may set alert thresholds for these typical performance deterioration sources, at least –

Substantial deviations from baseline metrics, in terms of performance tuning in SQL MySQL
Excessive CPU utilization
Query latency
Query faults
Connection restraints
Buffer pool usage

Identify and Resolve Performance Issues Quickly

A database monitoring and MySQL SQL performance tuning tool is going to be the best option in nearly every case, so that issues are resolved before they grow into bigger problems.

There are plenty of performance tuning and monitoring tools on the market that come with a broad range of features at varying price ranges. Choosing the right one for your database will depend on your budget and requirements.

As long as you know what to look for in a performance tuning tool, you won’t have many issues in making your selection. Given below are a few features you should consider if you want a tool to perform SQL tuning for MySQL –

Scalability
Mobile tracking
Intuitive User Interface
Affordable yet feature-filled
Zero connection limitations
Different analysis variations, such as
- Multidimensional workload analysis
- Alarm source analysis
- Blocking analysis
Smart alarms
Historical data monitoring

2021-02-262023-02-19

SQL Performance Tuning: Frequent Questions about Indexes

A database is a piece of software operating on a computer, which means it is dependent and likely to face the same limitations as other software present on that computer. In other words, it will only be able to process as much data as the hardware can handle.

One of the best ways to speed up queries is to perform SQL performance tuning. In this post, we will answer some of the most frequent questions involving databases and indexes.

What is Indexing in SQL Query Optimization?

Indexing is one of the first things you may have come across while learning the ropes of your database. It is a wonderful tool that enables users to enhance the efficiency of their database. However, bear in mind that not every database requires indexing, and not all indexes are helpful in SQL performance tuning.

Let’s learn more about indexing: what it is and how it helps in enhancing database performance.

How do Indexes Affect SQL Query Performance?

An Index can locate data swiftly without having to go through each row in the table. This saves plenty of time!

Certain data columns are required before you can create an index. These are –

The Search Key which holds a duplicate of the primary key
The Data Reference which has a set of pointers

All of these constitute the structure of one index. To understand how an index works, let us take an example. Suppose you need to look for a bit of data in your database. Rather than scour every line yourself, you make the computer search each row till it locates the information. Remember that the search is bound to take much longer if the requisite information is located at the end. Fortunately, you have the option to sort alphabetically to shorten the length of such queries.

What are the Types of Database Indexes?

Database indexes are of two kinds –

Clustered indexes – These arrange data using the primary key. The reason behind using a clustered index is to make sure the primary key is saved in ascending order. This is the same order in which the table stores memory.

A clustered index is automatically created when the primary key is set, which helps in SQL tuning for Oracle in the long run as well.

Non-clustered indexes – A non-clustered index is a data structure that boosts data fetching speed. It is different from clustered indexes, as they are made by data analysts or developers.

When and How Should We Use Indexes?

Since indexes are intended to accelerate database performance, you should apply them whenever you think they can simplify the use of the database. Although smaller databases may not have several opportunities to use indexes, they are likely to see the benefits of indexing as they grow into larger databases.

You can make sure your indexes keep performing well, if you test run a set of queries on your database first. Clock the time those queries take to execute and begin creating your indexes after that. Keep rerunning these ‘tests’ for continuous improvements.

Conclusion

Indexing has its challenges, the biggest one being determining the best ones for every table.

For instance, heaps require clustered indexes because searching for a record in a heap table is comparable to finding a needle in a haystack: it’s inefficient and time-consuming, thanks to the heap’s unordered structure.

On the other hand, locating data is simpler and faster from a table that contains a proper clustered index, just like finding a name in a list that’s alphabetically ordered. DBAs, therefore, recommend that every SQL table contains a proper clustered index. Now that you know how indexes work and how they can optimize database performance, you should be able to use them to reduce query times substantially. If you would like more tips on how to use indexing, or you need a SQL query optimization tool for your database, let our experts know!

2021-02-052021-02-04

How to Tune SQL Statement with DECODE Expression for Oracle?

Here the following is an example SQL statement with a DECODE expression syntax.

select * from employee
where decode(emp_dept , ‘AAA’ , ‘ADM’ , ‘AAB’ , ‘ACC’ , emp_dept) = ‘ADM’

Here the following are the query plans of this SQL, it takes 6.41 seconds to finish. The query shows a Full Table Scan of EMPLOYEE table due to the DECODE expression cannot utilize the EMP_DEPT column’s index.

We can rewrite the DECODE expression into the following semantical equivalent SQL statement with multiple OR conditions.

SELECT   *
FROM      employee
WHERE  emp_dept = ‘AAA’
      AND ‘ADM’ = ‘ADM’
      OR  NOT ( emp_dept = ‘AAA’ )
          AND emp_dept = ‘AAB’
          AND ‘ACC’ = ‘ADM’
      OR  NOT ( emp_dept = ‘AAA’
                  OR emp_dept = ‘AAB’ )
          AND emp_dept = ‘ADM’

Here is the query plan of the rewritten SQL and the speed is 0.41 seconds. It is 15 times better than the original syntax. The new query plan shows a BITMAP OR of two INDEX RANGE SCAN of EMP_DEPT index.