Home » RDBMS Server » Performance Tuning » Select .... from and performance issues
Select .... from and performance issues [message #64734] Wed, 14 January 2004 09:01 Go to next message
Parbhani
Messages: 3
Registered: January 2004
Junior Member
Hi all,

I am on Oracle 8.1.7 on AIX Unix.

I have some fundamental questions about the select command and the performance of the SQL,

1) The order of tables in the FROM clause, does it makes any difference ? is yes , what ??

2) The order of conditions in the WHERE clause, does that make any difference ? if yes, what ??

3) What is a Star query ?

Please help out.

Thanks in advance
Re: Select .... from and performance issues [message #64735 is a reply to message #64734] Wed, 14 January 2004 09:34 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
Oracle evaluates query from the buttom and therefore
1. Order of tables in the FROM clause should be:
larger table
smaller table
2. Order of conditions should be:
Less selective
More selective
3.http://www.fortunecity.com/skyscraper/oracle/699/orahtml/oracle/o8star.html
Clarification on order of tables [message #64736 is a reply to message #64735] Wed, 14 January 2004 11:44 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The order of the tables in the FROM clause is only significant under the RBO (rule-based optimizer). It makes no difference under the CBO.
Re: Select .... from and performance issues [message #64737 is a reply to message #64736] Thu, 15 January 2004 00:14 Go to previous messageGo to next message
Parbhani
Messages: 3
Registered: January 2004
Junior Member
Yes , I agree that for CBO order of tables in the where clause does not matter.
In case of the CBO also, when the query is parsed, its parsed from button upwords. So, I feel that the order of where clauses should make a difference, because if a more selective condition is at the buttom and Oracle finds that first probably the whole execution plan will change.
Also I have done few experiments with the query execution plan and FROM and WHERE clauses.
I have seen that, the plan changes if I change the order of WHERE clauses, but it remained unchanged (at least in my case) for change of order in FROM clause.
Please share you past experiences on this. Also if you have VLDBs with you, its worth doing few experiments.

Regards
Re: Re: Select .... from and performance issues [message #64738 is a reply to message #64737] Thu, 15 January 2004 05:46 Go to previous messageGo to next message
Epe
Messages: 99
Registered: March 2002
Member
I never did much experiments on it, but I've always been told about the where clause to first put the joins (if present), then going from most to least selective...

Cheers,
Epe
Re: Select .... from and performance issues [message #64739 is a reply to message #64734] Thu, 15 January 2004 05:56 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Also, see here -- >
Joins

The order of the joins is relevant in both RBO and CBO . In case of CBO, the optimizer figures out the best join order for you and hence we dont have to worry too much about ordering the tables in the FROM clause explicitly as long as we make sure to provide the optimizer with up todate statistics.

For eg)

thiru@9.2.0:SQL>set autotrace on explain
thiru@9.2.0:SQL>select /*+ RULE */ count(*) from <B>t,t2</B> where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T2'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T'

-- As seen here, the table in the right most order is the first table(ie driving table) of the query.

thiru@9.2.0:SQL>select /*+ RULE */ count(*) from <B>t2,t</B> where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T2'

-- When we change the join order, the driving table changes ie) the table in the right most order is still the driving table

-- Now,lets create an index to see if there is any chang

thiru@9.2.0:SQL>create index t_idx on t(x);

Index created.

-- Now table T has an index

thiru@9.2.0:SQL>select /*+ RULE */ count(*) from t,t2 where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T2'
   4    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)

thiru@9.2.0:SQL>select /*+ RULE */ count(*) from t2,t where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T2'
   4    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)

-- As seen above,when you have an index, the RBO ignores the order of the tables in the FROM clause and chooses the table with the index for Nested loops join. Here ,the order of the tables in the FROM clause didnt matter. 

-- Now lets create an index on T2 to even the balance.

thiru@9.2.0:SQL>create index t2_idx on t2(x);

Index created.

thiru@9.2.0:SQL>select /*+ RULE */ count(*) from t,t2 where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T2'
   4    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)

-- As seen above and below, T2 (the last table in the FROM clause) is the driving table.

thiru@9.2.0:SQL>select /*+ RULE */ count(*) from t2,t where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T'
   4    2       INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE)

-- As seen above, when both the tables have indexes, the RBO decides to honor the order in the FROM clause to decide its join order ( & driving table).

-- Lets try with CBO

thiru@9.2.0:SQL>analyze table t compute statistics;

Table analyzed.

thiru@9.2.0:SQL>analyze table t2 compute statistics;

Table analyzed.

thiru@9.2.0:SQL>select count(*) from t2,t where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=1 Card=1 Bytes=26)
   3    2       INDEX (FULL SCAN) OF 'T2_IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)

thiru@9.2.0:SQL>select count(*) from t,t2 where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=1 Card=1 Bytes=26)
   3    2       INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE)

-- As seen above, with equal indexes on both the tables(of the same sizes),

the order of the tables in the FROM clause (Left to Right) was respected ,but may not be the case always. 
 For eg, when the table T is much bigger than T2, the order of the tables in the FROM clause doesnt affect

 the join order as the CBO decides the best join order based on the statistics. But the JOIN ORDER still does matter.

thiru@9.2.0:SQL>insert into t select rownum from all_objects;

29795 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view

SP2-0612: Error generating AUTOTRACE EXPLAIN report
thiru@9.2.0:SQL>commit;

Commit complete.

thiru@9.2.0:SQL>analyze table t compute statistics;

Table analyzed.

thiru@9.2.0:SQL>select count(*) from t2,t where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=2 Card=1 Bytes=17)
   3    2       INDEX (FULL SCAN) OF 'T2_IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=4)

thiru@9.2.0:SQL>select count(*) from t,t2 where t.x=t2.x;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=2 Card=1 Bytes=17)
   3    2       INDEX (FULL SCAN) OF 'T2_IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=4)

thiru@9.2.0:SQL>

-- As seen above,the table T is the one used for lookups in the Nested loops join via the index. T2 is the driving table in both the cases.



2) again the CBO picks up the best order for evaluating the predicates depending on the presence of indexes,cardinality etc. See the link for more details.

3) Star query is one which involves the join between the fact table and the dimension tables in a star schema.

-Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: Select .... from and performance issues [message #64740 is a reply to message #64735] Thu, 15 January 2004 08:21 Go to previous messageGo to next message
Michael Dinh
Messages: 7
Registered: January 2004
Junior Member
Table order matters only when using RBO.
Table does not matter when using CBO.
However, you must analyze your tables when using CBO,
Re: Clarification on order of tables [message #64744 is a reply to message #64736] Fri, 16 January 2004 11:30 Go to previous message
andreyp
Messages: 1
Registered: January 2004
Junior Member
This is true until you use /*+ ordered */ or some other hints under the CBO.
Previous Topic: Difference between Joins
Next Topic: how to force the sql query to use or not to use index
Goto Forum:
  


Current Time: Fri Sep 25 03:13:45 CDT 2020