On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
> > SELECT
> > sc.open_dt,
> > sc.close_dt,
> > sc.protocol,
> > INET_NTOA( sc.src_address ) AS src_address,
> > sc.src_port,
> > INET_NTOA( sc.dst_address ) AS dst_address,
> > sc.dst_port,
> > sc.sent,
> > sc.rcvd,
> > spm.desc AS src_port_desc,
> > dpm.desc AS dst_port_desc
> > FROM firewall_connections AS sc
> If this is a firewall connection log I presume open_dt is the time a
> connection was opened and is always going to be less than close_dt.
> > WHERE
> > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30
> > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <=
> In that case your logic here simplifies to:
> open_dt >= '2011-08-30 00:00:00'
> close_dt <= '2011-08-30 12:36:53'
> Now add an index over open_dt and close_dt and see what happens.
I can't really use your WHERE logic because I also need to retrieve
results where the open_dt time is out of the range specified. For
example, a very large file download might span multiple days so given
your logic if the connection was started 2 days ago and I want to pull 1
days worth of connections, I would miss that entry. Basically I want to
SELECT all of the records that were opened OR closed during the
specified time period, ie. if any activity happened between my start and
end dates, I need to see that record.
Any other ideas?