Home » RDBMS Server » Performance Tuning » Oracle Text index column when searching multiple tables (12.1.0.2.0)
icon5.gif  Oracle Text index column when searching multiple tables [message #669527] Fri, 27 April 2018 04:49 Go to next message
LFranz
Messages: 4
Registered: April 2018
Junior Member
Hi,

we're trying to use oracle text to search on multiple columns from different tables.
To test this functionality, I followed the example at orafaq.com/forum/mv/msg/201983/655836/#msg_num_2 (i'm not allowed to post links yet), which worked great, but doesn't seem to fully apply to our data structure.

-- Simplified tables and data. We're looking to expand the search to more tables in the future, ideally being able to search through all our data.
Table streets:
STREET_ID    STREET_NAME
------------ --------------
           1 Sesame Street 1
           2 Sunset Boulevard 5
           3 Hollywood Plaza 3

Table address:
CUSTOMER_ID    ADDRESS_ID    NAME           IS_PERSON    Street_ID
-------------- ------------ --------------- ------------ ------------
          1000       111111 John Smith Ltd.            0            1
          2000       222222 James                      1            2
          1000       333333 Mary                       1            3  

Table phone_numbers
CUSTOMER_ID    ADDRESS_ID_OWNER    RELATION     ADDRESS_ID_USER     PHONENUMBER
-------------- ------------------- -----------  ------------------ --------------
          1000              111111           0              111111         123456 -- Phone number of John Smith Ltd. (e. g. reception of the business)
          1000              111111           1              333333         123457 -- Phone number of Mary as contact for John Smith Ltd. (Relation 1 to ADDRESS_ID 111111)
          1000              333333           0              333333         555555 -- Phone number of Mary (private)

The stored procedure should produce something to the following extend:
<ADDRESS><ADDRESS_ID>111111</ADDRESS_ID><NAME>John Smith Ltd.</NAME><STREET>Sesame Street 1</STREET></ADDRESS><PHONE_NUMBERS><PHONENUMBER>123456</PHONENUMBER><PHONENUMBER>123457</PHONENUMBER></PHONE_NUMBERS>

<ADDRESS><ADDRESS_ID>222222</ADDRESS_ID><NAME>James</NAME><STREET>Sunset Boulevard 5</STREET></ADDRESS><PHONE_NUMBERS><PHONENUMBER></PHONENUMBER></PHONE_NUMBERS>

<ADDRESS><ADDRESS_ID>333333</ADDRESS_ID><NAME>Mary</NAME><STREET>Hollywood Plaza 3</STREET></ADDRESS><PHONE_NUMBERS><PHONENNUMBER>555555</PHONENUMBER></PHONE_NUMBERS>
So e. g. searching for 123456 or 123457 would both return the first line with John Smith Ltd.

My problem is that I don't know where and how to create the index. The index should be updated when any of the values change. For example the sync should occur when I change the street name in the streets table or the name in the address table or the phone number in the phone_numbers table, etc.

How is this possible with oracle text?
Thank you very much in advance!
Re: Oracle Text index column when searching multiple tables [message #669563 is a reply to message #669527] Mon, 30 April 2018 01:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see the demonstration and comments below. You should also periodically either drop and recreate your index or alter and rebuild your index or optimize your index to eliminate index fragmentation caused by index synchronization that will gradually slow your searches.

-- version:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

-- tables:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE address
  2    (customer_id  NUMBER,
  3  	address_id   NUMBER,
  4  	name	     VARCHAR2(15),
  5  	is_person    NUMBER,
  6  	street_id    NUMBER)
  7  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE streets
  2    (street_id    NUMBER,
  3  	street_name  VARCHAR2(18))
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE phone_numbers
  2    (customer_id	 NUMBER,
  3  	address_id_owner NUMBER,
  4  	relation	 NUMBER,
  5  	address_id_user  NUMBER,
  6  	phonenumber	 NUMBER)
  7  /

Table created.

-- procedure:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE test_proc
  2    (p_rid  IN	     ROWID,
  3  	p_clob IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    FOR i IN
  7  	 (SELECT a.customer_id,
  8  		 a.address_id,
  9  		 '<ADDRESS><ADDRESS_ID>' || a.address_id  ||
 10  		 '</ADDRESS_ID><NAME>'	 || a.name	  ||
 11  		 '</NAME><STREET>'	 || s.street_name ||
 12  		 '</STREET></ADDRESS>' address
 13  	  FROM	 address a, streets s
 14  	  WHERE  a.ROWID = p_rid
 15  	  AND	 a.street_id = s.street_id)
 16    LOOP
 17  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (i.address), i.address);
 18  	 DBMS_LOB.WRITEAPPEND (p_clob, 15, '<PHONE_NUMBERS>');
 19  	 FOR j IN
 20  	   (SELECT '<PHONENUMBER>' || p.phonenumber || '</PHONENUMBER>' phones
 21  	    FROM   phone_numbers p
 22  	    WHERE  p.customer_id = i.customer_id
 23  	    AND    p.address_id_owner = i.address_id)
 24  	 LOOP
 25  	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (j.phones), j.phones);
 26  	 END LOOP;
 27  	 DBMS_LOB.WRITEAPPEND (p_clob, 16, '</PHONE_NUMBERS>');
 28    END LOOP;
 29  END test_proc;
 30  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- user datastore and auto section group:
SCOTT@orcl_12.1.0.2.0> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_ds', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_ds', 'PROCEDURE', 'test_proc');
  4    CTX_DDL.CREATE_SECTION_GROUP ('test_sg', 'AUTO_SECTION_GROUP');
  5  END;
  6  /

PL/SQL procedure successfully completed.

-- index that synchronizes when the indexed column (name) is updated:
-- (You could create the index on the name column in the address table or add a dummy column to that table.
-- Whichever you use is what you need to use in your searches and your triggers. I have used the name column below.)
SCOTT@orcl_12.1.0.2.0> CREATE INDEX test_idx ON address (name)
  2    INDEXTYPE IS CTXSYS.CONTEXT
  3    PARAMETERS
  4  	 ('DATASTORE	  test_ds
  5  	   SECTION GROUP  test_sg
  6  	   SYNC 	  (ON COMMIT)')
  7  /

Index created.

-- trigger to cause index synchronization when streets.street_name is updated:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TRIGGER streets_trig
  2    AFTER INSERT OR UPDATE OR DELETE OF street_name ON streets
  3    FOR EACH ROW
  4  BEGIN
  5    IF INSERTING OR UPDATING THEN
  6  	 UPDATE address
  7  	 SET	name = name
  8  	 WHERE	street_id = :NEW.street_id;
  9    ELSIF DELETING THEN
 10  	 UPDATE address
 11  	 SET	name = name
 12  	 WHERE	street_id = :OLD.street_id;
 13    END IF;
 14  END streets_trig;
 15  /

Trigger created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- trigger to cause index synchronization when phone_numbers.phonenumber is updated:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TRIGGER phone_numbers_trig
  2    AFTER INSERT OR UPDATE OR DELETE OF phonenumber ON phone_numbers
  3    FOR EACH ROW
  4  BEGIN
  5    IF INSERTING OR UPDATING THEN
  6  	 UPDATE address
  7  	 SET	name = name
  8  	 WHERE	customer_id = :NEW.customer_id;
  9    ELSIF DELETING THEN
 10  	 UPDATE address
 11  	 SET	name = name
 12  	 WHERE	customer_id = :OLD.customer_id;
 13    END IF;
 14  END phone_numbers_trig;
 15  /

Trigger created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- inserts of data and commits, resulting in index synchronization:
SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO address VALUES (1000, 111111, 'John Smith Ltd.', 0, 1)
  3  INTO address VALUES (2000, 222222, 'James',	   1, 2)
  4  INTO address VALUES (1000, 333333, 'Mary', 	   1, 3)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO streets VALUES (1, 'Sesame Street 1')
  3  INTO streets VALUES (2, 'Sunset Boulevard 5')
  4  INTO streets VALUES (3, 'Hollywood Plaza 3')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO phone_numbers VALUES (1000, 111111, 0, 111111, 123456)
  3  INTO phone_numbers VALUES (1000, 111111, 1, 333333, 123457)
  4  INTO phone_numbers VALUES (1000, 333333, 0, 333333, 555555)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

-- wrapper function for usage in data retrieval:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_func
  2    (p_rowid IN ROWID)
  3    RETURN	   XMLTYPE
  4  AS
  5    v_clob	   CLOB;
  6    v_xmltype   XMLTYPE;
  7  BEGIN
  8    DBMS_LOB.CREATETEMPORARY (v_clob, FALSE);
  9    test_proc (p_rowid, v_clob);
 10    v_xmltype := XMLTYPE ('<DATA>' || v_clob || '</DATA>');
 11    DBMS_LOB.FREETEMPORARY (v_clob);
 12    RETURN v_xmltype;
 13  END test_func;
 14  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- what the procedure produces and function returns:
SCOTT@orcl_12.1.0.2.0> SELECT test_func (ROWID) FROM address ORDER BY address_id
  2  /

TEST_FUNC(ROWID)
--------------------------------------------------------------------------------
<DATA>
  <ADDRESS>
    <ADDRESS_ID>111111</ADDRESS_ID>
    <NAME>John Smith Ltd.</NAME>
    <STREET>Sesame Street 1</STREET>
  </ADDRESS>
  <PHONE_NUMBERS>
    <PHONENUMBER>123456</PHONENUMBER>
    <PHONENUMBER>123457</PHONENUMBER>
  </PHONE_NUMBERS>
</DATA>

<DATA>
  <ADDRESS>
    <ADDRESS_ID>222222</ADDRESS_ID>
    <NAME>James</NAME>
    <STREET>Sunset Boulevard 5</STREET>
  </ADDRESS>
  <PHONE_NUMBERS/>
</DATA>

<DATA>
  <ADDRESS>
    <ADDRESS_ID>333333</ADDRESS_ID>
    <NAME>Mary</NAME>
    <STREET>Hollywood Plaza 3</STREET>
  </ADDRESS>
  <PHONE_NUMBERS>
    <PHONENUMBER>555555</PHONENUMBER>
  </PHONE_NUMBERS>
</DATA>


3 rows selected.

-- searches:
SCOTT@orcl_12.1.0.2.0> SELECT test_func (ROWID) FROM address WHERE CONTAINS (name, '123456 WITHIN phonenumber') > 0
  2  /

TEST_FUNC(ROWID)
--------------------------------------------------------------------------------
<DATA>
  <ADDRESS>
    <ADDRESS_ID>111111</ADDRESS_ID>
    <NAME>John Smith Ltd.</NAME>
    <STREET>Sesame Street 1</STREET>
  </ADDRESS>
  <PHONE_NUMBERS>
    <PHONENUMBER>123456</PHONENUMBER>
    <PHONENUMBER>123457</PHONENUMBER>
  </PHONE_NUMBERS>
</DATA>


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT test_func (ROWID) FROM address WHERE CONTAINS (name, '123457 WITHIN phonenumber') > 0
  2  /

TEST_FUNC(ROWID)
--------------------------------------------------------------------------------
<DATA>
  <ADDRESS>
    <ADDRESS_ID>111111</ADDRESS_ID>
    <NAME>John Smith Ltd.</NAME>
    <STREET>Sesame Street 1</STREET>
  </ADDRESS>
  <PHONE_NUMBERS>
    <PHONENUMBER>123456</PHONENUMBER>
    <PHONENUMBER>123457</PHONENUMBER>
  </PHONE_NUMBERS>
</DATA>


1 row selected.
Re: Oracle Text index column when searching multiple tables [message #670169 is a reply to message #669563] Wed, 13 June 2018 03:29 Go to previous messageGo to next message
LFranz
Messages: 4
Registered: April 2018
Junior Member
Hi Barbara,

please excuse the delayed reply, some projects had to get done asap. Thank you very much for your detailed example, I've got it running on a subset of our tables and it worked so far.

I've encountered one huge problem that I wasn't aware of in the beginning. The text index is created on a column in one of the data tables or in a seperate table that references the data tables. The text index is subsequently only updated when a value in the column is changed. Meaning that we would have to create triggers on all of the other tables that would change the value of the corresponding row in the other table. That is not only really impractical, it would also mean quite a bit of overhead performance wise (especially when updating millions of rows) let alone having to add all those triggers.

Another way would be to regularly (e.g. 15 minutes) run an index update via scheduled job. That is out of the question for us though, as we need real time like searching.

As far as I know this isn't possible via oracle text (when adding the above contraints) or am I wrong?

If not, do you know any good alternatives for searching through oracle data in real time as described in my original posting?

Thank you very much!

Best regards
Lukas
Re: Oracle Text index column when searching multiple tables [message #670177 is a reply to message #670169] Wed, 13 June 2018 23:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is another method without triggers.

-- version:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

-- tables:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE address
  2    (customer_id  NUMBER,
  3  	address_id   NUMBER,
  4  	name	     VARCHAR2(15),
  5  	is_person    NUMBER,
  6  	street_id    NUMBER)
  7  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE streets
  2    (street_id    NUMBER,
  3  	street_name  VARCHAR2(18))
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE phone_numbers
  2    (customer_id	 NUMBER,
  3  	address_id_owner NUMBER,
  4  	relation	 NUMBER,
  5  	address_id_user  NUMBER,
  6  	phonenumber	 NUMBER)
  7  /

Table created.

-- materialized view logs and materialized view:
SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW LOG ON address WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW LOG ON streets WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW LOG ON phone_numbers WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW test_mview
  2    REFRESH FAST ON COMMIT
  3  AS
  4  SELECT a.ROWID arowid, s.ROWID srowid, p.ROWID prowid,
  5  	    a.customer_id a_customer_id, a.address_id, a.name, a.is_person, a.street_id a_street_id,
  6  	    s.*, p.*
  7  FROM   address a, streets s, phone_numbers p
  8  WHERE  a.street_id = s.street_id
  9  AND    a.customer_id = p.customer_id
 10  AND    p.address_id_owner = a.address_id
 11  /

Materialized view created.

-- multi_column_datastore, auto_section_group, and context index:
SCOTT@orcl_12.1.0.2.0> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_ds', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_ds', 'COLUMNS', 'name, street_name, phonenumber');
  4    CTX_DDL.CREATE_SECTION_GROUP ('test_sg', 'AUTO_SECTION_GROUP');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX test_idx ON test_mview (name) INDEXTYPE IS CTXSYS.CONTEXT
  2  PARAMETERS
  3    ('DATASTORE	test_ds
  4  	 SECTION GROUP	test_sg
  5  	 SYNC		(ON COMMIT)')
  6  /

Index created.

-- inserts of data and commits, resulting in fast refresh of materialized view and index synchronization:
SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO address VALUES (1000, 111111, 'John Smith Ltd.', 0, 1)
  3  INTO address VALUES (2000, 222222, 'James',	   1, 2)
  4  INTO address VALUES (1000, 333333, 'Mary', 	   1, 3)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO streets VALUES (1, 'Sesame Street 1')
  3  INTO streets VALUES (2, 'Sunset Boulevard 5')
  4  INTO streets VALUES (3, 'Hollywood Plaza 3')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO phone_numbers VALUES (1000, 111111, 0, 111111, 123456)
  3  INTO phone_numbers VALUES (1000, 111111, 1, 333333, 123457)
  4  INTO phone_numbers VALUES (1000, 333333, 0, 333333, 555555)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

-- searches:
SCOTT@orcl_12.1.0.2.0> SELECT address_id, name, street_name, phonenumber
  2  FROM   test_mview WHERE CONTAINS (name, '123456 WITHIN phonenumber') > 0
  3  /

ADDRESS_ID NAME            STREET_NAME        PHONENUMBER
---------- --------------- ------------------ -----------
    111111 John Smith Ltd. Sesame Street 1         123456

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT address_id, name, street_name, phonenumber
  2  FROM   test_mview WHERE CONTAINS (name, '123457 WITHIN phonenumber') > 0
  3  /

ADDRESS_ID NAME            STREET_NAME        PHONENUMBER
---------- --------------- ------------------ -----------
    111111 John Smith Ltd. Sesame Street 1         123457

1 row selected.
Re: Oracle Text index column when searching multiple tables [message #670185 is a reply to message #670177] Fri, 15 June 2018 05:03 Go to previous messageGo to next message
LFranz
Messages: 4
Registered: April 2018
Junior Member
Thank you for your reply and the new approach. I got it working with our data, but I'm now running into performance problems when updating data on tables which are used in the materialized view.

-- Created mv logs on used tables
CREATE MATERIALIZED VIEW LOG ON addresses WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON phone_numbers WITH ROWID;

-- Created mv (I minimized the below statement for clarity in terms of table names and selected fields)
> CREATE MATERIALIZED VIEW MV_ORA_MASTER_INDEX
REFRESH FAST ON COMMIT
AS
SELECT 
      addr.ROWID
    , phone_numbers.ROWID
    , addr.CUSTOMER_ID
    , addr.ADDRESS_ID
    , ...
    , phone_numbers.RELATION
    , phone_numbers.ADDRESS_ID_USER
    , ...
  FROM 
    addresses addr,
    phone_numbers phones
  WHERE 
    addr.CUSTOMER_ID = phone_numbers.CUSTOMER_ID
AND addr.CUSTOMER_ID IN (1000,2000)
AND (
      (addr.ADDRESS_ID = phone_numbers.ADDRESS_ID_OWNER AND phone_numbers.ADDRESS_ID_OWNER = phone_numbers.ADDRESS_ID_USER AND phone_numbers.RELATION = 0)
    OR 
      ((addr.ADDRESS_ID = phone_numbers.ADDRESS_ID_OWNER OR addr.ADDRESS_ID = phone_numbers.ADDRESS_ID_USER) AND phone_numbers.RELATION <> 0)
    );

The resulting view has ~125 000 records. At this point it takes ~1.7 seconds to commit an update of one record in the addresses table, compared to ~0.02 seconds without the materialized view:
UPDATE addresses Set name = 'John' WHERE CUSTOMER_ID = 2000 AND ADDRESS_ID = 222222;
commit;

Task completed in 1.724 seconds

-- Creating multi_column_datastore, auto_section_group and context index:
begin
  CTX_DDL.CREATE_PREFERENCE (
      preference_name => 'OT_DATASTORE',
      object_name => 'MULTI_COLUMN_DATASTORE'
    );
  CTX_DDL.SET_ATTRIBUTE (
      preference_name => 'OT_DATASTORE',
      attribute_name => 'COLUMNS',
      attribute_value => 'CUSTOMER_ID, ADDRESS_ID, ...'
    );

  CTX_DDL.CREATE_SECTION_GROUP (
      group_name => 'OT_SECTION_GROUP',
      group_type => 'AUTO_SECTION_GROUP'
    );
end;

CREATE INDEX ORACLE_TEXT_IDX ON MV_ORA_MASTER_INDEX (name)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS
  ('DATASTORE OT_DATASTORE SECTION GROUP OT_SECTION_GROUP SYNC (ON COMMIT)');

After adding the text index the commit duration only increases very slightly (about ~0.05 to ~0.1 seconds), which is fine.

Do you have any tips on how to improve the performance? The materialized view and the index are updated directly after the commit (which is what we want), but it's blocking the execution until both are updated instead of runnign asynchronously in the background after the commit was successfully run. Is there maybe a way to configure this?

I really like the approach, but these commit times are currently not sustainable.

[Updated on: Fri, 15 June 2018 05:40]

Report message to a moderator

Re: Oracle Text index column when searching multiple tables [message #670199 is a reply to message #670185] Fri, 15 June 2018 21:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since this has now become more about materialized view performance than Oracle Text, I am moving it to the performance turning sub-forum, where hopefully it will get the attention of people with expertise in that area.

I see that you have a rather complex predicate:

AND (
      (addr.ADDRESS_ID = phone_numbers.ADDRESS_ID_OWNER AND phone_numbers.ADDRESS_ID_OWNER = phone_numbers.ADDRESS_ID_USER AND phone_numbers.RELATION = 0)
    OR 
      ((addr.ADDRESS_ID = phone_numbers.ADDRESS_ID_OWNER OR addr.ADDRESS_ID = phone_numbers.ADDRESS_ID_USER) AND phone_numbers.RELATION <> 0)
    )

You might try testing without that and comparing times and see if there is a simpler way to re-write that part.

Hopefully, you have standard (non-text) indexes on the columns that are used in joining the tables in the materialized view and indexes on the columns used in filter conditions in the predicates. It may help if you post what indexes you have and an autotrace or explained plan for the query used in the materialized view creation. It may help determine where the slowest part is and what can be done to improve it.



[Updated on: Fri, 15 June 2018 21:04]

Report message to a moderator

Re: Oracle Text index column when searching multiple tables [message #670302 is a reply to message #670199] Mon, 25 June 2018 08:27 Go to previous messageGo to next message
LFranz
Messages: 4
Registered: April 2018
Junior Member
Thank you for moving the topic.
Both tables used in the query are indexed. For testing purposes I tried setting the hidden "_mv_refresh_use_stats" flag to true, which increased the performance to about ~0.6 seconds for committing an update of one record in the addresses table. 600 ms for one commit is still way too long. It may be fine for an end-user-application, but it goes way beyond the scope of batch processing.

Following you'll find the explain plan for the select used in the mv and the used indices.
EXPLAINED SQL STATEMENT:
------------------------
SELECT addr.ROWID AS ADDRROWID , phones.ROWID AS PHONESROWID , addr.CUSTOMER_ID, addr.ADDRESS_ID, ..., phones.RELATION, phones.ADDRESS_ID_USER, ...
FROM addresses addr, phone_numbers phones WHERE addr.CUSTOMER_ID = phones.CUSTOMER_ID AND addr.ADDRESS_ID = addr.ADDRESS_ID
AND addr.CUSTOMER_ID IN (7841,7700) AND ((addr.ADDRESS_ID = phones.ADDRESS_ID_OWNER AND phones.ADDRESS_ID_OWNER = phones.ADDRESS_ID_USER AND phones.RELATION = 0)
OR ((addr.ADDRESS_ID = phones.ADDRESS_ID_OWNER OR addr.ADDRESS_ID = phones.ADDRESS_ID_USER) AND phones.RELATION <> 0))
 
Plan hash value: 2359877992
 
------------------------------------------------------------------------
| Id  | Operation                                      | Name          |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |               |
|   1 |  CONCATENATION                                 |               |
|   2 |   NESTED LOOPS                                 |               |
|   3 |    NESTED LOOPS                                |               |
|   4 |     INLIST ITERATOR                            |               |
|   5 |      PARTITION RANGE ITERATOR                  |               |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PHONE_NUMBERS |
|   7 |        INDEX RANGE SCAN                        | PHN_NMBRS_IDX |
|   8 |     INDEX UNIQUE SCAN                          | ADDRESSES_PK  |
|   9 |    TABLE ACCESS BY GLOBAL INDEX ROWID          | ADDRESSES     |
|  10 |   NESTED LOOPS                                 |               |
|  11 |    NESTED LOOPS                                |               |
|  12 |     INLIST ITERATOR                            |               |
|  13 |      PARTITION RANGE ITERATOR                  |               |
|  14 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PHONE_NUMBERS |
|  15 |        INDEX RANGE SCAN                        | PHN_NMBRS_IDX |
|  16 |     INDEX UNIQUE SCAN                          | ADDRESSES_PK  |
|  17 |    TABLE ACCESS BY GLOBAL INDEX ROWID          | ADDRESSES     |
|  18 |   NESTED LOOPS                                 |               |
|  19 |    NESTED LOOPS                                |               |
|  20 |     INLIST ITERATOR                            |               |
|  21 |      PARTITION RANGE ITERATOR                  |               |
|  22 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PHONE_NUMBERS |
|  23 |        INDEX RANGE SCAN                        | PHN_NMBRS_IDX |
|  24 |     INDEX UNIQUE SCAN                          | ADDRESSES_PK  |
|  25 |    TABLE ACCESS BY GLOBAL INDEX ROWID          | ADDRESSES     |
------------------------------------------------------------------------
-- The indices:
PHN_NBMRS_IDX:
----------------------------------------------------------------------------------------
| "COLUMN_NAME"      | "COLUMN_POSITION" | "COLUMN_LENGTH" | "CHAR_LENGTH" | "DESCEND" |
----------------------------------------------------------------------------------------
| "CUSTOMER_ID"      | "1"               | "22"            | "0"           | "ASC"     |
| "ADDRESS_ID_OWNER" | "2"               | "22"            | "0"           | "ASC"     |
| "RELATION"         | "3"               | "22"            | "0"           | "ASC"     |
| "ADDRESS_ID_USER"  | "4"               | "22"            | "0"           | "ASC"     |
----------------------------------------------------------------------------------------

ADDRESSES_PK:
-----------------------------------------------------------------------------------
| "COLUMN_NAME" | "COLUMN_POSITION" | "COLUMN_LENGTH" | "CHAR_LENGTH" | "DESCEND" |
-----------------------------------------------------------------------------------
| "CUSTOMER_ID" | "1"               | "22"            | "0"           | "ASC"     |
| "ADDRESS_ID"  | "2"               | "22"            | "0"           | "ASC"     |
-----------------------------------------------------------------------------------

I tried testing with a minimalized predicate, which led to 8 million entries in the MV and a commit time of ~5-6 seconds.

Considering we'd like to search through even more tables and data (adding to the already problematic join), I don't think that we're going to have any success this way. Barring any alternatives built into oracle, are there any third-party components you'd recommend? I've read a bit about reading transaction logs and writing them to disc / tables and indexing the results. Maybe a combined effort of something that gathers the data and oracle text to index it all would be the way to go. Although I'm a little wary of the required disk space in that scenario.

Any ideas would be greatly appreciated. Thank you very much for your efforts so far!
Re: Oracle Text index column when searching multiple tables [message #670331 is a reply to message #670302] Thu, 28 June 2018 03:12 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you upgrade to Oracle 12c release 2, then you can use "ON QUERY COMPUTATION" to create a "real-time materialized view". People are saying this is lightening fast. I don't have release 2 to test with, so I don't know how it will react with Oracle Text. Here are some links for you:

Scroll down to "{ ENABLE | DISABLE } ON QUERY COMPUTATION" in the link below:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-MATERIALIZED-VIEW.html#GUID-EE262CA4-01E5-4618-B659-6165 D993CA1B

https://medium.com/oracledevs/lightning-fast-sql-with-real-time-materialized-views-12-things-developers-will-love-about-oracle-54bcc9 eac358

https://uhesse.com/2017/01/05/real-time-materialized-views-in-oracle-12c/

[Updated on: Thu, 28 June 2018 03:19]

Report message to a moderator

Previous Topic: Encountering issues with join condition in stored procedure
Next Topic: please help for sql query to find out total number of concurrent connections in database
Goto Forum:
  


Current Time: Thu Mar 28 08:36:46 CDT 2024