"I could swear I created an Index..but Oracle doesnt seem to USE it all of a sudden.."

okay, so we know what LIOs mean and why to take that into consideration while tuning..

now moving on to Oracle community's famous belief - "Full table scans are evil..Indexes are super good"

I will save myself from lot of texual explanation and just get into copy & pasting business..its easy ,plus you will also get used to this blogs style (no theory where its not necessary)..

just reread the topic again and you know, how many times you have said that yourself or heard that from your fellow team members..

-- okay, we have the same table we created before..
-- table with ~ 100K rows..
-- lets say you (or a developer) wanted to get all rows from table whose id value is
-- greater than 100 (probably it wouldnt make sense to select off a
-- sequence id like that..but just for this demo, just think its
-- some meaningful column like salary or basic pay or something)

so here comes the query (with plan and statistics listed below)
---------------------------------------------------------------

DEMO@orcl>select * from test_table1 where id>100
2 /

49780 rows selected.

Elapsed: 00:00:00.57

Execution Plan
----------------------------------------------------------
Plan hash value: 2372207557

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49782 | 583K| 33 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TABLE1 | 49782 | 583K| 33 (7)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID">100)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3438 consistent gets
1 physical reads
0 redo size
1118460 bytes sent via SQL*Net to client
36879 bytes received via SQL*Net from client
3320 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49780 rows processed

Guess what happens next? Our developer gets freaked out..just short of calling 911 and complaining about dreadful Full table scan causing him/her a heart attack..

Then , our senior developer (or a DBA) comes in with a big smile and say "Buddy, you know..Oracle always does things like that..and this is when you use Hints..and I have been tuning such stupid oracle mistakes for last n years..".

What follows is a quick search on google for 'Oracle+hints+in+sql+index+use' and quick scan of top 5 articles and a quick grasp of what hint should be used..and here in the next 10-15 minutes, the same query gets rewritten with an Index hint like below

Senior Developer rewrites the query and exhibits his/her sql expertise with hints
--------------------------------------------------------------------

DEMO@orcl>ed
Wrote file afiedt.buf

1 select /*+ INDEX(T TEST_TABLE_UIDX) */ -- thats the new hint added
2* * from test_table1 t where id>100
DEMO@orcl>/

49780 rows selected.

Elapsed: 00:00:00.57

Execution Plan
----------------------------------------------------------
Plan hash value: 3929903905

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49782 | 583K| 238 (2)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE1 | 49782 | 583K| 238 (2)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | TEST_TABLE_UIDX | 49782 | | 113 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">100)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6857 consistent gets
0 physical reads
0 redo size
1317523 bytes sent via SQL*Net to client
36879 bytes received via SQL*Net from client
3320 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49780 rows processed

so looking at explain plan, both the developer and senior developer now breath easy and smile..the most dreadful "full table scan" just got disappeared and replaced by "Index Scan".


But in reality what happened was..

Operation Blocks read (LIOs)
--------- ------------------
Full Table 3438
Index Scan 6857

so the tuning team , just managed to double the workload without realizing Oracle was indeed doing the right thing before..


so next time you see a full table scan, first check if the index is in place..if its there , but still oracle is doing full table scan, time to check what percentage of data are you fetching from the table.

Remember, Index lookup happens block by block (slow)..whereas Oracle performs table scan in bulk (set of 16 blocks or 32 blocks etc depending on parameter setting)..so table scan could be lot more faster than index lookup..


I can already hear.."ok dude, enough!!! Just say your final conclusion..Will ya?"..so here it is

• Full Table scans are not ALWAYS bad ( there are perfect and valid reasons and scenarios for Oracle to switch to Full table scan ignoring existing Index.)
• Hints..are meant for fixes (temporary)..dont show your expertise by googling up and adding 10-15 unheard hints..Instead think, why Oracle is not doing what you think it should do..and use autotrace to compare your way and Oracle's way..Validate which option performs less LIOs.
• Nexttime anyone in your team suggests Index hint to avoid full table scan..dont look at the time ..look at the LIOs between the two versions..The more you talk about Blocks and LIOs, the more your DBA fears to come close to you :)
  

Catch you in next ..until then "Happy Sql"-ing..:)