Re: Union

From: Randolf Geist <mahrah_at_web.de>
Date: Sat, 24 Oct 2009 14:04:09 -0700 (PDT)
Message-ID: <4566fd04-eec0-4de2-a4cb-f2871f6eed75_at_o13g2000vbl.googlegroups.com>

On Oct 11, 11:27 pm, The Magnet <a..._at_unsu.com> wrote:

> Tried adding

> indexes to the compared date columns with no improvement either.
> Still full table scans.  And at this time the tables are not even
> large, 200,000 records, though that will grow over time.

It's not sure whether indexes will really be helpful depending on how selective the criteria is but Oracle is actually able to push the predicate into the inline view. You haven't included the "Predicate Information" section from your EXPLAIN PLAN, but it's obvious from the asterisk in front of all the "TABLE ACCESS FULL" operations that the filter predicate is applied to each table within the view.

So the indexes that you have added very likely have been disabled by the awkward conversion applied to the dates, as pointed out by other contributors already. Either you need to use an appropriate functionbased  index on TO_CHAR(event_date,'YYYYMMDD'). If the DATE columns don't contain any time information, then this conversion could simply be removed (event_date = date '2009-09-01'), otherwise you obviously need some way to deal with this, e.g. using the TRUNC function (at least no conversion required, trunc(event_date) = date '2009-09-01', again requiring a suitable function-based index) or a greater than/ less than comparison (event_date >= date '2009-09-01' and event_date < date '2009-09-02')

I second the point that the design looks questionable given the provided query. There might be much better ways to do this depending on the actual requirement.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Sat Oct 24 2009 - 16:04:09 CDT