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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 9077
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

Previous Topic: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
Next Topic: Text search
Goto Forum:
  


Current Time: Thu Mar 28 06:18:52 CDT 2024