I am using Oracle 9i.
I have some reports developed in Reports 6i which run very slow.
When I run the query in TOAD, it’s taking too much time for displaying
result.
Can anybody suggest me how to tune this query ?

The Oracle version number (for example 9.2.0.4) is important, as it determines what types of transformations Oracle may use to help improve the query performance. First, a slight change in the formatting to make it easier for me to read (you will need to fix the DECODE statements):

In the above, you are concatenating three columns into a single value, and then trying to determine if the same concatenated value exists in a table. A DBMS_XPLAN would probably show many, many full table scans of the TRANSDETAILS table.

If we just list the three columns, and use TRUNC rather than TO_CHAR, we may be able to take advantage of an index on those columns, and avoid the CPU overhead of a data type conversion of a DATE column to a character data type. You could use an EXISTS syntax instead, which might be more efficient (not shown):

Let’s transform the subquery into an inline view, which will sometimes help improve performance (usually a very noticeable on Oracle 8i). Such a transformation may be performed by Oracle automatically:

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: