Local sort on CLOB possible issue

[MarkF]

I'm attempting to perform a local sort on a CLOB column. It seems that if I sort a small number of records (say 100), it works fine. If the number is higher than about 200 records it seems to freeze up (or take a really long time.) I appeared to get a stack overflow error at one point, but that hasn't repeated. Should CLOBs be sortable? Is there anything to be aware of? The CLOBs in this particular table are not huge, they vary from 100 to 8000 characters. I've tried it with both deferred and non-deferred (but cached) options and it doesn't seem to matter. Latest ODAC under D2010, UnicodeOCI on. Thanks for any help.

-Mark

[bork]

Hello

I cannot reproduce your issue. Try to create a new application, add a new button and add the following code on the button click event:

Code: Select all

var
  i, j: integer;
  len: integer;
  str: string;
  MySession: TOraSession;
  MyQuery: TOraQuery;
  StartTime, FinishTime: TDateTime;
begin
  Randomize;

  MySession := TOraSession.Create(self);
  MySession.Username := 'scott';
  MySession.Password := 'tiger';
  MySession.Server := 'ORA1110';
  MySession.Options.UseUnicode := True;
  MySession.Open;

  try
    OraSession1.ExecSQL('drop table test_table', []);
  except
  end;

  OraSession1.ExecSQL('CREATE TABLE test_table ( ' + #13 +
                      'ID NUMBER, ' + #13 +
                      'NAME VARCHAR2(50), ' + #13 +
                      'MYCLOB CLOB, ' + #13 +
                      'CONSTRAINT pk_test_table PRIMARY KEY (ID) ' + #13 +
                      ')', []);

  MyQuery := TOraQuery.Create(self);
  MyQuery.Session := MySession;
  MyQuery.SQL.Text := 'select * from test_table';
  MyQuery.Open;

  for i := 1 to 200 do
  begin
    str := '';
    for j := 0 to RandomRange(100, 8000) do
      str := str + Char(RandomRange(byte('a'), byte('z') + 1));

    MyQuery.Append;
    MyQuery.FieldByName('ID').AsInteger := i;
    MyQuery.FieldByName('Name').AsString := 'test ' + IntToStr(i);
    MyQuery.FieldByName('myclob').AsString := str;
    MyQuery.Post;
  end;

  ShowMessage('Start sorting');

  StartTime := Now;

  MyQuery.IndexFieldNames := 'myclob';

  FinishTime := Now;

  ShowMessage('Data is sorted: ' + FloatToStr((FinishTime - StartTime) * 24 * 60 * 60) + ' sec');
end;

On my computer this code was executed successfully and it takes 14 seconds. Please provide me the result of executing this code. If this code is working correctly then please modify this code to reproduce your issue.

[MarkF]

Thanks for looking into this. I also can't get your example to fail the way that mine does. Does it make sense that the sort takes 14 seconds (22 for me, I must have a slow computer!) to complete? If you modify your example to put the same data into the varchar field (shortening it to a max of 2000 chars) sorting the varchar field takes less than a second. Sorting the same data as a CLOB takes so much longer that I suspect that there is some kind of character data conversion going on or something along those lines. I suspect that the performance difference also may explain the problem I'm seeing (stack overflow issue.) I will try to make a test case, but I'd be interested in your thoughts on that.

-Mark

[MarkF]

I think I've got it. It looks like the problem is related to empty clobs and sorting. If you use the following in place of your code that generates the data:

Code: Select all

  MySession.ExecSQL('insert into test_table values (1, ''1 Clob has data'', ''Clob data.'')', []);
  MySession.ExecSQL('insert into test_table values (2, ''2 Clob is empty'', empty_clob())', []);
  MySession.ExecSQL('insert into test_table values (3, ''3 Clob is null'', '''')', []);
  MySession.Commit;

Then open with select * from test_table and try the sort. It immediately throws the stack overflow error along with a few access violations as well. Please let me know if that doesn't do it for you and I'll create a full test case. It would also be great if the performance issue was looked at.

Thanks,

-Mark

[bork]

Hello

Thank you for the information. We have reproduced this problem (performance & access violations) and fixed it. This fix will be included in the next ODAC build.

[MarkF]

Fantastic! Thanks for your help!

-Mark