Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
27,192

This blog is related to few of my experiences while working on a Project(Migrating from Oracle PL/SQL to HANA SQL Scripts). Some of the basic differences that I found between Oracle PL/SQL and HANA SQL Script are as follows:

1. In HANA nvl() function does not exist. We have IFNULL() function to check if an attribute value is null or not.


For example:

In Oracle: select employee_name from employees where nvl(salary, 0) > 20000;

While in HANA:  select employee_name from  employees where ifnull(salary, 0) > 20000;


The above statements check if the salary is NULL or not, if the salary is null, it sets the salary to 0.


2. Equivalent function for DECODE (In Oracle) is MAP in HANA. It works exactly the way DECODE works.


In Oracle : SELECT DECODE (GENDER, ‘F’, ‘FEMALE’, ‘MALE’) “GENDER”, EMPLOYEE_NAME FROM EMPLOYEES;

In HANA : SELECT MAP (GENDER, ‘F’, ‘FEMALE’, ‘MALE’) “GENDER”, EMPLOYEE_NAME FROM EMPLOYEES;


The above statements return employee name and gender (FEMALE or MALE, based on Values F or M stored in column GENDER).


3. Rounding Issues: In HANA, If a column’s datatype is decimal and its precision exceeds the allowed limit, then HANA doesn’t round it based on the next digit unlike Oracle.

     

For example:

TABLE NAME: PURCHASE_ORDERS

COLUMNS: PURCHASEORDERID VARCHAR(20),

          ITEM VARCHAR(30),

          LOCATION VARCHAR(30),

          QUANTITY DECIMAL(15, 3)


Suppose I want to insert the following row into PURCHASE_ORDERS table:


INSERT INTO PURCHASE_ORDERS (PURCHASEORDERID, ITEM, LOCATION, QUANTITY) VALUES (‘234212’, ‘Q-123-VE21’, ‘IND’, ‘200.0009’);


And when I check the Quantity of this record after inserting the record in HANA and Oracle, I get the following result:


Oracle:

HANA:

Oracle rounded the Quantity based on the next few digits(rounded 200.0009 to 200.001 as the precision limit is 3), while HANA did not round the Quantity based on next few digits.

4. In Oracle a variable/column having data type as DATE can store timestamp also along with the date, but in HANA the DATE data type can have only date. If you want to have timestamp along with date in HANA, you can use Data type TIMESTAMP.

5. The function CONCAT behaves differently in Oracle and HANA if one or more strings that is to be concatenated is NULL.

           

     For example:

     In Oracle: select concat('a', null) from dual;

                         The output will be 'a'.

          

     In HANA: select concat('a', null) from dummy;

                         The output will be null.

           

6. Materialized view – Oracle uses materialized view to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. While in HANA, we don’t have materialized view as materialized views are expensive and are not required in HANA.

7. Regular Expression Functions: Regular expression functions in Oracle like REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_COUNT etc. are not yet implemented in HANA. You can write SQL script procedure/function to implement the same functionality.

8. Rowid Psuedocolumn: The psuedocolun ROWID returns the address of the row in Oracle.


For example:

SELECT ROWID, EMPLOYEE_NAME FROM EMPLOYEES WHERE EMP_ID = 123;


This statement returns the row address in Oracle.


However, HANA doesn’t offer ROWID functionality. Altough there is "$rowid$" in HANA which can provide similar functionality. But it is purely internal and non-documented hence its use should be avoided.

9. Whether it is Oracle or HANA, nesting of select queries should be avoided as it consumes a lot of memory and the performance degrades. Instead, break the query into smaller chunks, put them in variable and use those variables.

23 Comments
Former Member
0 Kudos

Very Useful Points Prachi.

Regards

Vignesh J

Former Member
0 Kudos

Good pointers.Will be helpful for HANA developers

Former Member
0 Kudos

Good one Prachi ! Its very helpful.

Former Member
0 Kudos

Good blog on basic differences between hana and oracle .. :smile:

Will be helpful.

Former Member
0 Kudos

For Oracle PL/SQL developers who wish to learn HANA SQL scripting , this is a Great blog to start with ..

Good one.

lbreddemann
Active Contributor
0 Kudos

I think the idea to compare the two platforms under the perspective of a migration is pretty good and some of the points are surely a good hint for developers.

However, there are some points that are not as good in this blog post and maybe you correct them:

  • Formatting: please, there are heading, bullet list, numbered list, etc. styles available that allow you to structure the text. Please make use of them! It doesn't only look much better but also helps the reader to navigate the text.
  • Your comment no. 3 "Rounding issues" is absolutely wrong. If SAP HANA would simply cut off the values, it would produce wrong results.
    The reason why you don't see the decimal digits in your result set is that by default SAP HANA studio has the option "Format Values" ticked ON for result sets.
    This setting then makes sure that the values in the result set display are rendered according to the regional settings of the JAVA runtime your SAP HANA studio is running in. Typically that would be the regional setting of your OS-session.
    You can disable the formatting to see the "raw" values in the SAP HANA preferences.
    Remember: whenever you actually see a result set of any database (same with Oracle) you see a rendered version of it.
  • ROWID is a big NO NO for developers in Oracle.
    If you use it - that's a bug (unless you're Tom Kyte or Jonathan Lewis, that is :smile: )
    The statement you made about the "$rowid$" column ("it should not be used") is equally true for Oracle's ROWID column.
  • The statement about nesting of sub-queries is to general to even have a validity. Not sure if you ever heard about query transformation, but both Oracle and SAP HANA use that a lot and not all sub-queries are handled the same way.
    Statements like this definitively require a lot more factual backing!

I guess, what I'm saying is: keep going with the blog post writing and up your game.

- Lars

stefan_koehler
Active Contributor
0 Kudos

Hey Lars,

>> ROWID is a big NO NO for developers in Oracle. If you use it - that's a bug (unless you're Tom Kyte or Jonathan Lewis)


In general i would agree with you on this, but there are application cases that benefit from using ROWID (and it is legal to do it so). Good examples are applications that are used to manually maintain data - SQL*Developer is such a tool which uses ROWID for updating some data set for example. However every mask based single data set update application may benefit and use the ROWID approach for maintaining data.

Not the common use case for the "big data hyper super OLAP fast" HANA appliance, but pretty common in OLTP :razz:

Regards

Stefan

lbreddemann
Active Contributor
0 Kudos

hmm... I cannot agree to this, really.

SQL*Developer, TOAD and the like do use ROWID to access rows without having to know the table key. But these are rather special developer tools and they usually don't do the OLTP processing.

Also, the ROWID actually can change for a given record - you won't build your application logic on that.

Even good old Tom makes it very clear (Ask Tom "Is it safe to use ROWID to locate a row?") :


It is perfectly safe to use the rowid in ALL CASES however, assuming you combine it with the primary key as well


In other words: locating the record with rowid - fine. Identifying it by rowid - not such a good idea.

- Lars

stefan_koehler
Active Contributor
0 Kudos

Hi Lars,

great, i need to convince you - Let me try :wink:

>> SQL*Developer, TOAD and the like do use ROWID to access rows without having to know the table key. But these are rather special developer tools and they usually don't do the OLTP processing.


I guess you misunderstood my point. The usual (OLTP) single data set mask processing is the following:

  1. User selects data in application mask at first (e.g. based on some primary / table key or whatever)
  2. User locks data (in edit) mode and modifies specific data set
  3. User updates data set (and commits)


In step 1 (or step 2 depend on your application) you usually select the ROWID as well and then use it to proceed with step 3 in the most efficient way later on. That's also the way how SQL*Developer (Single Record View) does it, but the dialog box can be from any other application as well.

In consequence the SQL (execution) flow could be something like this:

  1. SELECT ROWID,"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "SCOTT"."EMP" WHERE "EMPNO" = 7788 FOR UPDATE
  2. UPDATE "SCOTT"."EMP" SET COMM=:sqldevvalue WHERE ROWID = :sqldevgridrowid

Totally legal to do so and in the most efficient way as the PK (or any other WHERE clause) is not used / accessed twice :smile:

Regards

Stefan

P.S.: This "logic" is also described by Christian Antognini in his book "Troubleshooting Oracle Performance, 2nd Edition" on page 482.

lbreddemann
Active Contributor
0 Kudos

Alright Stefan, it's not always a bug to do that.

Just most of the times :wink: .

Your workflow is very specifically designed to make the use of ROWID safe - that's not to be assumed in general and honestly the vast majority of occasions I have seen ROWID in productive code it was a bug (e.g. trying to find the "newest" record and the like).

Anyhow, good to see you're still out and about and holding up the flag for Oracle know-how here on SCN!

stefan_koehler
Active Contributor
0 Kudos

>> ... and holding up the flag for Oracle know-how here on SCN!

.. and i still have hope for you and Martin :lol:

Former Member
0 Kudos

Hi Lars,

Thanks for your suggestion. I will surely work on it.

I might be wrong, but I tried to turn "Format Values" option off and then created a table and inserted rows in it -

CREATE TABLE INVENTORY (NAME NVARCHAR(50), QTY DECIMAL(15,3), UNIT NVARCHAR(5));

  

INSERT INTO INVENTORY values ('RICE', 891.0008, 'KG');

INSERT INTO INVENTORY values ('WHEAT', 713.009, 'KG');

When I see the result of it, HANA gives me the following result -

While Oracle gives me this -

Please let me know if I am wrong somewhere.

Regards,
Prachi

lbreddemann
Active Contributor
0 Kudos

Hi Prachi,

you're right and I've got to apologize. :shock:

Indeed this was not a display error, but (like other DBMS as well) SAP HANA doesn't perform automatic rounding when doing a type conversion.

Compared with Oracle the implicit conversion performs a TRUNC() while Oracle does a ROUND() .

It's still possible, of course, to manually ensure that the values are treated the same way:

by using the ROUND() function during the insert.

BTW: the SQL standard left the decision whether to round or to truncate during implicit conversions explicitly to the DBMS platform. Which means both ways of doing it are actually correct. 

Cheers, Lars

Former Member
0 Kudos

Thanks Prachi. This is easy to read and understand.

Thanks Lars for your insights and corrections.

Former Member
0 Kudos

Thanks Prachi for putting this in one place. I am searching for Decode function in HANA and i got it 🙂  it helped me to run Hana procedure.

former_member183463
Participant
0 Kudos

Nice info Prachi.

Regards,

Kamal

Former Member
0 Kudos

we are doing sql syntax translators from oracle plsql to hana sqlscript, other translation type can also be supported, if anyone have this needs, please consult me or my team, hana e2e.

Former Member
0 Kudos

Hi Stephen,

Please can you clarify, is SAP building a tool that does auto-translation of ORACLE PLSQL to HANA SQLScript?

Which version is this going to be available from? Will there be any license charges involved?

Thanks

Sudarshan

Former Member
0 Kudos

Hello Sudarshan,

Yes, there is some plan to do this, but not officially a project yet. This will be based on newest version of Oracle and HANA, charge issue is not in consideration yet.

thanks,

Stephen Du

Former Member
0 Kudos

OK Thanks Stephen. This will be extremely useful for a lot of customers. Am directly involved in a few such projects where we start with a legacy Oracle Script and then re-build it for HANA! Wish the tool is available soon. Look forward to hearing more.  Regards, Sudarshan

Former Member
0 Kudos
Nice info Thanks !!!. Please you have any idea , oracle model clause equivalent to SAP hana. usually MODEL clause defines a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns. if this similar querying how we can do it via hana.

 

 
andy_haack
Discoverer
0 Kudos
Yes, using ROWID is not a big NO NO, as there are good reasons to use it, for example to achieve better performance avoiding an index scan in merge statements like this:

merge into xxen_report_pivot_v_recs xrpvr0 using (
select x.* from (
select
xrpvr.rowid row_id,
xrpvr.value,
min(xrpvr.value) keep (dense_rank first order by xrpvr.row_number) over (partition by xrpvr.column_number, lower(xrpvr.value)) new_value
from
xxen_report_pivot_v_recs xrpvr
) x
where
x.value<>x.new_value
) y
on
(y.row_id=xrpvr0.rowid)
when matched then update set
xrpvr0.value=y.new_value;
andy_haack
Discoverer
0 Kudos
Regarding point 9.: I can't speak for HANA, but for Oracle it is the other way around: For best performance, do as much work as possible in (one) SQL instead breaking a query into smaller chunks in PLSQL code.

If you execute a lot of small SQLs and fetch the results into variables in between, you essentially do the same work manually, which the database would do automatically for you - but more efficiently and with less and better maintainable code.

In short: If you can do it in one SQL, do it in one SQL.
Labels in this area