Results 1 to 7 of 7

Thread: Indexing BLOB's

  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: Indexing BLOB's

    Hi all,

    Maybe this is a dumb question, but just for curiosity, is ther any advantage on indexing BLOB fields? or CLOB's?

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    While I could see a reason to index CLOBs I can't see any reason why you want to index BLOBs as they contain binary data.

    In order to take advantage of an index, the column must be referenced in a WHERE condition (well at least in most of the cases) which you cannot do with a BLOB column.

    The only thing that might work for BLOBs is to create a function based index e.g. on length(blob_column) to find BLOBs of a certain size easier.

    What is it that you want to achieve?

  3. #3
    Join Date
    May 2004
    Posts
    95
    the only thing that I want is, a better perfomance doing select's on a table just with ID and BLOB field.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Then you need to index the ID field, not the BLOB

  5. #5
    Join Date
    May 2004
    Posts
    95
    ok.. that was done by default. ID primary key and by inherit indexed.

    BTW, is there any Oracle command to see the content-type, size, and extension of a BLOB field?

    Thanks.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by xixo
    BTW, is there any Oracle command to see the content-type, size, and extension of a BLOB field?
    A BLOB field does not have a content-type or an extension. It's just a collection of bytes. That's it.
    What you put into it and how you interpret the contents is completely up to you.

    To get the size of the BLOB use the length() function

  7. #7
    Join Date
    May 2006
    Posts
    132
    You can most certainly index BLOBs. If you want to index BLOBs you can consider implementing Oracle Text indexes which utilize the AUTO_FILTER to index the binary data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •