Home » Server Options » Text & interMedia » Source of the hit on a concatenated datastore? (Oracle 10g)
| Source of the hit on a concatenated datastore? [message #310230] |
Mon, 31 March 2008 11:21  |
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
|
Junior Member |
|
|
Hi, i'm having trouble with a scenario where i have a concatenated datastore(reference http://forums.oracle.com/forums/thread.jspa?threadID=635914&tstart=0), this is what i've tried, with few problems, i've to use a union any way?
What i've is this, a table buyer_tbl that is basically a table where a buyer is registered, now this table have a child table with a few alias names of the buyer buyer_branch_tbl. and a equal scenario this one as suppliers supplier_tbl->supplier_branch_tbl all this trying to create a concat index with field "name" of the 4 tables.
Now the only thing could be equal on the 2 groups is the field NIT that's a simple identifier key this could be the same on the tables buyer_tbl, supplier_tbl, now i'm working on a text search that output this:
Nit Name Source of Record
===========================
1234 abc Supplier
1235 bcasdf Buyer
The real problem is the field Source or Record= this is the group where record was found, now either on buyers or supplier.
Code:
SCOTT@orcl_11g> CREATE TABLE buyer_tbl
2 (buyer_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
9 CONSTRAINT buyer_id_pk PRIMARY KEY (buyer_id))
10 /
SCOTT@orcl_11g> CREATE TABLE buyer_branch_tbl
2 (buyer_id NUMBER, branch_id NUMBER,
3 name VARCHAR2 (5),
9 CONSTRAINT buyer_branch_id_pk PRIMARY KEY (buyer_id, branch_id))
10 /
SCOTT@orcl_11g> CREATE TABLE supplier_tbl
2 (supplier_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
9 CONSTRAINT supplier_id_pk PRIMARY KEY (supplier_id))
10 /
SCOTT@orcl_11g> CREATE TABLE supplier_branch_tbl
2 (supplier_id NUMBER, branch_id NUMBER,
3 name VARCHAR2 (5),
9 CONSTRAINT supplier_branch_id_pk PRIMARY KEY (supplier_id, branch_id))
10 /
SCOTT@orcl_11g> ALTER TABLE buyer_brach_tbl ADD (
2 CONSTRAINT FK_buyer_tbl
3 FOREIGN KEY (buyer_id)
4 REFERENCES buyer_tbl (buyer_id)); /
SCOTT@orcl_11g> ALTER TABLE supplier_brach_tbl ADD (
2 CONSTRAINT FK_supplier_tbl
3 FOREIGN KEY (supplier_id)
4 REFERENCES supplier_tbl (supplier_id)); /
[Updated on: Mon, 31 March 2008 11:34] Report message to a moderator |
|
|
|
| Re: Source of the hit on a concatenated datastore? [message #310264 is a reply to message #310230] |
Mon, 31 March 2008 14:16  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is the same as I posted in response to your thread on the OTN text forums, demonstrating first just the usage of a standard query on the tables, followed by a materialized view and a query on that, followed by a context index on the materialized view and a text query on the materialized view. What method you want to use depends on what types of searches you want to do.
SCOTT@orcl_11g> -- tables and constraints:
SCOTT@orcl_11g> CREATE TABLE buyer_tbl
2 (buyer_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
5 CONSTRAINT buyer_id_pk PRIMARY KEY (buyer_id))
6 /
Table created.
SCOTT@orcl_11g> CREATE TABLE supplier_tbl
2 (supplier_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
5 CONSTRAINT supplier_id_pk PRIMARY KEY (supplier_id))
6 /
Table created.
SCOTT@orcl_11g> CREATE TABLE buyer_branch_tbl
2 (buyer_id NUMBER,
3 branch_id NUMBER,
4 name VARCHAR2 (5),
5 CONSTRAINT buyer_branch_id_pk PRIMARY KEY (buyer_id, branch_id),
6 CONSTRAINT FK_buyer_tbl FOREIGN KEY (buyer_id) REFERENCES buyer_tbl (buyer_id))
7 /
Table created.
SCOTT@orcl_11g> CREATE TABLE supplier_branch_tbl
2 (supplier_id NUMBER,
3 branch_id NUMBER,
4 name VARCHAR2 (5),
5 CONSTRAINT supplier_branch_id_pk PRIMARY KEY (supplier_id, branch_id),
6 CONSTRAINT FK_supplier_tbl FOREIGN KEY (supplier_id) REFERENCES supplier_tbl (supplier_id))
7 /
Table created.
SCOTT@orcl_11g> -- insert test data:
SCOTT@orcl_11g> BEGIN
2 INSERT INTO buyer_tbl VALUES (1, 'xyz', 1234);
3 INSERT INTO supplier_tbl VALUES (100, 'abca', 1234);
4 INSERT INTO buyer_branch_tbl VALUES (1, 10, 'xyza');
5 INSERT INTO supplier_branch_tbl VALUES (100, 10, 'abcaa');
6 --
7 INSERT INTO buyer_tbl VALUES (2, 'abcb', 1235);
8 INSERT INTO supplier_tbl VALUES (200, 'zyx', 1235);
9 INSERT INTO buyer_branch_tbl VALUES (2, 20, 'abcbb');
10 INSERT INTO supplier_branch_tbl VALUES (200, 20, 'zyxa');
11 END;
12 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> -- search query without text index:
SCOTT@orcl_11g> VARIABLE search_string VARCHAR2(30)
SCOTT@orcl_11g> EXEC :search_string := 'abc'
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT *
2 FROM (SELECT nit "Nit", name nombre, 'Buyer' source
3 FROM buyer_tbl
4 UNION ALL
5 SELECT bt.nit "Nit", bbt.name nombre, 'Buyer' source
6 FROM buyer_tbl bt, buyer_branch_tbl bbt
7 WHERE bt.buyer_id = bbt.buyer_id
8 UNION ALL
9 SELECT nit "Nit", name nombre, 'Supplier' source
10 FROM supplier_tbl
11 UNION ALL
12 SELECT st.nit "Nit", sbt.name nombre, 'Supplier' source
13 FROM supplier_tbl st, supplier_branch_tbl sbt
14 WHERE st.supplier_id = sbt.supplier_id
15 ORDER BY 1, 2, 3)
16 WHERE INSTR (nombre, :search_string) > 0
17 /
Nit NOMBR SOURCE
----- ----- --------
1234 abca Supplier
1234 abcaa Supplier
1235 abcb Buyer
1235 abcbb Buyer
SCOTT@orcl_11g> -- materialized view:
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW buyer_supplier_names AS
2 SELECT nit "Nit", name nombre, 'Buyer' source
3 FROM buyer_tbl
4 UNION ALL
5 SELECT bt.nit "Nit", bbt.name nombre, 'Buyer' source
6 FROM buyer_tbl bt, buyer_branch_tbl bbt
7 WHERE bt.buyer_id = bbt.buyer_id
8 UNION ALL
9 SELECT nit "Nit", name nombre, 'Supplier' source
10 FROM supplier_tbl
11 UNION ALL
12 SELECT st.nit "Nit", sbt.name nombre, 'Supplier' source
13 FROM supplier_tbl st, supplier_branch_tbl sbt
14 WHERE st.supplier_id = sbt.supplier_id
15 /
Materialized view created.
SCOTT@orcl_11g> -- search of materialized view:
SCOTT@orcl_11g> SELECT * FROM buyer_supplier_names WHERE INSTR (nombre, :search_string) > 0 ORDER BY 1, 2, 3
2 /
Nit NOMBR SOURCE
----- ----- --------
1234 abca Supplier
1234 abcaa Supplier
1235 abcb Buyer
1235 abcbb Buyer
SCOTT@orcl_11g> -- text index on materialized view:;
SCOTT@orcl_11g> CREATE INDEX buyer_supplier_names_idx ON buyer_supplier_names (nombre)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 /
Index created.
SCOTT@orcl_11g> -- text query on materialized view using text index:
SCOTT@orcl_11g> EXEC :search_string := 'abc%'
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT * FROM buyer_supplier_names WHERE CONTAINS (nombre, :search_string) > 0 ORDER BY 1, 2, 3
2 /
Nit NOMBR SOURCE
----- ----- --------
1234 abca Supplier
1234 abcaa Supplier
1235 abcb Buyer
1235 abcbb Buyer
SCOTT@orcl_11g>
|
|
|
|
| Re: Source of the hit on a concatenated datastore? [message #314478 is a reply to message #310264] |
Wed, 16 April 2008 19:12 |
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
|
Junior Member |
|
|
In this scenario the problem that im having is with the refresh strategy, with FAST ON COMMIT it fails, is about the union, i think.
CREATE MATERIALIZED VIEW LOG ON buyer_tbl
WITH ROWID INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON supplier_tbl WITH ROWID INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON buyer_branch_tbl WITH ROWID INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON supplier_branch_tbl WITH ROWID INCLUDING NEW VALUES;
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW buyer_supplier_names REFRESH FAST ON COMMIT WITH ROWID AS
2 SELECT nit "Nit", name nombre, 'Buyer' source
3 FROM buyer_tbl
4 UNION ALL
5 SELECT bt.nit "Nit", bbt.name nombre, 'Buyer' source
6 FROM buyer_tbl bt, buyer_branch_tbl bbt
7 WHERE bt.buyer_id = bbt.buyer_id
8 UNION ALL
9 SELECT nit "Nit", name nombre, 'Supplier' source
10 FROM supplier_tbl
11 UNION ALL
12 SELECT st.nit "Nit", sbt.name nombre, 'Supplier' source
13 FROM supplier_tbl st, supplier_branch_tbl sbt
14 WHERE st.supplier_id = sbt.supplier_id
15 /
I searched along but it seems to get always this error:
ORA-12054 cannot set the ON COMMIT refresh attribute for the materialized view
Previous to this i created on demand, but the refresh COMPLETE where very expensive(long) because a table on this has almost 4 millon rows...
|
|
|
|
| Re: Source of the hit on a concatenated datastore? [message #314509 is a reply to message #314478] |
Thu, 17 April 2008 00:20 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to add the rowid's and the union all marker columns. Please see the demonstration below.
SCOTT@orcl_11g> -- tables and constraints:
SCOTT@orcl_11g> CREATE TABLE buyer_tbl
2 (buyer_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
5 CONSTRAINT buyer_id_pk PRIMARY KEY (buyer_id))
6 /
Table created.
SCOTT@orcl_11g> CREATE TABLE supplier_tbl
2 (supplier_id NUMBER,
3 name VARCHAR2 (5),
4 nit VARCHAR2 (5),
5 CONSTRAINT supplier_id_pk PRIMARY KEY (supplier_id))
6 /
Table created.
SCOTT@orcl_11g> CREATE TABLE buyer_branch_tbl
2 (buyer_id NUMBER,
3 branch_id NUMBER,
4 name VARCHAR2 (5),
5 CONSTRAINT buyer_branch_id_pk PRIMARY KEY (buyer_id, branch_id),
6 CONSTRAINT FK_buyer_tbl FOREIGN KEY (buyer_id) REFERENCES buyer_tbl (buyer_id))
7 /
Table created.
SCOTT@orcl_11g> CREATE TABLE supplier_branch_tbl
2 (supplier_id NUMBER,
3 branch_id NUMBER,
4 name VARCHAR2 (5),
5 CONSTRAINT supplier_branch_id_pk PRIMARY KEY (supplier_id, branch_id),
6 CONSTRAINT FK_supplier_tbl FOREIGN KEY (supplier_id) REFERENCES supplier_tbl (supplier_id))
7 /
Table created.
SCOTT@orcl_11g> -- materialized view:
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON buyer_tbl WITH ROWID INCLUDING NEW VALUES
2 /
Materialized view log created.
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON supplier_tbl WITH ROWID INCLUDING NEW VALUES
2 /
Materialized view log created.
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON buyer_branch_tbl WITH ROWID INCLUDING NEW VALUES
2 /
Materialized view log created.
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON supplier_branch_tbl WITH ROWID INCLUDING NEW VALUES
2 /
Materialized view log created.
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW buyer_supplier_names
2 REFRESH FAST ON COMMIT WITH ROWID
3 AS
4 SELECT bt.ROWID rid, NULL rid2, nit "Nit", name nombre, 'Buyer' source, 1 marker
5 FROM buyer_tbl bt
6 UNION ALL
7 SELECT bt.ROWID rid, bbt.ROWID rid2, bt.nit "Nit", bbt.name nombre, 'Buyer' source, 2 marker
8 FROM buyer_tbl bt, buyer_branch_tbl bbt
9 WHERE bt.buyer_id = bbt.buyer_id
10 UNION ALL
11 SELECT st.ROWID rid, NULL rid2, nit "Nit", name nombre, 'Supplier' source, 3 marker
12 FROM supplier_tbl st
13 UNION ALL
14 SELECT st.ROWID rid, sbt.ROWID rid2, st.nit "Nit", sbt.name nombre, 'Supplier' source, 4 marker
15 FROM supplier_tbl st, supplier_branch_tbl sbt
16 WHERE st.supplier_id = sbt.supplier_id
17 /
Materialized view created.
SCOTT@orcl_11g> -- text index on materialized view:;
SCOTT@orcl_11g> CREATE INDEX buyer_supplier_names_idx ON buyer_supplier_names (nombre)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('SYNC (ON COMMIT)')
4 /
Index created.
SCOTT@orcl_11g> -- insert test data:
SCOTT@orcl_11g> BEGIN
2 INSERT INTO buyer_tbl VALUES (1, 'xyz', 1234);
3 INSERT INTO supplier_tbl VALUES (100, 'abca', 1234);
4 INSERT INTO buyer_branch_tbl VALUES (1, 10, 'xyza');
5 INSERT INTO supplier_branch_tbl VALUES (100, 10, 'abcaa');
6 --
7 INSERT INTO buyer_tbl VALUES (2, 'abcb', 1235);
8 INSERT INTO supplier_tbl VALUES (200, 'zyx', 1235);
9 INSERT INTO buyer_branch_tbl VALUES (2, 20, 'abcbb');
10 INSERT INTO supplier_branch_tbl VALUES (200, 20, 'zyxa');
11 END;
12 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> COMMIT
2 /
Commit complete.
SCOTT@orcl_11g> SELECT * FROM buyer_supplier_names
2 /
RID RID2 Nit NOMBR SOURCE MARKER
------------------ ------------------ ----- ----- -------- ----------
AAAbw2AAEAAA4BgAAA AAAbw6AAEAAA7PwAAA 1234 abcaa Supplier 4
AAAbw2AAEAAA4BgAAB AAAbw6AAEAAA7PwAAB 1235 zyxa Supplier 4
AAAbw2AAEAAA4BgAAA 1234 abca Supplier 3
AAAbw2AAEAAA4BgAAB 1235 zyx Supplier 3
AAAbw0AAEAAA4AQAAA AAAbw4AAEAAA5+YAAA 1234 xyza Buyer 2
AAAbw0AAEAAA4AQAAB AAAbw4AAEAAA5+YAAB 1235 abcbb Buyer 2
AAAbw0AAEAAA4AQAAA 1234 xyz Buyer 1
AAAbw0AAEAAA4AQAAB 1235 abcb Buyer 1
8 rows selected.
SCOTT@orcl_11g> -- search of materialized view:
SCOTT@orcl_11g> VARIABLE search_string VARCHAR2(30)
SCOTT@orcl_11g> EXEC :search_string := 'abc'
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT * FROM buyer_supplier_names WHERE INSTR (nombre, :search_string) > 0
2 /
RID RID2 Nit NOMBR SOURCE MARKER
------------------ ------------------ ----- ----- -------- ----------
AAAbw2AAEAAA4BgAAA AAAbw6AAEAAA7PwAAA 1234 abcaa Supplier 4
AAAbw2AAEAAA4BgAAA 1234 abca Supplier 3
AAAbw0AAEAAA4AQAAB AAAbw4AAEAAA5+YAAB 1235 abcbb Buyer 2
AAAbw0AAEAAA4AQAAB 1235 abcb Buyer 1
SCOTT@orcl_11g> -- text query on materialized view using text index:
SCOTT@orcl_11g> EXEC :search_string := 'abc%'
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT * FROM buyer_supplier_names WHERE CONTAINS (nombre, :search_string) > 0 ORDER BY 1, 2, 3
2 /
RID RID2 Nit NOMBR SOURCE MARKER
------------------ ------------------ ----- ----- -------- ----------
AAAbw0AAEAAA4AQAAB AAAbw4AAEAAA5+YAAB 1235 abcbb Buyer 2
AAAbw0AAEAAA4AQAAB 1235 abcb Buyer 1
AAAbw2AAEAAA4BgAAA AAAbw6AAEAAA7PwAAA 1234 abcaa Supplier 4
AAAbw2AAEAAA4BgAAA 1234 abca Supplier 3
SCOTT@orcl_11g>
[Updated on: Thu, 17 April 2008 00:25] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Jan 30 18:58:58 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
