Home » SQL & PL/SQL » SQL & PL/SQL » Simple example of (not) using an index
Simple example of (not) using an index [message #594104] Fri, 23 August 2013 10:00 Go to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
An interesting thing, I am still thinking on this.

1. As expected, optimizer goes for a FTS(no issues) -
SQL> DROP TABLE tbl;
 
Table dropped

SQL> create table tbl AS select  * from  dba_objects;
 
Table created

SQL> create index tbl_idx on tbl(object_id);
 
Index created

SQL> exec dbms_stats.gather_table_stats('SCOTT','TBL');
 
PL/SQL procedure successfully completed

SQL> SELECT 'Table has ' || COUNT(*) || ' rows with ' || COUNT(OBJECT_ID) ||' '||
  2         'rows where object_id is not null' X
  3    FROM TBL;
 
X
--------------------------------------------------------------------------------
Table has 80461 rows with 80454 rows where object_id is not NULL

SQL> select clustering_factor from dba_indexes where index_name='TBL_IDX';
 
CLUSTERING_FACTOR
-----------------
             1492
SQL> explain plan for
  2      select  *
  3        from  tbl
  4        where object_id is not NULL;
 
Explained

SQL>       SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 909258111
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 80454 |  7699K|   258   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TBL  | 80454 |  7699K|   258   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID" IS NOT NULL)
 
13 rows selected


2. Per Oracle docs, if number of rows to be fetched is < or ~ 15% of the total rows in the table, using the index is better, else a FTS is better. I took 14700 rows, around 18.26% of the total rows. Beyond which it goes for a FTS. So, for a maximum of 18.26% of rows to be fetched, it is using index.

SQL> update tbl set object_id = case when rownum <= 14700 then object_id END;
 
80461 rows UPDATED

SQL> COMMIT;
 
Commit COMPLETE

SQL> exec dbms_stats.gather_table_stats('SCOTT','TBL');
 
PL/SQL procedure successfully completed

SQL> SELECT 'Table has ' || COUNT(*) || ' rows with ' || COUNT(OBJECT_ID) ||' '||
  2         'rows where object_id is not null' X
  3    FROM TBL;
 
X
--------------------------------------------------------------------------------
Table has 80461 rows with 14700 rows where object_id is not NULL

SQL> select clustering_factor from dba_indexes where index_name='TBL_IDX';
 
CLUSTERING_FACTOR
-----------------
              217
              
SQL> explain plan for
  2      select  *
  3        from  tbl
  4        where object_id is not NULL;
 
Explained

SQL>       SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3069068003
--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 14700 |  1363K|   256   (1)| 00:
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL     | 14700 |  1363K|   256   (1)| 00:
|*  2 |   INDEX FULL SCAN           | TBL_IDX | 14700 |       |    38   (0)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
 
14 rows selected


3. I verified, for 14800 and more rows it goes FTS.

4. Using an aggregate function COUNT, it goes for an FAST FULL SCAN.

SQL> DROP TABLE tbl;
 
Table dropped
SQL> create table tbl AS select  * from  dba_objects;
 
Table created
SQL> create index tbl_idx on tbl(object_id);
 
Index created
SQL> exec dbms_stats.gather_table_stats('SCOTT','TBL');
 
PL/SQL procedure successfully completed
SQL> SELECT 'Table has ' || COUNT(*) || ' rows with ' || COUNT(OBJECT_ID) ||' '||
  2         'rows where object_id is not null' X
  3    FROM TBL;
 
X
--------------------------------------------------------------------------------
Table has 80461 rows with 80454 rows where object_id is not null
SQL> select clustering_factor from dba_indexes where index_name='TBL_IDX';
 
CLUSTERING_FACTOR
-----------------
             1492
 
SQL> 
SQL> explain plan for select count(*) from tbl where object_id is not null;
 
Explained
SQL>       SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1819699472
--------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     5 |    42   (3)| 00:00:01
|   1 |  SORT AGGREGATE       |         |     1 |     5 |            |
|*  2 |   INDEX FAST FULL SCAN| TBL_IDX | 80454 |   392K|    42   (3)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
 
14 rows selected


5. Now, if I include ORDER BY, it should go for INDEX FULL SCAN, since it has to look for sorted values and thus should end up with single block read. However, it still goes for FAST FULL SCAN, interesting.

SQL> explain plan for select count(*) from tbl where object_id is not null order by object_id;
 
Explained
SQL>       SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1819699472
--------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     5 |    42   (3)| 00:00:01
|   1 |  SORT AGGREGATE       |         |     1 |     5 |            |
|*  2 |   INDEX FAST FULL SCAN| TBL_IDX | 80454 |   392K|    42   (3)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
 
14 rows selected


6. Now as expected, if I include ORDER BY in normal select query it goes for INDEX FULL SCAN.

SQL> explain plan for select * from tbl where object_id is not null order by object_id;
 
Explained
SQL>       SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3069068003
--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 80454 |  7699K|  1674   (1)| 00:
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL     | 80454 |  7699K|  1674   (1)| 00:
|*  2 |   INDEX FULL SCAN           | TBL_IDX | 80454 |       |   180   (1)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
 
14 rows selected


So, regarding points 4 and 5, I am not able to understand. Any thoughts?

[UPDATE : I did some more research in my development environment regarding the FAST FULL SCAN and INDEX FULL SCAN, but different test cases are showing different results. The tkprof output has put me in dilemma. If it is beyond the scope of this thread, I would open a new thread. I might be missing a trivial thing, appreciate if my understanding is given a better direction. What to you guys say?]

[Updated on: Fri, 23 August 2013 13:20]

Report message to a moderator

Re: How to make IS NOT NULL condition to use index [message #594137 is a reply to message #594104] Fri, 23 August 2013 15:59 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Lalit,

Not sure if it may be a case for your 2nd point, but Oracle gather statics on sample data (auto_size), not on whole data (applicable for 10g).
Check how much is the sampling settings, put it 100%, update stats and try again.

https://blogs.oracle.com/optimizer/entry/improvement_of_auto_sampling_statistics_gathering_feature_in_oracle_database_11g

Regards,
Manu

[Updated on: Fri, 23 August 2013 16:03]

Report message to a moderator

Re: How to make IS NOT NULL condition to use index [message #594138 is a reply to message #594137] Fri, 23 August 2013 16:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi Manu,

I appreciate your effort on this. My test cases are on version 11g. Yes I blindly use Auto sampling and I never touch the sample size precentage.

[Updated on: Fri, 23 August 2013 20:52]

Report message to a moderator

Re: Simple example of (not) using an index [message #594153 is a reply to message #594104] Sat, 24 August 2013 01:20 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
I've split this off from the topic that was hijacked.

I think your problem is that you don't understand the SQL execution cycle, the relevant parts of which are:
first, row selection
second, aggregation
third, column projection
fourth, ordering.
You description (5) suggests that you believe the sequence to be the reverse.
Re: Simple example of (not) using an index [message #594161 is a reply to message #594153] Sat, 24 August 2013 02:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks for the reply John.

I understand the SQL execution cycle explained by you concisely. In point 6, I am very clear that, to avoid the cost of ordering, optimizer goes for a INDEX FULL SCAN since it uses the indexed column.

However, my confusion is that with aggregate function(COUNT), it did not happen the similar way.

Okay, now I got my answer while putting up a question! Please correct me if I am wrong. In point 4 and 5, the COUNT function does not have to bother about the ordering of the rows returnd at all. So, it goes for a INDEX FAST FULL SCAN. In any other case, even if ordering happens, it will happen after the Index scan, at the last stage of SQL execution. The order by clause with count is too stupid I guess.
Re: Simple example of (not) using an index [message #594171 is a reply to message #594161] Sat, 24 August 2013 07:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
Lalit Kumar B wrote on Sat, 24 August 2013 03:30
In any other case, even if ordering happens, it will happen after the Index scan, at the last stage of SQL execution.


Not true. Index is already ordered (provides ability to read it in index column order to be precise). So in case 6 where we need actual data ordered by index column optimizer is smart enough to understand that using INDEX FULL SCAN to fetch data will fetch data in required order. That is why case 6 execution plan has no separate step for ORDER BY - it is achieved as part of INDEX FULL SCAN. And INDEX FULL SCAN means reading index block-by-block. In cases where COUNT is used optimizer understands we are ordering not data but data aggregation and therefore we can use INDEX FAST FULL SCAN which will read index using faster multiblock read. Ande SORT AGGREGATE step takes care of counting and ordering.

SY.
Re: Simple example of (not) using an index [message #594195 is a reply to message #594171] Sun, 25 August 2013 22:25 Go to previous messageGo to next message
Kevin Meade
Messages: 1936
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi Kumar, how is HIGH TECH CITY?

There are three reasons why we might put a column in an index.

ACCESS
FILTER
COVERAGE


ACCESS=get index entries we think we want.
FILTER=after ACCESS, throw away index entries we just fetched that we now realize we don't want (indicates possible tuning opportunity).
COVERAGE=index has all the columns we need to satisfy the query so don't bother to go to the table.


#2 and #3 show there is clearly a threshold for each query and associated data, for which INDEX becomes better that TABLE SCAN.  Oracle says 15% in its docs.  You demonstrated a case of 18%.  I say the norm is more like 2%.

#4 shows COVERAGE.  For this query the index has all columns needed so if we use it we can avoid visiting the table.

#5 is the same as #4.  Your mistake is that you think just because you put an ORDER BY into the query, that oracle must do it.  The order by is meaningless to the query.  It cannot change the count so why bother doing it.  Oracle knows this so it just tossed the order by clause away leaving you with the same query as #4.

#6 shows optimizer skipping a sort by fetching rows from the index in sorted order.


INDEX FAST FULL SCAN is similar to FULL TABLE SCAN.  It uses MULTI-BLOCK I/O and does not care what order the rows are returned in.

INDEX FULL SCAN uses SINGLE BLOCK I/O and fetches rows in index order.


Good luck. Kevin
Re: Simple example of (not) using an index [message #594773 is a reply to message #594195] Mon, 02 September 2013 12:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi Kevin,

Hi-tech city is simply awesome as always Smile

Thanks for the clarification on each points of my demonstration. I have understood from bits n pieces to a complete concept.
Re: Simple example of (not) using an index [message #594774 is a reply to message #594773] Mon, 02 September 2013 12:57 Go to previous message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks to John and Solomon for pointing out the gaps of my understanding and making the things crystal clear.
Previous Topic: Locking and foreign key indexes
Next Topic: CREATE TABLE: tablename with data from subselect (variable)
Goto Forum:
  


Current Time: Sat Aug 30 16:04:28 CDT 2014

Total time taken to generate the page: 7.24963 seconds