Home » RDBMS Server » Performance Tuning » Oracle Text index column when searching multiple tables (12.1.0.2.0)
|
|
| Re: Oracle Text index column when searching multiple tables [message #669563 is a reply to message #669527] |
Mon, 30 April 2018 01:42   |
 |
Barbara Boehmer
Messages: 9104
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 #670177 is a reply to message #670169] |
Wed, 13 June 2018 23:06 |
|
Barbara Boehmer
Messages: 9104
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 |
 |
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
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 |
------------------------------------------------------------------------
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!
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 06 07:40:55 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
