I saw in the book yesterday that the where condition sequence is different for SQL statement optimization, and the performance is different. Is this recommended for Oracle11G? Method 1 is better than method 2?
Method 1:
Copy codeThe Code is as follows: select .*
From students s,
Class c
Where
S. id = c. id
S. id = 'xxxxxxxx'
Method 2:Copy codeThe Code is as follows: select .*
From students s,
Class c
Where
S. id = 'xxxxxxxx'
S. id = c. id
The test results in 10 Gb prove to be the same.
Microsoft Windows [version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C: \ Documents ents 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,200 5, Oracle. All rights reserved.
Connect:
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 been changed.
SQL> alter system flush buffer_cache;
The system has been 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
*
Row 3 has an 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_NAME
-------------------------
EG Egypt 4 4 4
Middle East and Africa
IL Israel 4 4 4
Middle East and Africa
KW Kuwait 4 4
Middle East and Africa
CO COUNTRY_NAME 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
You have selected 6 rows.
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)
Statistics
----------------------------------------------------------
628 recursive cballs
0 db block gets
127 consistent gets
20 physical reads
0 redo size
825 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
#############
SQL> alter system flush shared_pool;
The system has been changed.
SQL> alter system flush buffer_cache;
The system has been 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_NAME
-------------------------
EG Egypt 4 4 4
Middle East and Africa
IL Israel 4 4 4
Middle East and Africa
KW Kuwait 4 4
Middle East and Africa
CO COUNTRY_NAME 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
You have selected 6 rows.
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)
Statistics
----------------------------------------------------------
656 recursive cballs
0 db block gets
131 consistent gets
22 physical reads
0 redo size
825 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>