2

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?

Community
  • 1
  • 1
kseen
  • 359
  • 8
  • 56
  • 104
  • 1
    @nneonneo That question is about transferring between 2 databases. My question is about receiving on client side from server. There is a qoute difference. – kseen Oct 24 '12 at 05:51
  • I think this question deserve at least an yes or no with a little explanation. – Florin Ghita Oct 24 '12 at 06:18
  • Client/Server vs. DbLink is no different. The protocol is the same in both cases (with a DbLink another server is the "client"). –  Oct 24 '12 at 06:38

1 Answers1

5

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Thank you for your response! I just read Jonathan Lewis' article on de-duplication. At the end of his article he mention object method of restructuring that query. Is it really worth to do in my case? Maybe it will produce a less of traffic? – kseen Oct 24 '12 at 08:17
  • @kseen - I fear you have misread his observation. He is saying that bundling child records in objects (embedded nested tables) rather than just joining them to their parents *won't* save network traffic, precisely because Oracle Net will compress the parent records (assuming the query is sorted by the parent's ID which is likely to be the case). – APC Oct 25 '12 at 07:31