Skip to Main Content
  • Questions
  • 9i Oracle Text searching multiple tables

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Mike.

Asked: September 24, 2002 - 5:02 pm UTC

Answered by: Tom Kyte - Last updated: February 10, 2012 - 5:25 pm UTC

Category: Database - Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I currently have a simple search page that incorporates 9i Oracle Text by using the Context index on a CLOB column. What I'd like to know is if its possible to use a single query with CONTAINS that searches a CLOB column in Table A as well as multiple VARCHAR columns in Table B without having to modify my query to join the two tables, etc.

thanks,
Mike A.

and we said...

Well, thats what I do here on asktom.

I index the question + answer (table T1), and the reviews (table T2) and the followups to the reviews (T3).

I accomplish this by indexing a function, not the table itself (requires a rebuild of the index, not the app).


see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:440419921146 <code>
for an example.


and you rated our response

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Indexing multiple table columns

June 02, 2005 - 9:49 pm UTC

Reviewer: RD from NZ

Hi Tom,
In the link given above, the indexes are on multiple columns on the same tables.
Please can you give an example where one can index columns of multiple tables.
Thanks in advance,
RahulD.

Tom Kyte

Followup  

June 03, 2005 - 7:17 am UTC


ok, here is the code I use myself to do this...

@su ask_tom

grant select on WWC_ASK_INDEXED_QUESTIONS$ to ctxsys;
grant select on WWC_ASK_QUESTION_REVIEWS$ to ctxsys;
grant select on WWC_ASK_REVIEW_FOLLOWUPS$ to ctxsys;

@su ctxsys

create or replace procedure index_ask_tom( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select *
from ask_tom.WWC_ASK_INDEXED_QUESTIONS$
where rowid = p_id )
loop
dbms_lob.copy( p_lob, x.text, dbms_lob.getLength(x.text) );

for y in ( select REVIEW_TITLE || ' ' || REVIEWER_NAME || ' ' ||
REVIEWER_LOCATION || ' ' subj,
review_comments
from ask_tom.WWC_ASK_QUESTION_REVIEWS$
where displayid = x.displayid )
loop
dbms_lob.writeAppend( p_lob, length(y.subj), y.subj );
dbms_lob.append( p_lob, y.review_comments );
end loop;

for y in ( select followup_comments
from ask_tom.WWC_ASK_REVIEW_FOLLOWUPS$
where displayid = x.displayid )
loop
dbms_lob.writeAppend( p_lob, 1, ' ' );
dbms_lob.append( p_lob, y.followup_comments );
end loop;

end loop;
end;
/
grant execute on index_ask_tom to ask_tom
/

Note how that function gets the data from 3 separate tables, many rows -- formats a "document" based on it...


@su ask_tom

create trigger WWC_ASK_AIUD_QUESTION_REVIEWS
after insert or insert or delete on WWC_ASK_QUESTION_REVIEWS$
for each row
begin
-- just tickle the row for oracle text to know to re-index...
if (inserting or updating)
then
update WWC_ASK_INDEXED_QUESTIONS$
set dummy = dummy where displayid = :new.displayid;
else
update WWC_ASK_INDEXED_QUESTIONS$
set dummy = dummy where displayid = :old.displayid;
end if;
end;
/

create trigger WWC_ASK_AIUD_QUESTION_FUPS
after insert or insert or delete on WWC_ASK_REVIEW_FOLLOWUPS$
for each row
begin
-- just tickle the row for oracle text to know to re-index...
if (inserting or updating)
then
update WWC_ASK_INDEXED_QUESTIONS$
set dummy = dummy where displayid = :new.displayid;
else
update WWC_ASK_INDEXED_QUESTIONS$
set dummy = dummy where displayid = :old.displayid;
end if;
end;
/


we have a single index, so we must "tickle" the row to be re-indexed...

begin
ctx_ddl.create_preference('ask_tom_user_datastore',
'user_datastore' );

ctx_ddl.set_attribute( 'ask_tom_user_datastore', 'procedure',
'CTXSYS.INDEX_ASK_TOM' );
end;
/

begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'NO' );
end;
/

create index search_idx on WWC_ASK_INDEXED_QUESTIONS$(dummy)
indextype is ctxsys.context parameters( 'datastore ask_tom_user_datastore lexer my_lexer')
/


and there you go... A index that crosses columns in a table and rows in many tables



Question on ownership of indexing procedure

June 03, 2005 - 3:28 pm UTC

Reviewer: Dan Kefford from New Haven, CT

Tom...

In your example above of indexing multiple columns from multiple columns, is it necessary that the indexing procedure be created under ctxsys? Would it be better for say.. the schema owner of the tables in question itself to be the owner? I realize ctxsys is not sys, but it is a product account nonetheless, and so shouldn't application specific stuff be kept out of it, or is there simply no other way to accomplish this?



Tom Kyte

Followup  

June 03, 2005 - 5:29 pm UTC

yes it is.

ctxsys must own the procedure, the procedure ctxsys owns could just be a call to the "real" procedure.




Re: Question on ownership of indexing procedure

June 03, 2005 - 8:12 pm UTC

Reviewer: Dan Kefford from New Haven, CT

Thanks, Tom. And thanks for all your answers before; I feel like I owe you at least twenty of them.

Can't wait to see you at the next CTOUG meeting.


Re: 9i Oracle Text searching multiple tables

January 09, 2008 - 2:48 pm UTC

Reviewer: Edith Lee from USA

Hi Tom,

Will this 'search_idx' created on WWC_ASK_INDEXED_QUESTIONS$(dummy) be updated
by 'EXEC ctx_ddl.SYNC_INDEX('search_idx')'
if the values in any columns in the WWC_ASK_INDEXED_QUESTIONS$ is changed?
Thanks.
Tom Kyte

Followup  

January 10, 2008 - 2:14 pm UTC

the way I wrote this trigger:

create trigger WWC_ASK_AIUD_QUESTION_REVIEWS
after insert or insert or delete on WWC_ASK_QUESTION_REVIEWS$
for each row
begin
    -- just tickle the row for oracle text to know to re-index...
    if (inserting or updating)
    then
        update WWC_ASK_INDEXED_QUESTIONS$ 
           set dummy = dummy where displayid = :new.displayid;
    else
        update WWC_ASK_INDEXED_QUESTIONS$ 
           set dummy = dummy where displayid = :old.displayid;
    end if;
end;
/


yes, I update dummy regardless of the column being modified - so any column modification would cause that row to update dummy and that is what trips the index.

Re: 9i Oracle Text searching multiple tables

January 17, 2008 - 5:18 pm UTC

Reviewer: Edith Lee from USA

Hi Tom

I noticed that you use both dbms_lob.writeAppend and dbms_lob.append in the procedure index_ask_tom.
Are both of them needed here or just one of them will do the job also?

Thanks!
Tom Kyte

Followup  

January 19, 2008 - 10:30 pm UTC

given my code, both were needed. I had a string and a clob to append.

writeappend - varchar to clob
append - clob to clob

Lost infos about join - PKs are missing

January 23, 2008 - 10:13 am UTC

Reviewer: Jan Mutter from Munich, Germany

Hi Tom,

the solution works like a charm. However, after joining WWC_ASK_INDEXED_QUESTIONS and WWC_ASK_QUESTION_REVIEWS (in the procedure as in your example) the information about other columns in the search result are lost because the only return type is a CLOB.
For example when I do a search by "contains" on search_idx i'd like to know the primary key of WWC_ASK_QUESTION_REVIEWS which belongs to the result row. But that seems to be impossible if the relation is 1:n.

Thanks,
Jan.
Tom Kyte

Followup  

January 23, 2008 - 2:17 pm UTC

not sure what you are getting at.

I'm indexing a DOCUMENT (This page)

that the data was stored in a bunch of rows, in a bunch of tables - not relevant to me.

but, if it were to you - you would index a tagged XML representation of the page (eg: don't build a text document like I did, build XML and index that)

then, you can do lots of "other stuff" with it.

Re: 9i Oracle Text searching multiple tables

January 23, 2008 - 11:58 am UTC

Reviewer: Edith from USA

Hi Tom,

Thank very much for the example and explanation, I am making big progress on my project now. I do have one more question though - When I was trying to look up certain words such as 'CAN' using

SELECT * FROM WWC_ASK_INDEXED_QUESTIONS$
WHERE contains (dummy, 'CAN') > 0;

I found nothing, but after I changed the value to 'CAN1'
in the table, reindexed, and did

SELECT * FROM WWC_ASK_INDEXED_QUESTIONS$
WHERE contains (dummy, 'CAN1') > 0;

I found it. Is this a bug or someting I did wrong?

Thanks!
Tom Kyte

Followup  

January 23, 2008 - 2:21 pm UTC

stopwords, the, and, or, etc etc etc are filtered as noise. not indexed. not a bug, feature.

http://asktom.oracle.com/pls/ask/search?p_string=stopwords

Re: 9i Oracle Text searching multiple tables

January 23, 2008 - 3:08 pm UTC

Reviewer: Edith from USA

Tom,

Is there a script that I can use to create the 3 tables --
WWC_ASK_INDEXED_QUESTIONS$, WWC_ASK_QUESTION_REVIEWS$,
and WWC_ASK_REVIEW_FOLLOWUPS$ in the example? I created them by studying the code in the procedure, but I do not know how how each column are typed. Is it because
column "review_comments" and "followup_comments" are typed clob, they are "append" instead of "writeAppend" to "p_lob"?

Thanks.


Tom Kyte

Followup  

January 23, 2008 - 8:25 pm UTC

they are clobs, that is why I used either append or writeAppend - depending on the types.

My tables - not relevant to you.
The technique - relevant to you.

You would take YOUR data and using a routine YOU write, glue all of the stuff together into a document that text will index.

RE: 9i Oracle Text searching multiple tables

January 24, 2008 - 12:45 pm UTC

Reviewer: Edith from USA

Tom,

Now I understand why append and WriteAppend are both needed.

Thanks again for your help.

CTXCAT indexes

July 21, 2008 - 2:20 pm UTC

Reviewer: Sacha Mallais from Vancouver, BC Canada

Hi Tom, I've tried this with a CTXCAT indextype instead of CONTEXT, but it doesn't work. Is there a way to get it working with CTXCAT index?

I wonder if its even necessary, since the thing I need from CTXCAT indexes is that they are automatically updated... does your procedure above automatically update the index when the values change?
Tom Kyte

Followup  

July 22, 2008 - 11:05 am UTC

context indexes can be "automatically updated" as well - for many releases now.


Question on ownership of indexing procedure

June 02, 2009 - 5:46 am UTC

Reviewer: Henrik from Aalborg, Denmark

ON June 3, 2005 - 5pm US/Eastern, you write
ctxsys must own the procedure, the procedure ctxsys owns could just be a call to the "real" procedure.

Is this still the case?

In Oracle Text Reference, 10g Release 2 (10.2), B14218-01 (pdf version) it says:
On page 2-12, Table 2-6 USER_DATASTORE Attributes
Attribute: procedure
Specify the procedure that synthesizes the document to be indexed.
This procedure can be owned by any user and must be executable by the index owner.


But I can't get it to work:



This works:

Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

create table t (c char(1));
insert into t values (' ');
commit;

select ERR_TEXT from ctx_user_index_errors;
no rows selected


connect ctxsys/...

create procedure t_data(rid in rowid, doc in out NOCOPY clob) is
begin
dbms_lob.trim(lob_loc => doc, newlen => 0);
dbms_lob.writeAppend(lob_loc => doc, amount => 11, buffer => 'ABC DEF GHI');
end t_data;
/

connect <user>/...
Connected.

begin
ctx_ddl.create_preference('uds', 'user_datastore');
ctx_ddl.set_attribute('uds', 'procedure', 'ctxsys.t_data');
ctx_ddl.set_attribute('uds', 'output_type', 'CLOB');
end;
/

create index t_CTXIdx
on t(c)
indextype is CTXSYS.Context
parameters ('datastore uds');

Index created.

select ERR_TEXT from ctx_user_index_errors;
no rows selected
select count(*) from dr$t_ctxidx$k;
COUNT(*)
----------
1
1 row selected.
select count(*) from dr$t_ctxidx$i;
COUNT(*)
----------
3
1 row selected.




But as I read the documentation, I shold be able to create an Oracle Text index wihtout connecting to ctxsys, like this:


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

create table t (c char(1));
insert into t values (' ');
commit;

create procedure t_data(rid in rowid, doc in out NOCOPY clob) is
begin
dbms_lob.trim(lob_loc => doc, newlen => 0);
dbms_lob.writeAppend(lob_loc => doc, amount => 11, buffer => 'ABC DEF GHI');
end t_data;
/

begin
ctx_ddl.create_preference('uds', 'user_datastore');
ctx_ddl.set_attribute('uds', 'procedure', 't_data');
ctx_ddl.set_attribute('uds', 'output_type', 'CLOB');
end;
/

create index t_CTXIdx
on t(c)
indextype is CTXSYS.Context
parameters ('datastore uds');

Index created.

select ERR_TEXT from ctx_user_index_errors;
ERR_TEXT
------------------------------------------------------------
DRG-12604: execution of user datastore procedure has failed
DRG-50857: oracle error in drsinopen
ORA-06550: line 1, column 12:
PLS-00302: component 'T_DATA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Have also tried "grant execute on t_data to public;"
and "create public synonym t_data for <user>.t_data;"
Neither made any difference.

What am I missing?


Tom Kyte

Followup  

June 02, 2009 - 7:52 am UTC

ops$tkyte%ORA10GR2> create table t (c char(1));

Table created.

ops$tkyte%ORA10GR2> insert into t values (' ');

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure t_data(rid in rowid, doc in out NOCOPY clob) is
  2  begin
  3    dbms_lob.trim(lob_loc => doc, newlen => 0);
  4    dbms_lob.writeAppend(lob_loc => doc, amount => 11, buffer => 'ABC DEF GHI');
  5  end t_data;
  6  /

Procedure created.
<b>
ops$tkyte%ORA10GR2> GRANT EXECUTE ON T_DATA TO CTXSYS;
</b>
Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2    ctx_ddl.create_preference('uds', 'user_datastore');
  3    ctx_ddl.set_attribute('uds', 'procedure', '<b>"OPS$TKYTE"."T_DATA"</b>');
  4    ctx_ddl.set_attribute('uds', 'output_type', 'CLOB');
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_CTXIdx
  2    on t(c)
  3    indextype is CTXSYS.Context
  4    parameters ('datastore uds');

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ERR_TEXT from ctx_user_index_errors;

no rows selected



avoid public synonyms, pretend they do not exist


use a fully qualified reference
grant execute on the procedure to precisely the only user that needs it

works for me in current releases - give it a go.

Re: Question on ownership of indexing procedure

June 04, 2009 - 8:49 am UTC

Reviewer: Henrik from Aalborg, Denmark

I get the same result, tried two databases. (10.2.0.1 and 10.2.0.3)

I expect (hope) to install a new test server in a few weeks, will try latest 10g version on that.
Tom Kyte

Followup  

June 08, 2009 - 11:23 am UTC

show me a cut and paste of your attempt please, a cut and paste exactly like mine.


use user_data_store to concat multiple columns from different tables to xml

January 03, 2010 - 11:35 am UTC

Reviewer: liat

Hi Tom,
i was using the example you gave to create user_data_store which would create xml (on 11g).
in my case i have over 10 tables containing text and reference the master table in a one to many relationship (possible that they have no referencing row to the main table)
also possible that many of the columns are null.
i narrowed it down to 3 tables only for demonstrating:
--create tables
create table people(pseq number primary key,id varchar2(250), name varchar2(250),description varchar2(4000));
create table people_add (pseq number, address varchar2(4000),phone varchar2(250));
create table people_mail (pseq number, email varchar2(250),email_desc varchar2(4000));
ALTER TABLE TEXT.PEOPLE_ADD ADD (FOREIGN KEY (PSEQ) REFERENCES TEXT.PEOPLE (PSEQ) ON DELETE CASCADE);
ALTER TABLE TEXT.PEOPLE_MAIL ADD (FOREIGN KEY (PSEQ) REFERENCES TEXT.PEOPLE (PSEQ) ON DELETE CASCADE);
--populate tables
insert into people values (1,'111111','lulu','no information');
insert into people values (2,'222222','dan','bank account');
insert into people values (3,'333333','dana','');
insert into people values (4,'444444','kuku','information');
insert into people values (5,'111111','lala','basketball');
insert into people values (6,'111111','','missing name');
insert into people values (7,'111111','nono','something something');
insert into people_add values (1,'street','012-phone');
insert into people_add values (1,'second street','');
insert into people_add values (2,'first','');
insert into people_add values (3,'5th','1357');
insert into people_add values (5,'3rd','111');
insert into people_mail values (1,'mymail@somemail.com','office email');
insert into people_mail values (4,'mymail2@somemail.com','e-mail');
insert into people_mail values (5,'','at home');
commit;
-- create procedure
create or replace procedure people_full_search  (rid  in rowid,p_clob in out NOCOPY   clob)
is
begin
  FOR r1 IN (SELECT pseq,nvl(id,' ') id,nvl(name,' ') name,nvl(description,' ') description FROM people WHERE ROWID = rid) LOOP
      DBMS_LOB.WRITEAPPEND (p_clob, 4, '<id>');
      DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.id), r1.id);
      DBMS_LOB.WRITEAPPEND (p_clob, 6, '<name>');
         DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.name), r1.name);
         DBMS_LOB.WRITEAPPEND (p_clob, 7, '</name>');
         DBMS_LOB.WRITEAPPEND (p_clob, 13, '<description>');
         DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.description), r1.description);
         DBMS_LOB.WRITEAPPEND (p_clob, 14, '</description>');
      FOR r2 IN (SELECT nvl(address,' ') address, nvl(phone,' ') phone FROM people_add WHERE pseq = r1.pseq) LOOP
               DBMS_LOB.WRITEAPPEND (p_clob, 9, '<address>');
               DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.address), r2.address);
               DBMS_LOB.WRITEAPPEND (p_clob, 10, '</address>');
               DBMS_LOB.WRITEAPPEND (p_clob, 7, '<phone>');
               DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.phone), r2.phone);
               DBMS_LOB.WRITEAPPEND (p_clob, 8, '</phone>');
         END LOOP;
         FOR r3 IN (SELECT nvl(email,' ') email, nvl(email_desc,' ') email_desc FROM people_mail WHERE pseq = r1.pseq) LOOP
               DBMS_LOB.WRITEAPPEND (p_clob, 7, '<email>');
               DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.email), r3.email);
               DBMS_LOB.WRITEAPPEND (p_clob, 8, '</email>');
               DBMS_LOB.WRITEAPPEND (p_clob, 12, '<email_desc>');
               DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.email_desc), r3.email_desc);
               DBMS_LOB.WRITEAPPEND (p_clob, 13, '</email_desc>');
         END LOOP;
         DBMS_LOB.WRITEAPPEND (p_clob, 5, '</id>');
  END LOOP;
end people_full_search;
/

BEGIN
    CTX_DDL.CREATE_PREFERENCE ('people_datastore', 'USER_DATASTORE');
     CTX_DDL.SET_ATTRIBUTE ('people_datastore', 'PROCEDURE', 'people_full_search');
     CTX_DDL.SET_ATTRIBUTE ('people_datastore', 'OUTPUT_TYPE', 'CLOB');

   END;
   /
CREATE INDEX people_search ON people (search)
    INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS
      ('DATASTORE people_datastore
      SECTION GROUP CTXSYS.AUTO_SECTION_GROUP');

it seem that when i want to get data from across 3 tables i face a Cartesian join.
adding a condition will filter rows that don't have related data some of the tables, and outer join will take hours and fail on temp resources.
am i down to the second option of index each table separately?
thanks,
Liat
Tom Kyte

Followup  

January 04, 2010 - 12:07 pm UTC

... it seem that when i want to get data from across 3 tables i face a Cartesian join. ...


where? You have a hierachy:

people 1:M people-add(ress)
1:M
people-mail


You would simply search:

select * from people where contains( search, :x ) > 0;

that would give you people with the searched for data somewhere in the people record, the address record(s) or the mail record(s).

If you want the entire "hierarchy" of data, it might be something like:
 

select p.*, 
       cursor( select * from people_add a where a.pseq = p.pseq ),
      cursor( select * from people_mail b where b.pseq = p.pseq )
  from people p
 where contains( ... ) > 0;



but it would be entirely up to you how to retrieve this set of data, how to display it.

I have the same 'issue', my function indexes a Q&A table with two clobs - and a child table followups (1:M with Q&A) and reviews (1:M with Q&A). I query it in a similar fashion to what I described here.

use user_data_store to concat multiple columns from different tables to xml

January 04, 2010 - 6:03 pm UTC

Reviewer: A reader

many thanks tom,
i actually used a function to return the data, but i'll give the developers your option as well.
i'm still looking for a way to return the name of the column
(or tag in the document i concatenated) that contained the text i searched for. it was a feature in the previous indexing engine which is being replaced by oracle text
also will the solution you demonstrated for multiple table search is applicable in a case my table sores only the path to the documents (not the clob) + few other varchar columns, and the related tables holds only varchars, and we need to search across all 3?

Tom Kyte

Followup  

January 05, 2010 - 9:40 am UTC

... also will the solution you demonstrated for multiple table search is applicable
in a case my table sores only the path to the documents (not the clob) + few
other varchar columns, and the related tables holds only varchars, and we need
to search across all 3?
....


you write the function, if your function you write
a) has access to the documents
b) reads the documents
c) incorporates the document text into the return value

then sure, it'll work. If the document is passed back to text somehow, it'll index it.


you can use contains with "within" to see if the hit was within a specific item

ops$tkyte%ORA10GR2> select id,
  2         contains( search, 'office within email' ) email,
  3         contains( search, 'office within description') descript ,
  4         contains( search, 'office within email_desc') email_desc
  5    from people
  6   where contains( search, 'office' ) > 0;

ID              EMAIL   DESCRIPT EMAIL_DESC
---------- ---------- ---------- ----------
111111              0          0          6



or you could use hilighting if you like to show the term in "context"

http://docs.oracle.com/docs/cd/B19306_01/text.102/b14217/view.htm#sthref591

January 20, 2010 - 4:34 am UTC

Reviewer: Sushil from UK

Hello Tom,

Can i create a concatenated datastore on a single column.For e.g. a clob column contains serachable data for direct_datastore and embedded pdf link as file_datastore.

Now can i create a concat index in true sense mixing both the data types so my serach query retrives everthing from data stored in column as well data in embedded pdf?

many to many relation ship

May 30, 2010 - 8:32 am UTC

Reviewer: vasu from India

i want perfect create query for many to many relation ship between two tables in oracle?write which is best query?
Tom Kyte

Followup  

May 30, 2010 - 8:49 am UTC

dumbfounded by this one.

there is just about one way to write a perfect query.

That would be to have

o the schema, including tables, indexes, constraints - so you can understand the schema

o most importantly - the QUESTION you are trying to answer.


Take the typical "student" "professor" relationship. A student has many professors, a professor has many students. Classic many to many relationship, there will be an intersection table students_to_professors or something like that with at a minimum the primary keys of student and professor in it (and maybe more!)

So now, write the perfect query against that - you cannot, it is a trick question because there is NO QUESTION yet. Here is the perfect query against those three tables (performance wise anyway):

<this space left intentionally blank>


Yes, that is the perfect query, you will find none faster. It answers the question:

<I have no question, thanks very much>


Now, if you ask a question such as "given a student id, I would like to know how many distinct professors they have" or "find all professors who have students X and Y both as students" or "whatever", then and only then can one start talking about the way to approach constructing that query.

Below link is broken

April 18, 2011 - 9:33 pm UTC

Reviewer: learner from Boston,MA

Hi,

Below link is broken. Please update!

http://asktom.oracle.com/pls/ask/search?p_string=stopwords


getting error following your example

February 10, 2012 - 4:07 am UTC

Reviewer: Joel from Singapore

Hi Tom,

I had the error that say it can't find the procedure. I thought the problem is my script, so I tried following your example but I am still getting the error.

I am on Oracle10gR2, my username is REQUEST_TOOL.
Here is the output.

REQUEST_TOOL@DEV > create table t (c char(1));

Table created.

REQUEST_TOOL@DEV > insert into t values (' ');

1 row created.

REQUEST_TOOL@DEV > commit;

Commit complete.

REQUEST_TOOL@DEV >
REQUEST_TOOL@DEV > create or replace procedure t_data(rid in rowid, doc in out NOCOPY clob) is
  begin
    dbms_lob.trim(lob_loc => doc, newlen => 0);
    dbms_lob.writeAppend(lob_loc => doc, amount => 11, buffer => 'ABC DEF GHI');
  end t_data;
 /  2    3    4    5    6

Procedure created.

REQUEST_TOOL@DEV > GRANT EXECUTE ON T_DATA TO CTXSYS;

Grant succeeded.

REQUEST_TOOL@DEV > begin
    ctx_ddl.create_preference('uds', 'user_datastore');
    ctx_ddl.set_attribute('uds', 'procedure', '"REQUEST_TOOL"."T_DATA"');
    ctx_ddl.set_attribute('uds', 'output_type', 'CLOB');
  end;
  /
  2    3    4    5    6
PL/SQL procedure successfully completed.

REQUEST_TOOL@DEV >
REQUEST_TOOL@DEV > create index t_CTXIdx
    on t(c)
    indextype is CTXSYS.Context
    parameters ('datastore uds');  2    3    4

Index created.

REQUEST_TOOL@DEV > select ERR_TEXT from ctx_user_index_errors;

ERR_TEXT
--------------------------------------------------------------------------------
DRG-12604: execution of user datastore procedure has failed
DRG-50857: oracle error in drsinopen
ORA-06550: line 1, column 20:
PLS-00302: component 'T_DATA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Please kindly advise.

Tom Kyte

Followup  

February 10, 2012 - 5:25 pm UTC

I cannot reproduce, here is a COMPLETE example:

ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

ops$tkyte%ORA10GR2> drop user request_tool cascade;

User dropped.

ops$tkyte%ORA10GR2> create user request_tool identified by x default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant create session, create table, create procedure to request_tool;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant execute on ctxsys.ctx_ddl to request_tool;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect request_tool/x
Connected.
request_tool%ORA10GR2> set echo on
request_tool%ORA10GR2>
request_tool%ORA10GR2> create table t (c char(1));

Table created.

request_tool%ORA10GR2> insert into t values (' ');

1 row created.

request_tool%ORA10GR2> commit;

Commit complete.

request_tool%ORA10GR2> create or replace procedure t_data(rid in rowid, doc in out
  2  NOCOPY clob) is
  3    begin
  4      dbms_lob.trim(lob_loc => doc, newlen => 0);
  5      dbms_lob.writeAppend(lob_loc => doc, amount => 11, buffer => 'ABC DEF GHI');
  6    end t_data;
  7  /

Procedure created.

request_tool%ORA10GR2> GRANT EXECUTE ON T_DATA TO CTXSYS;

Grant succeeded.

request_tool%ORA10GR2> begin
  2      ctx_ddl.create_preference('uds', 'user_datastore');
  3      ctx_ddl.set_attribute('uds', 'procedure', '"REQUEST_TOOL"."T_DATA"');
  4      ctx_ddl.set_attribute('uds', 'output_type', 'CLOB');
  5    end;
  6  /

PL/SQL procedure successfully completed.

request_tool%ORA10GR2> create index t_CTXIdx
  2      on t(c)
  3      indextype is CTXSYS.Context
  4      parameters ('datastore uds');

Index created.

request_tool%ORA10GR2>
request_tool%ORA10GR2> select ERR_TEXT from ctx_user_index_errors;

no rows selected

request_tool%ORA10GR2>



Found the issue

February 13, 2012 - 12:47 am UTC

Reviewer: Joel from Singapore

Hi Tom,

I finally found the issue. REQUEST_TOOL user has a table called REQUEST_TOOL as well. Once i renamed that table, the script works.

I wonder if there's an alternative workaround beside changing the table name. That table is referenced in many stored procedure. It's a nightmare if i have to modify all the stored procedure.

any suggestion?

Thanks.

Found the solution in metalink

February 13, 2012 - 12:50 am UTC

Reviewer: Joel from Singapore

Tom,

Ignore my previous message. I have the solution in metalink.
Thanks for responding to my first question.

Thanks.