Re: Speeding up my SQL statement

Hi Rene!
I thought about that, but would that help? If TSM still has to scan every
object for a match, it wouldn't help much... That's the problem, I don't
know how SQL works...
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines


-----Original Message-----
From: Lambelet,Rene,VEVEY,GLOBE Center CSC
[mailto:Rene.Lambelet AT NESTLE DOT COM]
Sent: Tuesday, June 29, 2004 13:21
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Speeding up my SQL statement


hi Eric, you could add 

node_name like '%ORC' to the where clause...

best regards,

                René LAMBELET
                NESTEC  SA
                GLOBE - Global Business Excellence
                Central Support Center
                SD/ESN
                Av. Nestlé 55  CH-1800 Vevey (Switzerland) 
                tél +41 (0)21 924'35'43   fax +41 (0)21 924'45'89   local
REL-5 01
                mailto:rene.lambelet AT nestle DOT com

                This message is intended only for the use of the addressee
                and may contain information that is privileged and
confidential.


-----Original Message-----
From: Loon, E.J. van - SPLXM [mailto:Eric-van.Loon AT KLM DOT COM]
Sent: Tuesday,29. June 2004 12:52
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Speeding up my SQL statement


Hi *SM-ers!
I'm using the following SQL statement to retrieve obsolete Oracle backup
files:

select node_name, filespace_name, ll_name, date(backup_date) from backups
where ((days(current_date) - days(backup_date) >= 100)) and hl_name='//'

This returns all Oracle backup files, created more than 100 days ago. These
should not exist anymore.
Since this statement scans ALL (millions!!) backup objects for a hit, it
runs for more than a day!
I'm looking for a way to reduce this, but I don't know how to do this.
If I would be able to limit the scan to only the objects belonging to Oracle
nodes (in our shop, the nodename ends with -ORC) it would finish much
quicker, but I don't know how.
Can anybody tell me if this is possible at all?
Thank you very much for any reply in advance!!!
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines


**********************************************************************
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain confidential
and privileged material intended for the addressee only. If you are not the
addressee, you are notified that no part of the e-mail or any attachment may
be disclosed, copied or distributed, and that any other action related to
this e-mail or attachment is strictly prohibited, and may be unlawful. If
you have received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. Koninklijke Luchtvaart
Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be
liable for the incorrect or incomplete transmission of this e-mail or any
attachments, nor responsible for any delay in receipt.
**********************************************************************