Home » RDBMS Server » Performance Tuning » Joining Oracle Text results with multiple tables / Performance Problems (Oracle 9i, Win Server 2003)
icon5.gif   Joining Oracle Text results with multiple tables / Performance Problems [message #309472] Thu, 27 March 2008 11:04 Go to next message
ltnuke
Messages: 2
Registered: March 2008
Junior Member
I am having a problem with a SQL Query that is taking too long to execute (46 seconds). When the SQL Query is split into two separate queries each one runs at less than 1 second each. (Note, the queries below are not exactly how we have them in our system it is an example)

We have two tables, one that holds a clob with the text that we are searching over (Table 1). We are using a ctxsys.context index to index that text because we use a contains statement to search the clob. Table 2 holds permissions for the text that is being searched. There is an index on Table 2's username column (a bitmap index) and an index on Table 2's Table1RowID as well.

Table 1
-----------
RowID (Num)
TextData (CLOB)

Table 2
-----------
RowID (Num)
Table1RowID (Num)
Username (VarChar2)

Our SQL query looks like this:

QUERY1: Select * from Table1 join Table2 on RowID=Table1RowID where contains(CLOB,'test text', 1) >0 and Username='testuser'; (46 seconds)

As stated above, when the queries are separated each one runs very quickly (less than one second each).

QUERY2: Select * from Table 1 where contains(CLOB,'test text',1) > 0; (takes less than 1 second)

and

QUERY3: Select * from Table 2 where Username='testuser'; (takes less than 1 second)

We looked at the explain plan for QUERY2 and noticed that oracle text index is being used. We also looked at QUERY3 and the bitmap index was used. However, when we look at the explain plan for QUERY1 it used the Oracle Text index but not the bitmap index on the username column. We do not know why it is not using the bitmap index in QUERY1 (the combined query).

Any help would be very much appreciated.
Re: Joining Oracle Text results with multiple tables / Performance Problems [message #309527 is a reply to message #309472] Thu, 27 March 2008 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I assume that your ID columns are numeric and not actually ROWID datatype and that you have primary and foreign keys. Hopefully your text index is periodically optimized, rebuilt, or dropped and recreated, so that it is not fragmented and you have current statistics on the tables and indexes. Hopefully, you are also using bind variables in your actual queries.

It sounds like what you want to try to get the optimizer to do is use the separate indexes on the separate queries, then join the results. I have attempted to recreate your problem below. The last query is the one that I am suggesting that you try, which puts the queries in two separate subqueries with two separate index hints, then joins the results. The explain plan shows that it uses both indexes. If this does not help, then please provide a similar script that we can use to recreate the problem on our systems.

-- tables:
SCOTT@orcl_11g> CREATE TABLE table1
  2    (id	   NUMBER,
  3  	textdata   CLOB)
  4  /

Table created.

SCOTT@orcl_11g> CREATE TABLE table2
  2    (table1id   NUMBER,
  3  	the_user   VARCHAR2 (30))
  4  /

Table created.


-- test data:
SCOTT@orcl_11g> INSERT INTO table1 VALUES (1, 'test text')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO table1 SELECT object_id, object_name FROM all_objects WHERE object_id IS NOT NULL
  2  /

68405 rows created.

SCOTT@orcl_11g> INSERT INTO table2 VALUES (1, USER)
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO table2 SELECT object_id, object_name FROM all_objects
  2  /

68405 rows created.


-- constraints and indexes:
SCOTT@orcl_11g> ALTER TABLE table1 ADD CONSTRAINT table1_id_pk PRIMARY KEY (id)
  2  /

Table altered.

SCOTT@orcl_11g> ALTER TABLE table2 ADD CONSTRAINT table2_table1id_fk FOREIGN KEY (table1id) REFERENCES table1 (id)
  2  /

Table altered.

SCOTT@orcl_11g> CREATE INDEX table1_textdata_idx ON table1 (textdata)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> CREATE BITMAP INDEX table2_the_user_idx ON table2 (the_user)
  2  /

Index created.


-- statistics:
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TABLE1')

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.


-- original queries with bind variable:
SCOTT@orcl_11g> VARIABLE search_string VARCHAR2(30)
SCOTT@orcl_11g> EXEC :search_string := 'test text'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SET TIMING ON
SCOTT@orcl_11g> COLUMN textdata FORMAT A30 WORD_WRAPPED
SCOTT@orcl_11g> SELECT *
  2  FROM   table1
  3  WHERE  CONTAINS (table1.textdata, :search_string, 1) > 0
  4  /

        ID TEXTDATA
---------- ------------------------------
         1 test text

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 239443893

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |    34 |  4454 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1              |    34 |  4454 |    11   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | TABLE1_TEXTDATA_IDX |       |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   2 - access("CTXSYS"."CONTAINS"("TABLE1"."TEXTDATA",:SEARCH_STRING,1)>0)

SCOTT@orcl_11g> SELECT *
  2  FROM   table2
  3  WHERE  table2.the_user = USER
  4  /

  TABLE1ID THE_USER
---------- ------------------------------
         1 SCOTT

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3255866388

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     2 |    58 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TABLE2              |     2 |    58 |     1   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                     |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | TABLE2_THE_USER_IDX |       |       |            |          |
----------------------------------------------------------------------------------------------------

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

   3 - access("TABLE2"."THE_USER"=USER@!)

SCOTT@orcl_11g> SELECT *
  2  FROM   table1
  3  JOIN   table2 ON table1.id = table2.table1id
  4  WHERE  CONTAINS (table1.textdata, :search_string, 1) > 0
  5  AND    table2.the_user = USER
  6  /

        ID TEXTDATA                         TABLE1ID THE_USER
---------- ------------------------------ ---------- ------------------------------
         1 test text                               1 SCOTT

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1272484892

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     2 |   320 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                     |       |       |            |          |
|   2 |   NESTED LOOPS                 |                     |     2 |   320 |     9   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | TABLE2              |     2 |    58 |     1   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                     |       |       |            |          |
|*  5 |      BITMAP INDEX SINGLE VALUE | TABLE2_THE_USER_IDX |       |       |            |          |
|*  6 |    INDEX UNIQUE SCAN           | TABLE1_ID_PK        |     1 |       |     0   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID  | TABLE1              |     1 |   131 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   5 - access("TABLE2"."THE_USER"=USER@!)
   6 - access("TABLE2"."TABLE1ID"="TABLE1"."ID")
   7 - filter("CTXSYS"."CONTAINS"("TABLE1"."TEXTDATA",:SEARCH_STRING,1)>0)


-- suggested query:
SCOTT@orcl_11g> SELECT t1.*, t2.*
  2  FROM   (SELECT /*+ index (table1 table1_textdata_idx) */ *
  3  	     FROM   table1
  4  	     WHERE  CONTAINS (table1.textdata, :search_string, 1) > 0) t1,
  5  	    (SELECT /*+ index (table2 table2_the_user_idx) */ *
  6  	     FROM   table2
  7  	     WHERE  table2.the_user = USER) t2
  8  WHERE  t1.id = t2.table1id
  9  /

        ID TEXTDATA                         TABLE1ID THE_USER
---------- ------------------------------ ---------- ------------------------------
         1 test text                               1 SCOTT

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 894856207

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     2 |   320 |    13   (0)| 00:00:01 |
|*  1 |  HASH JOIN                    |                     |     2 |   320 |    13   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | TABLE2              |     2 |    58 |     1   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS|                     |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | TABLE2_THE_USER_IDX |       |       |            |          |
|   5 |   TABLE ACCESS BY INDEX ROWID | TABLE1              |    34 |  4454 |    11   (0)| 00:00:01 |
|*  6 |    DOMAIN INDEX               | TABLE1_TEXTDATA_IDX |       |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - access("TABLE2"."TABLE1ID"="TABLE1"."ID")
   4 - access("TABLE2"."THE_USER"=USER@!)
   6 - access("CTXSYS"."CONTAINS"("TABLE1"."TEXTDATA",:SEARCH_STRING,1)>0)

SCOTT@orcl_11g> 

Re: Joining Oracle Text results with multiple tables / Performance Problems [message #309532 is a reply to message #309527] Thu, 27 March 2008 15:50 Go to previous messageGo to next message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
Barbara,

Would you test this Query? (Since you already have the test environment and currently I can't access an Oracle DB):

SELECT /*+ index (table2 table2_the_user_idx) */ t2.*, q1.*
  FROM (
        SELECT /*+ index (table1 table1_textdata_idx) */ t1.* 
          FROM table1 t1
         WHERE CONTAINS (t1.textdata, :search_string, 1)
       ) q1,
       table2 t2
 WHERE t2.the_user = USER
   AND q1.id = t2.table1id


Just for curiosity.

Note: if the SQL is invalid... just ignore it. (And please forgive this silly man)
Re: Joining Oracle Text results with multiple tables / Performance Problems [message #309538 is a reply to message #309532] Thu, 27 March 2008 16:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
-- tafer's query with slight correction:
SCOTT@orcl_11g> SELECT /*+ index (table2 table2_the_user_idx) */ t2.*, q1.*
  2    FROM (
  3  	     SELECT /*+ index (table1 table1_textdata_idx) */ t1.*
  4  	       FROM table1 t1
  5  	      WHERE CONTAINS (t1.textdata, :search_string, 1) > 0 -- added > 0
  6  	    ) q1,
  7  	    table2 t2
  8   WHERE t2.the_user = USER
  9  	AND q1.id = t2.table1id
 10  /

  TABLE1ID THE_USER                               ID TEXTDATA
---------- ------------------------------ ---------- ------------------------------
         1 SCOTT                                   1 test text

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1272484892

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     2 |   320 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                     |       |       |            |          |
|   2 |   NESTED LOOPS                 |                     |     2 |   320 |     9   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | TABLE2              |     2 |    58 |     1   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                     |       |       |            |          |
|*  5 |      BITMAP INDEX SINGLE VALUE | TABLE2_THE_USER_IDX |       |       |            |          |
|*  6 |    INDEX UNIQUE SCAN           | TABLE1_ID_PK        |     1 |       |     0   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID  | TABLE1              |     1 |   131 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   5 - access("T2"."THE_USER"=USER@!)
   6 - access("T2"."TABLE1ID"="T1"."ID")
   7 - filter("CTXSYS"."CONTAINS"("T1"."TEXTDATA",:SEARCH_STRING,1)>0)

SCOTT@orcl_11g> 

Re: Joining Oracle Text results with multiple tables / Performance Problems [message #309540 is a reply to message #309538] Thu, 27 March 2008 16:45 Go to previous messageGo to next message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
Barbara,

Thanks a lot for your kind (and super fast) help!

Heh... Now to do some study to understand what happened there.

(And I'm so sorry for that mistake!)

[Updated on: Thu, 27 March 2008 16:51]

Report message to a moderator

Re: Joining Oracle Text results with multiple tables / Performance Problems [message #309559 is a reply to message #309540] Thu, 27 March 2008 21:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you ignore the bitmap index, and create a concatenated index on Table2(RowIDCol, Username) it should be faster still.

Ross Leishman
Re: Joining Oracle Text results with multiple tables / Performance Problems [message #310553 is a reply to message #309559] Tue, 01 April 2008 11:03 Go to previous messageGo to next message
ltnuke
Messages: 2
Registered: March 2008
Junior Member
Wow, thanks for the help, everyone.

Unfortunately, when we used the query that was provided with the broken up sql queries and the index hints we still get about the same search time. It's absolutely baffling to me because if we run the "contains" and the table 2 clause in separate sql statements we get super fast return times. The index hints don't appear to be doing anything because the explain plan still shows that the bitmap index on table 2 is not being used when we use the combined query.

In case I didn't mention it before we are using Oracle 9i, I don't know if this could be part of the problem.

Sad
Re: Joining Oracle Text results with multiple tables / Performance Problems [message #388656 is a reply to message #310553] Wed, 25 February 2009 20:54 Go to previous messageGo to next message
surferal41
Messages: 3
Registered: February 2009
Junior Member
Hello Luke,

Did you ever get this resolved? Ie. SQL performance running a query with CONTAINS in a separte pass ( 2 separate queries) as opposed to running the query with CONTAINS together (1 query).

Our particular details follow. Any insight would be appreciated!!!

Thanks
Al Rojas
Oracle Architect
NAVFAQ Pointe Hueneme
cell 949-291-8802

DETAILS--------SR Reference------7288776.993
/*
* QUERY RUNTIME: milliseconds
*/
create table CONTEXT_HITS as
SELECT d.subscriber_id
FROM portal.wwdoc_document$ d,
WHERE contains (d.blob_content, '(${navy})', 15) > 0;

However, when we simply JOIN our blob table "portal.wwdoc_documen$" with
another table, "portal.wwv_docinfo", we crash our server (CPU spike etc.) as
documented with the query below.

*
* QUERY RUNTIME: two (2) minutes
*/
SELECT ctxsys.score (15) scr,
di.NAME
FROM portal.wwdoc_document$ d, portal.wwv_docinfo di WHERE di.LANGUAGE =
'us'
AND di.subscriber_id = d.subscriber_id
AND di.NAME = d.NAME
AND di.thingid = 0
AND contains (d.blob_content, '(${navy})', 15) > 0;

As we all know, when we join the previous ID's returned from a previous
query running CONTAINS into a temp table CONTEXT_HITS, we are okay.

Therefore, it appears the "issue" is submitting a PLSQL query containing
BOTH a CONTAINS "portal.wwdoc_document$" and JOINING with another table
"portal.wwv_docinfo" as documented above.

If we break it up into two (2) separte queries, we are okay as documented
below.

/*
* QUERY RUNTIME: milliseconds
*/
SELECT ctxsys.score (15) scr,
di.NAME
FROM CONTEXT_HITS d, portal.wwv_docinfo di WHERE di.LANGUAGE = 'us'
AND di.subscriber_id = d.subscriber_id
AND di.NAME = d.NAME
AND di.thingid = 0;


Re: Joining Oracle Text results with multiple tables / Performance Problems [message #388659 is a reply to message #309472] Wed, 25 February 2009 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
surferal41,

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Thanks!
Re: Joining Oracle Text results with multiple tables / Performance Problems [message #388682 is a reply to message #388656] Wed, 25 February 2009 22:55 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
All I can suggest is putting the queries on each table in separate inline views with index hints, then joining them in an outer query, as discussed earlier in this thread. You might also add "and rownum > 0" to each inline view, to try to materialize it. So, it would be something like below. And, of course, make sure that your index is synchronized and optimized and statistics are current.

SELECT context_hits.scr, context_hits.name
FROM   (SELECT /*+ index (d your_context_index_name) */
               score (15) scr, d.subscriber_id, d.name
        FROM   portal.wwdoc_document$ d,
        WHERE  contains (d.blob_content, '(${navy})', 15) > 0
        AND    ROWNUM > 0) context_hits,
       (SELECT /*+ index (di your_other_index_name) */ *
               di.subscriber_id, di.name
        FROM   portal.wwv_docinfo di 
        WHERE  di.LANGUAGE = 'us'
        AND    di.thingid = 0
        AND    ROWNUM > 0) t2
WHERE  context_hits.subscriber_id = t2.subscriber_id
AND    context_hits.NAME = t2.NAME;


[Updated on: Wed, 25 February 2009 22:57]

Report message to a moderator

Previous Topic: blevel in bitmap index
Next Topic: cardinality in bitmap index
Goto Forum:
  


Current Time: Thu Dec 08 20:36:44 CST 2016

Total time taken to generate the page: 0.06814 seconds