Forum Stats

  • 3,783,341 Users
  • 2,254,760 Discussions
  • 7,880,368 Comments

Discussions

Varchar2 vs. Raw for hex values

556095
556095 Member Posts: 2
edited Feb 23, 2010 4:06PM in General Database Discussions
I'm working with a database (10.2.0.3.0 (32-bit Windows)) that needs to store a large number of 128-bit hexadecimal values. The two obvious candidates for these fields are RAW and VARCHAR2. RAW seems to be the more natural choice, as it is designed for storing binary data. There would seem to be less storage overhead and it will act as a constraint, ensuring that only hex values are inserted.

However, a simple test makes me wonder if there will be a performance issue related to using the RAW datatype. I created a table with both VARCHAR2 and RAW columns, then populated them with 2000 sets of hex values (each row contained the same value in both fields, but each row is unique). I then created an index on each column and compared the explain plan for selecting a value from each column (using hextoraw for the RAW column, to ensure that the index is used). For unique indexes, the cardinality is identical for both plans. However, for non-unique indexes the RAW index assumed a cardinality of 8 for the index and 20 for the table access, whereas the VARCHAR2 index correctly identified the cardinality as 1.

Am I correct in thinking that, for this data, a VARCHAR2 column is going to scale better than a RAW column? Also, does anyone know why this is?

--
Allan
Tagged:

Answers

  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    I can't answer your question directly but I would recommend using DBMS_STATS.SET_ functionality to learn what the CBO thinks of the idea.

    You might want to see if any of the functionality in UTL_RAW might be of value too.
    http://www.morganslibrary.org/reference/utl_raw.html
    damorgan
  • 556095
    556095 Member Posts: 2
    Apparently, the issue was that, while I had analyzed the indexes, I had not analyzed the table. Once that was done, the cardinality came out the same for both tables.

    Also, splitting the experiment into tables, one with a raw key and a payload column and the other with a varchar2 key and the same payload column revealed that the raw key resulted in a smaller row size, as expected.
This discussion has been closed.