| Select .... from and performance issues [message #64734] |
Wed, 14 January 2004 09:01  |
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 #64737 is a reply to message #64736] |
Thu, 15 January 2004 00:14  |
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: Select .... from and performance issues [message #64739 is a reply to message #64734] |
Thu, 15 January 2004 05:56 |
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
