The problem is it's a join and joins are expensive if the tables are big or
the fields are not indexed.  The issue is the filespace_name is not indexed
in the occupancy table.  Therefore, it just reads the table from beginning
to end everytime trying to match each row in the filespace table.

Sorry, I do not have a better answer.

Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: Gerhard Rentschler [mailto:g.rentschler AT RUS.UNI-STUTTGART DOT DE]
Sent: Wednesday, September 04, 2002 12:07 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Select Question


I would like to have a list of filespaces not backed up for more than a year
together with information from occupancy table (num_files, physical_mb).
With select statements I was not lucky. As a starter I tried the following
statement which doesn't check for the time of the ast backup:

select f.node_name, f.filespace_name, o.type, o.stgpool_name, num_files,
physical_mb from filespaces f, occupancy o where f.node_name=o.nodename and
f.filespace_name=o.filespace_name

After more than 1 hour with no output at all I cancelled the command. The
following select works much faster: select f.node_name, filespace_name,
contact from filespaces f, nodes n where f.node_name=n.node_name

What is the reason for the first example to run such as slowly?

Regards
Gerhard
---
Gerhard Rentschler            email:g.rentschler AT rus.uni-stuttgart DOT de
Regional Computing Center     tel.   ++49/711/685 5806
University of Stuttgart       fax:   ++49/711/682357
Allmandring 30a
D 70550
Stuttgart
Germany