| What is difference between 2 statements? [message #376916] |
Fri, 19 December 2008 02:53  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
I have one question, of course, I've searched in google and in orafaq_tuning_guide. However, I've not got the right answer, so that, I post this at here.
metatest@META> create table objects as
2 select * from sys.all_objects;
Table created.
metatest@META> create index idx_obj_id on objects(object_id);
Index created.
metatest@META> create index idx_obj_type on objects(object_type);
Index created.
You will see the
metatest@META> set autotrace traceonly
metatest@META> select * from objects
2 where object_type like '%ABLE';
1931 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 80137940
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2211 | 276K| 202 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJECTS | 2211 | 276K| 202 (1)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" LIKE '%ABLE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
248 recursive calls
0 db block gets
940 consistent gets
0 physical reads
0 redo size
96670 bytes sent via SQL*Net to client
1808 bytes received via SQL*Net from client
130 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1931 rows processed
metatest@META>
Yeap! Full table scan..
metatest@META> select * from objects
2 where object_type like 'TAB%';
2079 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1205454014
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 2079 | 259K| 96 (0)
| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 2079 | 259K| 96 (0)
| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_OBJ_TYPE | 2079 | | 8 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE" LIKE 'TAB%')
filter("OBJECT_TYPE" LIKE 'TAB%')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
498 consistent gets
11 physical reads
0 redo size
105936 bytes sent via SQL*Net to client
1918 bytes received via SQL*Net from client
140 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2079 rows processed
metatest@META>
Index range scan...
Statistics:
FTS Idx RS
248 recursive calls vs 8 recursive call
940 consistent gets vs 498 consistent gets
0 physical reads vs 11 physical reads
96670 bytes sent via SQL*Net to client vs 105936 bytes sent via SQL*Net to client
130 SQL*Net roundtrips to/from client vs 140 SQL*Net roundtrips to/from client
1931 rows processed vs 2079 rows processed
Of course, in 2 statements, the Index_Range_Scan had little Cost CPU and elapsed times to the other.
However, Why the first statement did not use the index but the second did?
Why the second statement has had more physical reads than the first?
In one purpose, why did the first statement only got 1931 rows but the second scanned 2079 rows?
Full table scan is not always evil, of course, this is in the case that table does not contain more rows.
May you clarify more?
Thank you!
[Updated on: Fri, 19 December 2008 02:59] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
