Tag Info

New answers tagged

0

Continued from previous post. So now we know that DISTINCT and GROUP BY are handled indeed differently, lets go back to question 1. Knowing the above, going back to the original, slower query, this: select company_id from t1 join t2 using(product_id) join ( select company_id from (select distinct company_id from t1) ...


2

Output is from a 11.2.0.4.6 Enterprise Edition database on Oracle Linux 7.1 x86-64 platform. Lets start with question 2 and an easy example. DISTINCT and GROUP BY are handled differently: the optimizer is able to completely eliminate a DISTINCT under certain circumstances, but it can not do the same with GROUP BY. Here is an example: create table t4 as ...


0

It is not due to any difference in Oracle's treatment of DISTINCT and GROUP BY. If we replace GROUP BY in the second query with a DISTINCT in SELECT we shall get similar performance/ plan cost as the first one I think, if we are using same clause in both the inner and outer queries with a natural join then Oracle parser is able to recognize that it will ...


1

Fisrt optimizer takes one of best plans - not the best. This is because when the optimizer works the time for calculating the best plan can be bigger than the time it saves during the execution. And yes, optimizer works based on statistics. So if your statistics is old enough optimizer will create plan suiatble for that statistics - i.e. for the moment of ...


4

The optimizer strives to get a plan that is "good enough", and this is not always the optimal one. A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan. In some cases, too many indexes on a table can also cause this, as the optimizer might use an index that is not the best one because as ...



Top 50 recent answers are included