Home » RDBMS Server » Performance Tuning » Joining Oracle Text results with multiple tables / Performance Problems (Oracle 9i, Win Server 2003)
 Joining Oracle Text results with multiple tables / Performance Problems [message #309472] |
Thu, 27 March 2008 11:04  |
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  |
 |
Barbara Boehmer
Messages: 9100
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 |
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 #388656 is a reply to message #310553] |
Wed, 25 February 2009 20:54 |
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 #388682 is a reply to message #388656] |
Wed, 25 February 2009 22:55 |
|
Barbara Boehmer
Messages: 9100
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 |
|
|
|
Goto Forum:
Current Time: Fri Nov 22 18:32:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
