Hi Team,
OS:RHEL
DB: 11g
I have run the sqltrpt script to find out the reccomendations for one of the erroneous query. I am not able to understandb the recommendations for this query. please find the explain plan and suggestion:
1- Original With Adjusted Cost
------------------------------
Plan hash value: 777465783
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 12746 | 662K (1)| 02:12:25 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2 | 12746 | 662K (1)| 02:12:25 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | 1 | 171 | 662K (1)| 02:12:25 |
|* 5 | TABLE ACCESS BY INDEX ROWID | HOQ_BASIC_ | 1 | 161 | 662K (1)| 02:12:25 |
|* 6 | INDEX RANGE SCAN | BASIC_RPT | 3085K| | 9090 (1)| 00:01:50 |
|* 7 | INDEX RANGE SCAN | HOQ_ID_LIST_MST | 1 | 10 | 2 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 1 | 179 | 8 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 138 | 6 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| HOQ_FAMILY_ | 1 | 128 | 4 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDXB_FM_ACCT_HOQ_RNAME | 1 | | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | HOQ_ID_LIST_MST | 1 | 10 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | HOQ_BASIC__PK | 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | HOQ_BASIC_ | 1 | 41 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++++++++
3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate TO_CHAR("B"."ACCOUNT_NUMBER") LIKE :B1 used at line ID 5 of
the execution plan contains an implicit data type conversion on indexed
column "ACCOUNT_NUMBER". This implicit data type conversion prevents the
optimizer from selecting indices on table
"PENSIONSBI"."TBL_HOQ_BASIC_".
Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices.
Regards,
Sphinx