RE: Stop Criteria for Sql Execution

This question might look simple but the fact is it has to do with a bigger issue.
I'm splitting a big table in a data warehouse based on certain conditions into two tables and I want to create a view that union-all on the two tables. For some reports one of the tables will not have any data to return and I want to make sure no time will be spent finding data in that table.
So a view will be created on the table that will have some predicate like "column1=3D10"
The outer view will be a union-all of this view and the other table (or view).
When a user submits a sql that has a predicate "column1=3D20", this predicate will be pushed to the two internal views. One of the tables will end up having "where column1=3D10 and = column1=3D20",
and the sql will stop without scanning that table.

Waleed

-----Original Message-----

From: Mercadante, Thomas F [mailto:thomas.mercadante_at_labor.state.ny.us]=20 Sent: Monday, March 07, 2005 1:40 PM
To: Khedr, Waleed; oracle-l_at_freelists.org Subject: RE: Stop Criteria for Sql Execution=20

Waleed,

Maybe Oracle should be smarter and recognize the craziness of the query.

But then, maybe the developer should also recognize the problem.

Perhaps your frustration is pointing in the wrong direction?

Tom

-----Original Message-----

From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]=20 Sent: Monday, March 07, 2005 11:38 AM
To: oracle-l_at_freelists.org
Subject: Stop Criteria for Sql Execution=20

Why does Oracle have to execute the sql and scan data in a table for a sql like this:

Select * from table
Where column1 is null and column1=3D3D10

Or=3D20

Select * from table
Where column1=3D3D10 and column1=3D3D20

All the research money going to optimize the optimizer, and still can't take care of simple stuff like this.

Am I missing something?

I will probably start searching for smarter databases :)

Waleed
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 07 2005 - 14:53:05 CST