Hi all,

Just to follow up a discussion that we had during last meetup.
I was stating that FULL SCAN isn't the best for selecting one row from
one row table.
Here is the example showing that I was saying.

A full scan select 3 blocks. An index scan does the same thing using 1
block read :)
In earlier versions of Oracle you could use that technique to optimize
selects from DUAL table. Starting from 10G (if I am not mistaken)
Oracle optimize select sysdate (and other fields) to avoid any LIOs.

Have a short productive week :)

Yury


08:15:27 SYSTEM:four> set autotrace on stat

08:15:38 SYSTEM:four> create table one_block_tab ( n number );

Table created.

08:17:07 SYSTEM:four> insert into one_block_tab values (1);

1 row created.

08:17:24 SYSTEM:four> commit;

Commit complete.

08:17:48 SYSTEM:four> create index ONE_BLOCK_TAB_n1 on ONE_BLOCK_TAB (n);

Index created.

08:19:08 SYSTEM:four> select n from ONE_BLOCK_TAB where n=1;

         N
----------
         1


...
          1  consistent gets
...

08:19:17 SYSTEM:four> drop index ONE_BLOCK_TAB_N1;

Index dropped.

08:20:06 SYSTEM:four> select n from ONE_BLOCK_TAB where n=1;

         N
----------
         1


...
          3  consistent gets
...





08:26:58 SYSTEM:four> select * from dual;

D
-
X


...
          3  consistent gets
...

08:27:07 SYSTEM:four> select sysdate from dual;

SYSDATE
-------------------
2010.06.07 08:27:14


...
          0  consistent gets
...


08:27:29 SYSTEM:four> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production




Yury Velikanov
Oracle [Apps] DBA

The Pythian Group (Sydney Office) - www.pythian.com
http://timeanddat...

From Sydney:
+61 (0)280147452 or +61 (0)280149235 x 277 - office
+61 (0)414252591 - cell

From US & Canada
+1 [masked]  x 277 - office

---------------------------------
IMs:
---------------------------------
Skype 	j.velikanovs 	
AIM 	pythianvelikanov 		
GTalk 	[address removed] 	
MSN 	[address removed] 	
Y! 	[address removed]
---------------------------------
Web resources:
---------------------------------
http://otn.oracle...
http://au.linkedi...
---------------------------------
--
Catch Alex & Sheeri at ODTUG/Kaleidoscope - June 27 - July 1. 
Hear Sheeri speak or email [address removed] to meet with Pythian.