Is It Possible to Change SQL Statement in the Database?

YES!

Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change about the sql. Well, in this case instead of asking to someone else, you can change that sql in the database before it runs!

After 12c, Oracle gave us an API, Sql Translation Framework, to run a different sql statement than the one sent by the application (or any client). We can affect a sql statements by using hints and database parameters but this is a whole new area. instead of “select * from customers” you can force Oracle to run “select count(*) from invoices“! you can change data source and event column list!

Main purpose of Sql Translation Framework to decrease the cost of converting a non Oracle application to an Oracle application. What! Let’s say you have small third party application which is run on Microsoft SQL Server database. Let say this is a small reporting tool and it is running some Select statements. you can easily move tables from sql server to Oracle but changing application could take much more longer time. At this point any basic select statement can run on any database for example “select * from invoices”; so no need to change anything in here but in sql server there is a useful command called TOP which allows you to get top n rows after an order by. So if your small reporting application runs this query: “select top 10 * from invoices order by amount desc” Oracle can not run this because TOP is a sql server command not Oracle.

Sql Translation Framework is involving at this point and allow you to run a different sql than the one issued. Whenever “select top 10 * from invoices order by amount desc” select statement issued we can tell Oracle to run “select * from invoices order by amount desc fetch first 10 rows only” statement. Pretty cool.

So this is the main purpose but since we can change the sql statements, this can let us to change poorly designed sql statements. Of course the main solution should be changing the source (change the sql which is called from the application) but this might not be possible all the time. for example, an application which we can not alter the source code might be running these queries (maybe we don’t have the source code or it is too expansive to ask for a change) or there might be an emergency, let’s say somehow a poor sql statement has been deployed to production system and it causes a very big performance problem. Since it might be fixed right away and deploying whole application all over again can take too much time. In this kind of situations, we can simply use sql translation framework.

I will demonstrate simple sql translation not an application translation (it will require more steps).

Steps to translate a sql to another:

create a translation profile: main record for your tranlations. there could be more than one sql in a translation profile. you can simply think that as a translations groups to managed easily.
register an sql to translate: the query you want to translate and the query you want translated to.
grant necessary privileges to users who will use translations
set sql_translation_profile parameter and 10601 event

That’s it.

exec dbms_sql_translator.create_profile('TEST1');

begin
  dbms_sql_translator.register_sql_translation('TEST1','select top 10 * from hr.employees',
  'select * from hr.employees fetch first 10 rows only');
end;
/

alter session set sql_translation_profile = test1;
alter session set events = '10601 trace name context forever, level 32';

select top 10 * from hr.employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17/06/2003 00:00:00 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21/09/2005 00:00:00 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13/01/2001 00:00:00 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03/01/2006 00:00:00 IT_PROG          9000                       102            60
        104 Bruce                Ernst                     BERNST                    590.423.4568         21/05/2007 00:00:00 IT_PROG          6000                       103            60
        105 David                Austin                    DAUSTIN                   590.423.4569         25/06/2005 00:00:00 IT_PROG          4800                       103            60
        106 Valli                Pataballa                 VPATABAL                  590.423.4560         05/02/2006 00:00:00 IT_PROG          4800                       103            60
        107 Diana                Lorentz                   DLORENTZ                  590.423.5567         07/02/2007 00:00:00 IT_PROG          4200                       103            60
        108 Nancy                Greenberg                 NGREENBE                  515.124.4569         17/08/2002 00:00:00 FI_MGR          12008                       101           100
        109 Daniel               Faviet                    DFAVIET                   515.124.4169         16/08/2002 00:00:00 FI_ACCOUNT       9000                       108           100

exec dbms_sql_translator.create_profile('TEST1');

begin

dbms_sql_translator.register_sql_translation('TEST1','select top 10 * from hr.employees',

'select * from hr.employees fetch first 10 rows only');

end;

alter session set sql_translation_profile = test1;

alter session set events = '10601 trace name context forever, level 32';

select top 10 * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------

100 Steven King SKING 515.123.4567 17/06/2003 00:00:00 AD_PRES 24000 90

101 Neena Kochhar NKOCHHAR 515.123.4568 21/09/2005 00:00:00 AD_VP 17000 100 90

102 Lex De Haan LDEHAAN 515.123.4569 13/01/2001 00:00:00 AD_VP 17000 100 90

103 Alexander Hunold AHUNOLD 590.423.4567 03/01/2006 00:00:00 IT_PROG 9000 102 60

104 Bruce Ernst BERNST 590.423.4568 21/05/2007 00:00:00 IT_PROG 6000 103 60

105 David Austin DAUSTIN 590.423.4569 25/06/2005 00:00:00 IT_PROG 4800 103 60

106 Valli Pataballa VPATABAL 590.423.4560 05/02/2006 00:00:00 IT_PROG 4800 103 60

107 Diana Lorentz DLORENTZ 590.423.5567 07/02/2007 00:00:00 IT_PROG 4200 103 60

108 Nancy Greenberg NGREENBE 515.124.4569 17/08/2002 00:00:00 FI_MGR 12008 101 100

109 Daniel Faviet DFAVIET 515.124.4169 16/08/2002 00:00:00 FI_ACCOUNT 9000 108 100

as you can see I run an “invalid” sql statements. top 10 is a structure works on sql server but now I am able to run it in Oracle becuase I translated it into FETCH FIRST clause. please remember only exact sql will be translated. What I mean is if I add a order by or where clause to my translated query it won’t work:

select top 10 * from hr.employees order by employee_id;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 13 Column: 12

select top 10 * from hr.employees order by employee_id;

ORA-00923: FROM keyword not found where expected

00923. 00000 - "FROM keyword not found where expected"

*Cause:

*Action:

Error at Line: 13 Column: 12

Oracle does not know the version with “order by ” so you must add it too.

Also I mentioned about we can use this to implement a new version for poorly designed statements. Let’s test a union / union all query:

create table tmp_translate as
Select 1 group_id, Level level_id, dbms_Random.string('X', 100) my_string
from dual 
connect by level < 500000;

insert into tmp_translate
select 2 group_id, 500000+level_id, my_string from tmp_translate;

create table tmp_translate as

Select 1 group_id, Level level_id, dbms_Random.string('X', 100) my_string

from dual

connect by level < 500000;

insert into tmp_translate

select 2 group_id, 500000+level_id, my_string from tmp_translate;

this is our test table. table contains 2 sets of data and LEVEL_ID is unique so same row won’t be listed in both group. Let’s assume our developer has written this sql:

conn mustafa/mustafa

set timing on

  select Count(*)
  from   (
           select level_id, my_string from tmp_translate where group_id = 1
           union
           select level_id, my_string from tmp_translate where group_id = 2
         );
....
Elapsed: 00:00:01.09

conn mustafa/mustafa

set timing on

select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union

select level_id, my_string from tmp_translate where group_id = 2

);

....

Elapsed: 00:00:01.09

Query takes 1.09 seconds. this query has a logical error. union is unnecessary, union all would return same data (since level_id is unique) and that’s because union all would work faster. if we can not change the source of the query then we can use sql translation framework:

Begin
    dbms_sql_translator.register_sql_translation('TEST1','  select Count(*)
      from   (
               select level_id, my_string from tmp_translate where group_id = 1
               union
               select level_id, my_string from tmp_translate where group_id = 2
             )'
    ,
    '  select Count(*)
     from   (
              select level_id, my_string from tmp_translate where group_id = 1
              union all
              select level_id, my_string from tmp_translate where group_id = 2
            )');
end;
/

Begin

dbms_sql_translator.register_sql_translation('TEST1',' select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union

select level_id, my_string from tmp_translate where group_id = 2

' select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union all

select level_id, my_string from tmp_translate where group_id = 2

)');

end;

there is one important thing here. consider ENTER character because if you are using it will be carriage return and new line but in linux it is just new line. so while testing you must be careful about enter char.

now let’s test it again:

  select Count(*)
      from   (
               select level_id, my_string from tmp_translate where group_id = 1
               union
               select level_id, my_string from tmp_translate where group_id = 2
             );

  COUNT(*)
----------
    999998

Elapsed: 00:00:00.14

select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union

select level_id, my_string from tmp_translate where group_id = 2

);

COUNT(*)

----------

999998

Elapsed: 00:00:00.14

as you can see performance of the query has increased almost 10 times. that is because it worked as union all not union. unfortunately you can not understand it by checking execution plan because it is the same cursor so you will be seeing old execution plan. That’s also why I compare timings. Of course you can check SQL_TRANSLATION_PROFILE_ID in V$Session while running the query.

This is a great tool but don’t get too involved into this. Creating too many translations can cause too much headache. you might loose in them. So I suggest you to use it wisely.

By the way this also works with bind variables too:

begin
dbms_sql_translator.register_sql_translation('TEST1','select * from hr.employees where employee_id = :emp_id',
'select * from hr.locations where rownum < 3 and :emp_id = :emp_id');
end;
/

begin

dbms_sql_translator.register_sql_translation('TEST1','select * from hr.employees where employee_id = :emp_id',

'select * from hr.locations where rownum < 3 and :emp_id = :emp_id');

end;

after running select * from hr.employees where employee_id = :emp_id with any emp id, you will get 3 rows from locations table. if you are careful you will see that I added a :emp_id = :emp_id condition to translated query. that is because same bind variables must be listed on the second query otherwise you will get an error.

I hope this helps you. There are too many different things in it so this is just an introduction.

have a nice day.

Leave a Reply Cancel reply