When I saw the SQL statement optimization yesterday in the book, where is the order of the conditions and the performance is different, is this suggestion appropriate in the oracle11g version? Mode 1 better than Mode 2?
Mode 1:
Copy Code code as follows:
Select A.*
From students S,
Class C
where
S.id = C.id
s.id = ' xxxxxxxx '
Mode 2:
Copy Code code as follows:
Select A.*
From students S,
Class C
where
s.id = ' xxxxxxxx '
S.id = C.id
The test results in 10g proved to be the same.
Microsoft Windows [version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\administrator>sqlplus/as Sysdba
Sql*plus:release 10.2.0.1.0-production on Saturday May 11 17:48:55 2013
Copyright (c) 1982, +, Oracle. All rights reserved.
Connect to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options
Sql> alter system flush Shared_pool;
The system has changed.
Sql> alter system flush Buffer_cache;
The system has changed.
Sql> set autotrace on;
Sql> SELECT *
2 from COUNTRIES C,
3 Regions R
4 where c.region_id=r.region_id and c.region_id= ' 4 ';
Regions R
*
Line 3rd Error:
ORA-00942: Table or view does not exist
Sql> SELECT *
2 from HR. COUNTRIES C,
3 hr. Regions R
4 where c.region_id=r.region_id and c.region_id= ' 4 ';
CO country_name region_id region_id
-- ---------------------------------------- ---------- ----------
Region_name
-------------------------
EG Egypt 4 4
Middle East and Africa
IL Israel 4 4
Middle East and Africa
KW Kuwait 4 4
Middle East and Africa
CO country_name region_id region_id
-- ---------------------------------------- ---------- ----------
Region_name
-------------------------
NG Nigeria 4 4
Middle East and Africa
ZM Zambia 4 4
Middle East and Africa
ZW Zimbabwe 4 4
Middle East and Africa
6 rows have been selected.
Execution plan
----------------------------------------------------------
Plan Hash value:4030513296
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU) | Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 6 | 168 | 2
(0) | 00:00:01 |
| 1 | NESTED LOOPS | | 6 | 168 | 2
(0) | 00:00:01 |
| 2 | TABLE ACCESS by INDEX rowid| Regions | 1 | 14 | 1
(0) | 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0
(0) | 00:00:01 |
|* 4 | INDEX Full SCAN | COUNTRY_C_ID_PK | 6 | 84 | 1
(0) | 00:00:01 |
--------------------------------------------------------------------------------
----------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-access ("R".) region_id "=4)
4-filter ("C".) region_id "=4)
Statistical information
----------------------------------------------------------
628 Recursive calls
0 db Block gets
127 consistent gets
Physical Reads
0 Redo Size
825 Bytes sent via sql*net to client
385 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
Sorts (memory)
0 Sorts (disk)
6 Rows processed
Sql>
#############
Sql> alter system flush Shared_pool;
The system has changed.
Sql> alter system flush Buffer_cache;
The system has changed.
SELECT *
From HR. COUNTRIES C,
Hr. Regions R
where
C.region_id= ' 4 '
6 and c.region_id=r.region_id;
CO country_name region_id region_id
-- ---------------------------------------- ---------- ----------
Region_name
-------------------------
EG Egypt 4 4
Middle East and Africa
IL Israel 4 4
Middle East and Africa
KW Kuwait 4 4
Middle East and Africa
CO country_name region_id region_id
-- ---------------------------------------- ---------- ----------
Region_name
-------------------------
NG Nigeria 4 4
Middle East and Africa
ZM Zambia 4 4
Middle East and Africa
ZW Zimbabwe 4 4
Middle East and Africa
6 rows have been selected.
Execution plan
----------------------------------------------------------
Plan Hash value:4030513296
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU) | Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 6 | 168 | 2
(0) | 00:00:01 |
| 1 | NESTED LOOPS | | 6 | 168 | 2
(0) | 00:00:01 |
| 2 | TABLE ACCESS by INDEX rowid| Regions | 1 | 14 | 1
(0) | 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0
(0) | 00:00:01 |
|* 4 | INDEX Full SCAN | COUNTRY_C_ID_PK | 6 | 84 | 1
(0) | 00:00:01 |
--------------------------------------------------------------------------------
----------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-access ("R".) region_id "=4)
4-filter ("C".) region_id "=4)
Statistical information
----------------------------------------------------------
656 Recursive calls
0 db Block gets
131 Consistent gets
Physical Reads
0 Redo Size
825 Bytes sent via sql*net to client
385 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
Sorts (memory)
0 Sorts (disk)
6 Rows processed
Sql>
