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:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 12:07 PM To: [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Regional Computing Center tel. ++49/711/685 5806 University of Stuttgart fax: ++49/711/682357 Allmandring 30a D 70550 Stuttgart Germany
