Home » Server Options » Text & interMedia » not contains and performance issue (10.2.0.1,apex 3.1)
not contains and performance issue [message #357747] Thu, 06 November 2008 08:03 Go to next message
pmapc
Messages: 46
Registered: July 2008
Member
hello

I'm using ctx indexes in my application, in some queries I'm using the contains statement to improve the performance on my select queries I have a query that in this query I've used "not contains" instead of contains to simulate the not like statement this query is too slow, I want to know the reasons and how can I improve the queries that in them I want to find data that does not contains some words?
Does anybody know why this happen?

Regards,

[Updated on: Thu, 06 November 2008 08:04]

Report message to a moderator

Re: not contains and performance issue [message #357773 is a reply to message #357747] Thu, 06 November 2008 09:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Assuming that you also have some words that are wanted as well as those that are not, then it is better to put the "not" inside the contains clause, instead of outside. Notice that the two queries below return the same results, but the second is generally more efficient. There are also many other factors that might influence your query speed.


SCOTT@orcl_11g> CREATE TABLE test_tab (test_col CLOB)
  2  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test_tab VALUES ('wanted')
  3  INTO test_tab VALUES ('unwanted')
  4  INTO test_tab VALUES ('other')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> -- slower:
SCOTT@orcl_11g> SELECT * FROM test_tab
  2  WHERE  CONTAINS (test_col, 'wanted') > 0
  3  AND    NOT CONTAINS (test_col, 'unwanted') > 0
  4  /

TEST_COL
--------------------------------------------------------------------------------
wanted

1 row selected.

SCOTT@orcl_11g> -- faster:
SCOTT@orcl_11g> SELECT * FROM test_tab
  2  WHERE  CONTAINS (test_col, 'wanted NOT unwanted') > 0
  3  /

TEST_COL
--------------------------------------------------------------------------------
wanted

1 row selected.

SCOTT@orcl_11g> 


Re: not contains and performance issue [message #357776 is a reply to message #357773] Thu, 06 November 2008 09:22 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Thank you for reply. In the query I just need "not contains" for example all results which not contains "car" . I don't know how should I used these kinds of queries for example you said:

WHERE CONTAINS (test_col, 'wanted NOT unwanted') > 0

but I don't have anything to put instead of "wanted" words. I mean need all the results that not contains a word. I have try some possibilities but none of them give me a good result for example using dot(.) instead of "wanted" word. any idea?
Re: not contains and performance issue [message #357781 is a reply to message #357776] Thu, 06 November 2008 09:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can use % but if you have a lot of data, you are likely to get an error that your wildcard expansion returns too many rows, even if you set your wildcard_maxterms to the maximum allowable for your version. Depending on the datatype of the rows you want to return, you may be able to use minus. I have demonstrated both below. If this doesn't help, if might help to see a realistic example, with create table statement, insert statement for sample data, and the result that you want based on that data and search criteria.


SCOTT@orcl_11g> CREATE TABLE test_tab (test_col VARCHAR2(30))
  2  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test_tab VALUES ('wanted')
  3  INTO test_tab VALUES ('unwanted')
  4  INTO test_tab VALUES ('other')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> SELECT * FROM test_tab
  2  WHERE  CONTAINS (test_col, '% NOT unwanted') > 0
  3  /

TEST_COL
------------------------------
wanted
other

2 rows selected.

SCOTT@orcl_11g> SELECT * FROM test_tab
  2  MINUS
  3  SELECT * FROM test_tab
  4  WHERE  CONTAINS (test_col, 'unwanted') > 0
  5  /

TEST_COL
------------------------------
other
wanted

2 rows selected.

SCOTT@orcl_11g> 

Re: not contains and performance issue [message #359342 is a reply to message #357781] Sat, 15 November 2008 01:06 Go to previous message
pmapc
Messages: 46
Registered: July 2008
Member
Thanks for reply but non of these solutions help me to solve the problem. It seems that ctx indexes don't understand "not" in their indexes and it is really wired that oracle didn't have a solution for these kinds of problems.
Any idea?
Previous Topic: File_DataStore Performance.
Next Topic: Text search in SQL query
Goto Forum:
  


Current Time: Thu Mar 28 12:28:05 CDT 2024