Re: How to find the rows that are different between a table and it's sub view.

On Jun 12, 6:35 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jun 12, 12:06 pm, bay_..._at_yahoo.com wrote:
> > (...)
>
> You will likely have better performance if you directly use the SQL
> statement behind the view, rather than just using the view name. For
> instance, if TABLE_B is actually:
> CREATE VIEW TABLE_B AS
> SELECT
> *
> FROM
> TABLE_A
> WHERE
> IP='127.0.0.1';
>
> You would instead use the above SQL directly in the other SQL
> statements. For instance, if the original SQL statement looked like
> this:
> SELECT
> IP,
> PORT
> FROM
> TABLE_A
> UNION ALL
> SELECT
> IP,
> PORT
> FROM
> TABLE_B;
>
> The above finds all rows in common between the two tables (one table
> and one view actually). Rather than using the above, I would write it
> like this:
> SELECT
> IP,
> PORT
> FROM
> TABLE_A
> UNION ALL
> SELECT
> IP,
> PORT
> FROM
> TABLE_A
> WHERE
> IP='127.0.0.1';
>

Hi!
I'm rather surprised by your statement that a view would perform worse than a query that looks exactly the same as the view definition. Had someone asked me I'd have said they should perform the same (let's say for Oracle 9i2 upwards). Can you throw in a few pointers as to why the performance would be better/worse?

cheers,
Martin Received on Tue Jun 12 2007 - 15:01:59 CDT