How does a WHERE CLAUSE work?

[balajiyes]

How does a WHERE CLAUSE work?

i need a good explanation
thnx,

[aarroyob]

And have you read the documentation? :-)

[balajiyes]

"And have you read the documentation? :-)"

yes sir
i wanna a good explanation how the where clause work?
wheather the WHERE CLAUSE move from downwards or upwards.

eg,
select * from emp
where ename = 'SMITH'
and empno = 12;

in the above example how the oracle access the TBALE

[aarroyob]

The best way to understand that you want is doing some examples:

1. Run the query without indexes and look it´s explain plan
2. Run the query with an index on column empno and look the explain plan.
...

Then you could see the access mode will be different. Normally when you use the where clause is better for the query performance to have indexes on.

Hope that helps

Best regards

Angel

[isaac_02]

binary search?

[balajiyes]

hi,
i want to know how does oracle read a record from a table.
for example,
select * from emp
where ename = 'SMITH'
and empno = 1090;

in the above case i want to know wheather the "empno" will execute first or "ename".

thnx,

[aarroyob]

If you have a complex index (ename,empno) the optimizer will go for it, if you haven´t, probably it will do a full scan table.

[pando]

the optimizer determines the rule, it depends on so many factors

[chrisrlong]

You are venturing into a very large world - the world of the Oracle query optimizer.

The most simple answer is that the order of the WHERE clause is completely irrelevant, but even this is not completely true.

First, there are actually 2 Oracle Optimizers:
- A Rule-Based Optimizer (RBO), which uses pre-defined rules for deciding how to join tables. These rules are based on variables such as simple existence of indexes. One of the variables, albeit somewhat low on the list of priorities, is the order of items in the WHERE clause. Now, it is important to realize that the RBO is all but dead. If you are just getting into Oracle, I would say that it is not even worth worrying about.
-The Cost-Based Optimizer. This optimizer uses statistics as well to determine a plan. Such things as table size, number of distinct values in a column, data distribution (if histograms exist) and *many* other variables to determine the most efficient execution plan for any given statement at any given time. *VERY* far down the list of variables is the order of items in the WHERE clause; SOOO far down that in 99.9% of the cases, the order is *completely* irrelevant.

So basically:

1 - Your question is un-aswerable without more information
2 - You need to do a lot of reading

HTH,

- Chris