Oracle 11gR2 has been released – and with column oriented storage option

You may already have noticed that Oracle 11gR2 for Linux is available for download on Oracle.com website, with documentation.

And this document ends speculation about whether Oracle 11.2 will support column-oriented storage – yes it will:

http://www.oracle.com/technology/products/database/oracle11g/pdf/oracle-database-11g-release2-overview.pdf

However, this is apparently available on Exadata storage only as a new error message below indicates:

ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
Cause: An attempt was made to use hybrid columnar compression on unsupported storage.
Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.

Update: Kevin Closson mentioned that 11gR2 doesn’t really have column oriented storage as some other products like Vertica’s and Sybase IQ use, but its rather just column oriented compression option where storage is still organized by row but individual fields in these rows use compression dictionaries whichcan span multiple block boundaries (we’ll thats my interpretation at least).

The 11gR2 release overview doc seems to be wrong in this case, as it says:

Hybrid columnar compression is a new method for organizing how data is stored. Instead of
storing the data in traditional rows, the data is grouped, ordered and stored one column at a time.

Read Kevin’s note here:

http://kevinclosson.wordpress.com/2009/09/01/oracle-switches-to-columnar-store-technology-with-oracle-database-11g-release-2/

ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
Cause: An attempt was made to use hybrid columnar compression on unsupported storage.

Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.

This entry was posted in Oracle and tagged , . Bookmark the permalink.

19 Responses to Oracle 11gR2 has been released – and with column oriented storage option

  1. Hi Tanel,

    It’s not really correct to call it “column oriented storage.” It is column oriented compression which means, in essence, the compression algorithm scans down columns as opposed to row-oriented compression. It is quite effective.

    I see a lot of data that compresses at, say, 5:1 with standard compression and 9 and sometimes 10:1 with hybrid colomnar compression (COMPRESS FOR QUERY HIGH).

  2. “Hybrid columnar compression is a new method for organizing how data is stored. Instead of
    storing the data in traditional rows, the data is grouped, ordered and stored one column at a time.”

    That sounds a lot like column-oriented storage to me.

  3. Aha, just seen your post: “Rows are still used. They are stored in an object called a Compression Unit. Compression Units can span multiple blocks. Like values are stored in the compression unit with metadata that maps back to the rows.” In Sybase IQ etc as I understand it, rows are only constructed at runtime for query results.

  4. Olivier says:

    From a pure business perspective i do not consider this as a shame. And even if it’s not from a business perspective, there may be good reasons for this option not being included. It may first has been designed for Exadata and also considered for 11gR2 and for possible technical reasons was not finally included.

  5. Tytus Krajecki says:

    FYI: I just read another blog post about Oracle 11gR2 and saw that you are citing there: http://bigdatamatters.com/bigdatamatters/2009/09/oracle-11g.html :)

  6. Was it worth beta testing the feature? Documented elsewhere SQL Language Reference CREATE TABLE
    “See Also:
    Oracle Exadata Storage Server Software documentation for more information on hybrid columnar compression, which is a feature of Oracle Exadata”.
    Similar comment also in 2 Day + Data Warehousing Guide.

  7. This is definitely an interesting new feature. We did a little work with a small company that was developing there own column-oriented database a couple of years back. A big part of their “schtick” was the tokenized compression. So it may be that Oracle was able to get most of the benefit without the huge amount of work required to change the actual storage format.

    It will be interesting to see how it pans out. There are certainly performance gains to be had in large DW environments. Makes sense that it’s tied to the Exadata product.

    By the way, I’ve heard rumors that the Oracle sales force is being strongly encouraged to sell the Exadata product. Rumor has it that the tech sales guys in one region were told that each one was expected to sell at least one Exadata system this year. They definitely seem to be doing lot’s “trials”. Very interesting, especially considering the Sun acquisition.

    Kerry

  8. Tanel Poder says:

    Btw, I wasn’t beta testing 11gR2, I was testing few releases before that but it added little value as most major features needed to be re-tested after final release anyway.

    I just commented on the 11gR2 features overview doc which had a misleading statement in it.

  9. to be clear…not “misleading”…just unclear

  10. Tanel Poder says:

    Heh-heh, it sure was misleading to me, I would not have written my (misleading) blog entry otherwise! ;)

    There’s one statement in the doc which says that data is “stored one column at a time”.

    Yep it sure mentioned the “hybrid” keyword and also that the data is compressed and grouped but the “is stored one column at a time” part alone did lead me to think that it was column oriented storage.

    But on the other hand, I wrote my blog entry just after skimming through the new features doc in few minutes, so I didn’t dig too deep to find out what’s really going on…

  11. Tanel Poder says:

    Kevin, of course I didn’t mean “deliberately misleading” or “misleading due some evil plan to conquer the world” when I wrote misleading here!

  12. I understand, Tanel. It is true data “is stored one column at a time.” That phrase doesn’t insinuate rows are totally out of the picture though. Picture, for instance, a hypothetical database implementation where rows have nothing in them except pointers to tablespaces where columns are stored on per partition basis and then sub-partitioned by range or something. I could refer to such a database as organizing the data in a columnar orientation but I still have rows. Now, this hypothetical is not aimed at describing what HCC does, but it is an easily understood example of a hybrid.

  13. Tanel Poder says:

    Ok thanks! Time to take a weekend of and do some research I think ;-)

  14. Daniel Abadi says:

    Oracle’s is the third hybrid row/column storage scheme announced in the last month. It’s time for a taxonomy: http://dbmsmusings.blogspot.com/2009/09/tour-through-hybrid-columnrow-oriented.html

  15. Murray says:

    I wonder how this will affect vendors like SAND Technology (www.sand.com)? It seems so many companies out there do not want to upgrade on Oracle due to costs, but an independent product like SAND could be a nice compromise…

  16. Hello Tanel Poder

    Great blog with great blog roll. I must say as an Oracle 9i, 10g and 11g OCP and beginning Oracle Partner, I am still in a personal recovery mode, but got some great idea’s from your blog. Appreciate it, thanks.

    Drs. Albert Spijkers
    DBA consulting
    Director
    Tel. : 06-26839051
    http://www.dbaconsulting.nl
    info@dbaconsulting.nl

  17. Matt McGuire says:

    I just want to compress the entire table and I am getting ORA-64307. I am not even trying to compress a single column. I have used compression in 10G just fine, but using the new syntax in 11G is not working out so well.

    Running: alter table compress for query high;

    I want new data to be compress and my old 10G syntax does not accomplish that.

  18. Tanel Poder says:

    Hi Matt,

    You are using the wrong syntax, this option I mentioned above is meant to be used only on Exadata platform.

    You probably should use this (if you have the advanced compression licenses):

    alter table t compress for all operations;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>