Home » SQL & PL/SQL » SQL & PL/SQL » searching across two tables
searching across two tables [message #279066] Tue, 06 November 2007 16:08 Go to next message
jimbo021956
Messages: 5
Registered: October 2007
Junior Member
I am experiencing slow response times when querying the
following two tables for call numbers. The user will input
a call number to search which may exist in either the
catalog's callnum column OR the holding's xcallno column.

There are about 220,000 rows in the catalog table and
about 500,000 rows in the holdings table.

create table catalog
(accn Number(Cool NOT NULL,
ab clob,
mti varchar2(1000) NOT NULL,
notes varchar2(4000),
oclcn varchar2(15) NOT NULL,
publ varchar2(500),
pubplace varchar2(500),
callnum varchar2(1000),
stockno varchar2(500),
subj varchar2(1000),
subjad varchar2(1000),
yr number(4),
CONSTRAINT PK_catalog PRIMARY KEY(oclcn),
CONSTRAINT UN_accn UNIQUE(accn),
CONSTRAINT DB_name CHECK (DB IN ('OCLC','NTIS','EOAL','EMAL')))


create table holdings
(oclcn varchar2(20) NOT NULL,
lb char(4) NOT NULL,
subcoll varchar2(Cool,
xcallno varchar2(250) NOT NULL,
open varchar2(200),
dtmod Date,
CONSTRAINT FK_holdings FOREIGN KEY(oclcn)
REFERENCES catalog(oclcn),
CONSTRAINT UN_holdings UNIQUE(oclcn,lb,subcoll,callno,open))


The following query works but the response time is very slow:

select mti,catalog.oclcn,yr from catalog,holdings where catalog.oclcn = holdings.oclcn AND
(catalog.callnum LIKE 'TD193C672005' OR holdings.xcallno LIKE 'TD193C672005')


This next query does the same as above and reduces response time by half but still is slow:

select mti,oclcn,accn,yr from catalog where oclcn IN
((select oclcn from catalog where callnum LIKE 'TD193C672005')
UNION
(select oclcn from holdings where xcallno LIKE 'TD193C672005%'))


Does anyone have any suggestion for improving response time?

Thanks in advance.

Jim
Re: searching across two tables [message #279069 is a reply to message #279066] Tue, 06 November 2007 16:33 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>(catalog.callnum LIKE 'TD193C672005' OR holdings.xcallno LIKE 'TD193C672005')
The use of LIKE precludes use of index.
The use of LIKE without any wildcard character make little sense.
(catalog.callnum = 'TD193C672005' OR holdings.xcallno = 'TD193C672005')
Above might run faster & won't run any slower

[Updated on: Tue, 06 November 2007 16:33] by Moderator

Report message to a moderator

Re: searching across two tables [message #279081 is a reply to message #279066] Tue, 06 November 2007 19:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Well, let me say a few things. Normally I avoid issues of protocol but I feel obliged to offer posting tips before moving on to a possible solution...

1) there is a code formatter on the front page of OraFaq, you should use it.

2) the {...} up above is for highlighting code, you should use that too.

here is what you get when you do use both

SELECT mti,
       Catalog.oclcn,
       yr
FROM   Catalog,
       Holdings
WHERE  Catalog.oclcn = Holdings.oclcn
       AND (     Catalog.Callnum LIKE 'TD193C672005'
             OR Holdings.xCallNo LIKE 'TD193C672005')


SELECT mti,
       oclcn,
       accn,
       yr
FROM   Catalog
WHERE  oclcn IN ((SELECT oclcn
                  FROM   Catalog
                  WHERE  Callnum LIKE 'TD193C672005')
                 UNION 
                 (SELECT oclcn
                  FROM   Holdings
                  WHERE  xCallNo LIKE 'TD193C672005%'))


3) your lack of table prefixes on your columns in query#1 is super annoying, this is one of my pet peeves. You should always prefix every column in a query with more than one table.

Now for some help...

a quick and dirty trick would be to create indexes specifically for your query with the idea of skipping table accesses.

query#2 might use these

catalog(callnum,oclcn)
catalog(oclcn,yr,accn,mti)
holdings(xcallno,ocln)


as was pointed out, it looks like you missed a % sign in one of your like expressions. Also, Like will us an index if sufficiently restrictive. I would expect your query#2 to be real fast given the above indexes, possibly both queries. Don't forget to collect statistics.

If you understand what I am suggesting, then you will know the correct query execution plan by its lack of references to the tables directly. Only the indexes will be probed for data. Thus you will skip much work.

good luck, Kevin
Re: searching across two tables [message #283628 is a reply to message #279081] Tue, 27 November 2007 14:47 Go to previous messageGo to next message
jimbo021956
Messages: 5
Registered: October 2007
Junior Member
I tried your suggestions below and initially the search worked very well with excellent response times. After the weekend, I tried the searches and again they were very slow. So I reloaded the database and again the response time was excellent. After another weekend they back to running slow again. I tried another weekend with the same results. I've run this problem around our office and no one seems to have any ideas.

I did run the 'Explain plan' command and did indeed get significantly different results after the initial load and then after coming back from the weekend. I did screen capture of both instances but the file attachment was too big to send.

This is indeed puzzling. Please let me know if you have any ideas or need more information.

THanks
Re: searching across two tables [message #283629 is a reply to message #279066] Tue, 27 November 2007 14:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
some thoughts

1) maybe it is a statistics issues.
2) maybe it is a fragmentation issue (doubt it), but, do you do lots of data changes during the week?
3) maybe there is a batch process monkeying with your data?

hard to say off the top.

try collecting stats after the weekend rather than reloading. This could determine if it is a stats collection issue.

run a response time indicator script each morning, noon, night to see if you can find our when it goes south. That may clue you to what is happening.

Kevin
Re: searching across two tables [message #283708 is a reply to message #283629] Wed, 28 November 2007 00:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If the Explain Plan is changing significantly over the weekend:
- Your stats are being wiped out from dropping/truncating the tables/indexes
- Your DBA has an automated process running on the weekend that overwrites your stats with a sub-optimal gathering
- Some process on the weeken MASSIVELY changes the distribution of data in the tables.

But I reckon its one of the first two.

Ross Leishman
Re: searching across two tables [message #284677 is a reply to message #283629] Fri, 30 November 2007 09:06 Go to previous message
jimbo021956
Messages: 5
Registered: October 2007
Junior Member
We've now found whats causing the problem. Whenever ORACLE is stopped and restarted this searching problem surfaces. But it only effects searches across tables. The searches on the individual tables continue to work fine. The CATALOG table has several text indexes and they continue to work fine.

Obviously we will periodically have to start and stop ORACLE.
Have you ever heard of this problem and if so is there a fix.
My only idea at this time is to write the search data from the child HOLDINGS table into dummy fields on the parent CATALOG table and then create text indexes from these fields which would allow me to limit my searches to one table. This will work but I really don't want to do that.

Any ideas?

Thanks for your help
Previous Topic: invalid relational operator - Error
Next Topic: Tree Structure - Concat String on Group By
Goto Forum:
  


Current Time: Fri Dec 02 12:20:16 CST 2016

Total time taken to generate the page: 0.12547 seconds