| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: InterMedia: Wildcard searches are slow compared to MS SQL Server ?
Something is awry. interMedia Text should not be performing this
poorly.
As a quick sanity check, I performed a test myself using Oracle8i 8.1.5 Enterprise Edition on a 400MHz Pentium III NT system with 256 MB RAM (a normal desktop machine, no high-end server).
I created table foo (id number primary key, text varchar2(1000)). I inserted 146,988 rows into foo, with data from another application (user-entered text descriptions, not computer-generated data). Average length of text is 14 bytes, max length of text is 240 bytes, with 13,253 distinct values of text and 19,871 distinct tokens in the index.
I then created index text_idx on foo(text) indextype is ctxsys.context. I also analyzed the table foo and the index text_idx, in both cases, computing statistics.
Performing your same type of query, I see:
SQL> set timing on SQL> set autotrace on explain statistics; SQL> set define off SQL> select count(*) from foo where contains(text, 'a% & b%') > 0;
COUNT(*)
485
real: 2493
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=27)
1 0 SORT (AGGREGATE) 2 1 DOMAIN INDEX OF 'TEXT_IDX' (Cost=12 Card=1 Bytes=27)
Statistics
3472 recursive calls
4 db block gets
6115 consistent gets
200 physical reads
0 redo size
1174490 bytes sent via SQL*Net to client
503565 bytes received via SQL*Net from client
6936 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Granted, this is not the same data set you are testing. But this query for my ad-hoc test is under 2.5 seconds.
If you can, generate the EXPLAIN PLAN for the query you are performing to see if it is performing a full-table scan or actually using the domain index.
Are you using Enterprise Edition or Standard Edition? If Standard Edition, I am aware of bug 889385 which (unfortunately) impacts the query plan for interMedia Text.
Hope this helps.
On Tue, 14 Sep 1999 21:15:46 +0200, "Kristian Holmstrøm Mikkelsen" <memsi_at_get2net.dk> wrote:
>Hi,
>
>I'am currently evaluating the full-text capabilities in Oracle Intermedia
>and Microsoft SQL Server. Both servers are up and running on a Pentium II
>333MHz 256 Mb RAM.
>
>I have populated both databases with the same data and created a full-tekst
>index on that data. The data consists of 42000 rows each with 10 words.
>Every thing works as expected except when I perform a wildcard search like
>this one:
>
>SELECT *
>FROM words
>WHERE CONTAINS(text, 'a% & b%') > 0
>
>The query returns the expected 9 rows but it takes about 20 seconds, which
>is unacceptable and I am afraid that we are going to disqualify Oracle if
>the performance really is this poor. Executing the same query with Microsoft
>SQL Server takes only 2 seconds.
>
>Can this really be true ?
>
>Has anyone else experienced the same ?
>
>Are there any tip and tricks to improve performance (I am using default
>settings) ?
>
>---
>Kristian Holmstrøm Mikkelsen, Software developer
>The Stibo Group
>Søren Nymarksvej 3
>DK-8270 Højbjerg / Århus
>Denmark
>
>memsi_at_stibo.dk
>http://www.stibo.dk
>
>
>
>
>
Thanks!
Joel
Joel R. Kallman Oracle Service Industries
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |