Home » Server Options » Text & interMedia » Index Type CTXSYS.CONTEXT not being used, any help? (Oracle 9i)
Index Type CTXSYS.CONTEXT not being used, any help? [message #386133] Thu, 12 February 2009 17:37 Go to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Hi all
we have an index SEARCH_CIDX which is of type CTXSYS.CONTENT on column search_keywords on the table SEARCH.

what ever query i do on the table with a where clause on search_keywords, it does a full table scan and takes about 50 sec -1 min of time to execute that query, sometimes more than that.

Now i want to analyze that index, but when i do ANALYZE, it says Analyzed sucessfully but DO NOT give me any stats and when i see the Index stats again, it shows "not analyzed".

Is there any other way to optimize such query by doing something with such a type of Index?

here is the Index created script

CREATE INDEX SEARCH_CIDX ON SEARCH
(SEARCH_KEYWORDS)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('memory 30m STORAGE SEARCH_STORAGE');
Re: Index Type CTXSYS.CONTEXT not being used, any help? [message #386159 is a reply to message #386133] Thu, 12 February 2009 22:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It might be better to use dbms_stats instead of analyze. Also, if you have done inserts, updates, or deletes since you created your index, your may need to synchronize, optimize, rebuild, or drop and recreate your index. Sometimes using the index is not the best execution plan and a full table scan is more efficient. It might help if you would post a copy and paste of exactly what you have done and the results, similar to what I have shown below, which shows that the index was analyzed and used in the query. It may be that your specific query is somehow not able to use the index. It is difficult to guess without seeing exactly what you are doing.

    
SCOTT@orcl_11g> CREATE TABLE search AS
  2  SELECT object_name AS search_keywords
  3  FROM   user_objects
  4  /

Table created.

SCOTT@orcl_11g> CREATE INDEX SEARCH_CIDX
  2  ON SEARCH (SEARCH_KEYWORDS)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'SEARCH')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT last_analyzed
  2  FROM   user_indexes
  3  WHERE  index_name = 'SEARCH_CIDX'
  4  /

LAST_ANAL
---------
12-FEB-09

SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT COUNT (*)
  2  FROM   search
  3  WHERE CONTAINS (search_keywords, 'test') > 0
  4  /

  COUNT(*)
----------
        15


Execution Plan
----------------------------------------------------------
Plan hash value: 3283167613

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |    19 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |             |     1 |    19 |            |          |
|*  2 |   DOMAIN INDEX   | SEARCH_CIDX |    15 |   285 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("SEARCH_KEYWORDS",'test')>0)

SCOTT@orcl_11g> SET AUTOTRACE OFF



Re: Index Type CTXSYS.CONTEXT not being used, any help? [message #386318 is a reply to message #386133] Fri, 13 February 2009 10:20 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you very much Barbara for your reply.

I am just trying to do the general queries, something like..

select * from search where search_keywords like 'ccna%';

and it does a full table scan.

Actually the problem is, the search table is indirectly used in the company website to search some products using the keyword_search, and they are getting the results real slow, and hence i was asked to check the indexes and i saw that the stats of this index are not updated.

And all that you asked me to do, can i do it on TOAD or have to do in SQL PLUS?


-thanks
Re: Index Type CTXSYS.CONTEXT not being used, any help? [message #386322 is a reply to message #386133] Fri, 13 February 2009 11:53 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Also, i did the below as SYS user:

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(OwnName =>'<name>',IndName=>'SEARCH_CIDX',Estimate_Percent =>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,Degree => 4,No_Invalidate => FALSE);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

and checked the stats but no results:

SQL> select last_analyzed from user_indexes where index_name='SEARCH_CIDX';

no rows selected


any suggestions ?

Please help, its quite urgent.


-thanks

[Updated on: Fri, 13 February 2009 11:55]

Report message to a moderator

Re: Index Type CTXSYS.CONTEXT not being used, any help? [message #386323 is a reply to message #386322] Fri, 13 February 2009 12:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You are confusing regular indexes and regular queries that use them such as the query using like that you posted with context indexes and queries that use them such as the query using contains that I posted. Only a query that uses contains will ever use a context index. Also, you need to just analyze the table, so that by default all of the appropriate dependent pieces are analyzed. When you try to analyze only specific parts, you miss other important parts. Please see the revised demonstration below that uses two separate indexes and two separate queries and notice which query uses which index.

SCOTT@orcl_11g> CREATE TABLE search AS
  2  SELECT object_name AS search_keywords FROM user_objects
  3  /

Table created.

SCOTT@orcl_11g> INSERT INTO search (search_keywords) VALUES ('ccnatest')
  2  /

1 row created.

SCOTT@orcl_11g> CREATE INDEX search_regular_index ON SEARCH (SEARCH_KEYWORDS)
  2  /

Index created.

SCOTT@orcl_11g> CREATE INDEX search_context_index ON SEARCH (SEARCH_KEYWORDS)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'SEARCH')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT index_name, last_analyzed
  2  FROM   user_indexes
  3  WHERE  index_name LIKE 'SEARCH%INDEX'
  4  /

INDEX_NAME                     LAST_ANAL
------------------------------ ---------
SEARCH_CONTEXT_INDEX           13-FEB-09
SEARCH_REGULAR_INDEX           13-FEB-09

SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT * FROM search WHERE search_keywords LIKE 'ccna%'
  2  /

SEARCH_KEYWORDS
----------------------------------------------------------------------------------------------------
ccnatest


Execution Plan
----------------------------------------------------------
Plan hash value: 2521177506

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| SEARCH_REGULAR_INDEX |     1 |    19 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SEARCH_KEYWORDS" LIKE 'ccna%')
       filter("SEARCH_KEYWORDS" LIKE 'ccna%')

SCOTT@orcl_11g> SELECT * FROM search WHERE CONTAINS (search_keywords, 'ccna%') > 0
  2  /

SEARCH_KEYWORDS
----------------------------------------------------------------------------------------------------
ccnatest


Execution Plan
----------------------------------------------------------
Plan hash value: 2227200634

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |    20 |   380 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SEARCH               |    20 |   380 |    11   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | SEARCH_CONTEXT_INDEX |       |       |     9   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("SEARCH_KEYWORDS",'ccna%')>0)

SCOTT@orcl_11g> SET AUTOTRACE OFF

Re: Index Type CTXSYS.CONTEXT not being used, any help? [message #386333 is a reply to message #386133] Fri, 13 February 2009 17:07 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you for the reply Barbara.

I do understand and understood the difference between the two explain plans.

The query with "where contains' clause ONLY uses the domain index and not the regular query.

The problem here is i DO NOT know whats the query going on in the background for the product search using the search_keywords, i only know that they are doing some product search using the search_keywords which is taking a long time to load (about 30-40 secs), about which they are complaining.

Now i want to make it in such a way that any query on the search table using the where clause on the search_keywords column should be very fast, and there is no general index on the search_keywords column, except for that domain index (Search_cidx).

So what do i do to improve the performance?

The only way is to build a general index on that column?
OR is there any thing else you would suggest me?

-thanks
Munna


[Updated on: Fri, 13 February 2009 17:07]

Report message to a moderator

Re: Index Type CTXSYS.CONTEXT not being used, any help? [message #386358 is a reply to message #386333] Fri, 13 February 2009 23:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you have some sort of application that is being used for querying, then all the users should be entering is the string that they want to search for, which would be used as a bind variable in a provided query. It sounds like you don't have such an application or control and users are entering ad-hoc queries. If that is the case, then all you can do is provide both types of indexes, so that the optimizer can choose whichever index is appropriate for the query entered. It would then be up to the users to write efficient queries. It would probably help if you would find out what queries are being entered by either asking the people complaining that the queries are slow what they are entering or if they are using an application then examining the application to find the query or perhaps just checking your shared pool to see what queries are in it. If you don't have a regular index and they are not using contains, then that is the obvious problem and you need a regular index. If they are not ever using contains, then you don't need the context index.

I should also mention that the type of index may not be the only problem. There may be other issues, such as whether the index is currently synchronized and optimized, whether statistics are current, whether the queries are using bind variables, and other aspects of the queries. You really need to find out what specific queries are slow and get an explain plan for them, so you can see what execution plan the optimizer is using.

[Updated on: Fri, 13 February 2009 23:09]

Report message to a moderator

Re: Index Type CTXSYS.CONTEXT not being used, any help? [message #386448 is a reply to message #386133] Sun, 15 February 2009 02:20 Go to previous message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you so much for your reply Barbara.

I will let you know if the issue is resolved by creating a general index on the same column.

-thanks
Munna
Previous Topic: Offset characters in Snippet for html files
Next Topic: Oracle 10.2.0.1.0 AUTO_FILTER not works
Goto Forum:
  


Current Time: Fri Mar 29 09:50:35 CDT 2024