Home » SQL & PL/SQL » SQL & PL/SQL » Sort operation : SQL end or ETL end ? (Oracle 10g)
Sort operation : SQL end or ETL end ? [message #285394] Tue, 04 December 2007 06:45 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Is it better to do sorting at the SQL level or using ETL transformation option ? I understand that Sort operation slows down SQL execution. Please Compare it with sorting at ETL end.
Re: Sort operation : SQL end or ETL end ? [message #285395 is a reply to message #285394] Tue, 04 December 2007 06:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> or using ETL transformation option
I do not understand this. Are you using a tool for ETL?
Generally speaking, sorting/computing should be done in database level. It was the databases are designed to do.
Re: Sort operation : SQL end or ETL end ? [message #285397 is a reply to message #285395] Tue, 04 December 2007 06:52 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Informatica is being used as ETL tool. Sorting can be done using Informatica also. I would like to know if that is better than using Order by in SQL.
Re: Sort operation : SQL end or ETL end ? [message #285398 is a reply to message #285397] Tue, 04 December 2007 06:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What does the Informatica documentation say? Have you performed any bench tests?
Re: Sort operation : SQL end or ETL end ? [message #285400 is a reply to message #285394] Tue, 04 December 2007 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is best to sort in SQL if you have memory enough on your database server.
If you have very efficient sorting algorithms in your ETL (and also memory enough on your client) then it is better to do it in your ETL.
This is not the case for Informatica.

Regards
Michel
Re: Sort operation : SQL end or ETL end ? [message #285401 is a reply to message #285400] Tue, 04 December 2007 06:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Good to know Michel (I'm not au fait with Informatica)
Re: Sort operation : SQL end or ETL end ? [message #285402 is a reply to message #285401] Tue, 04 December 2007 07:04 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks for the reply Michael. But isn't Sort painful at SQL end too ?
Re: Sort operation : SQL end or ETL end ? [message #285404 is a reply to message #285402] Tue, 04 December 2007 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sort algorithms are pretty efficient in Oracle.
Actually only real experts on data crunching beat it.

If you are interesting in these ones (and are an expert) see Oracle patents:
http://www.google.com/patents?as_q=&num=100&btnG=Google+Search&as_epq=&as_oq=&as_eq=&as_pnum=&as_vt=& as_pinvent=&as_pasgnee=Oracle+Corporation&as_pusc=&as_pintlc=&as_drrb_is=q&as_minm_is=1&as_miny_is=

Regards
Michel
Re: Sort operation : SQL end or ETL end ? [message #285406 is a reply to message #285402] Tue, 04 December 2007 07:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Well, nothing is free.
IMHO, doing it in the database side (server) is much better than in a tool.
Re: Sort operation : SQL end or ETL end ? [message #285409 is a reply to message #285404] Tue, 04 December 2007 07:28 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks Michael & Mahesh.
Re: Sort operation : SQL end or ETL end ? [message #285411 is a reply to message #285404] Tue, 04 December 2007 07:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Cheers
Re: Sort operation : SQL end or ETL end ? [message #285481 is a reply to message #285411] Tue, 04 December 2007 20:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If it were an extract from a live production database, you might want to be considerate to the OLTP users and push the load back onto the ETL server by using Informatica's sort.

Ross Leishman
Re: Sort operation : SQL end or ETL end ? [message #285811 is a reply to message #285481] Wed, 05 December 2007 13:59 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Best to benchmark it in both Informatica (PowerCenter) and database if it's important. In PowerCenter, the DTM buffer size, port widths and Ascii vs Unicode transfer mode all make a difference.

All things being equal, I'd do it at the SQL level becuase it's a simple addition to the SQL override statement as opposed to a separate transformation.
Previous Topic: role in annonymus PL SQL block vs Database Procedure
Next Topic: Is it possible to edit a procedure/function
Goto Forum:
  


Current Time: Sat Dec 03 18:05:52 CST 2016

Total time taken to generate the page: 0.15022 seconds