The where condition has different order and different performance examples

Source: Internet
Author: User

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>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.