Possible Duplicate:
Oracle 10g : Is transfer via database link compressed ? is it possible?
I have a huge SQL query with a lot of fields in it. Most of data in this result are duplicates of each selves.
Here is part of SQL result for your intelligence:
RECORD_ID DATE_REC CAB PERSON_ID CODE1 CODE4 CODE2 CODE5 CODE5_VALUE CODE5_SUBVALUE1 CODE5_SUBVALUE2 CODE5_SUBVALUE3 CODE6_SUBVALUE1 CODE6_SUBVALUE2 CODE6_SUBVALUE3
1500747 31.10.2012 2260784 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW PETERSON EARL TOMAS
1500750 31.10.2012 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500711 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500712 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500713 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500714 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500715 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500716 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500717 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500718 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500745 31.10.2012 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500746 31.10.2012 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500748 31.10.2012 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500749 31.10.2012 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500709 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
1500710 29.10.2012 0;4;28; 5664 26 552ec2af-c9d3-4f18-bc84-68d641fdbc5f HF2OUGVG11 JOHNSON DAVID MATTHEW
As you can see from request above, there is a lot of duplicate data. Will Oracle compress it while serializing to transmit it over network?
SQL*Net performs a limited level of de-duplication. That is, it compares the current row with the last row, and if the value in a given column in the current row contains the same value as that column in the sent row doesn't transmit the value. It then replaces all the duplications at the receiving end.
This means the sort order is crucial. However, if the sample date you have posted is representative of your query's output I think you should see a lot of de-duplication with your current ORDER BY clause. However, there may be alternate orderings which would offer greater compression: you may want to benchmark these if your "huge SQL query" exceeds a high enough value of "huge".
Jonathan Lewis has more details in a blog article, including a demonstration which you can use to evaluate the de-duplication in your case. You should read it.