RE: why am I getting an index full scan instead of a fast full scan with an analytic function

From: Jorgensen, Finn <Finn.Jorgensen_at_constellation.com>
Date: Fri, 15 Jul 2011 16:02:38 -0400
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF02693F98D9D2_at_EXM-OMF-04.Ceg.Corp.Net>

The below explanation came out of a Hotsos 2011 presentation by Maria Colgan who's the manager of the Oracle optimizer team :

Full Index Scan
Processes all leaf blocks of an index, but only enough branch blocks to find 1st leaf block. Used when all necessary columns are in index & order by clause matches index structure or if sort merge join is done

Your query seem to match that description pretty well. How does the number of leaf blocks compare to the total number of blocks in the index? If they're not very close this scan would do significantly less work.

Thanks,
Finn

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA Sent: Friday, July 15, 2011 3:13 PM
To: ORACLE-L
Subject: why am I getting an index full scan instead of a fast full scan with an analytic function

DB Version: 10.2.0.5
OS: Hp-unix
db_file_multblock_read_count 64 (4k block sizes. really old DB. no down time to re-create in an 8k block size DB) Not sure if these affect my current issue, but posting anyway.

optimizer_index_caching              integer     10
optimizer_index_cost_adj             integer     100

Table Size: 149 GBs.
Index Size: 21 gb (both columns that I use are in the index) Not partitioned
sort_area_size 100m
tempfile size: 96 gb. I am basically the only person on the database.

2 queries. On analytic and one group by. They produce the same output. Analytic Query: Oracle chooses an index full scan Group By: Oracle chooses a fast full scan.

I can't figure out why Oracle would ever want to do a regular 1 block at a time full scan on a 21 gb index. See explain plans below. Where do I look to figure this out? Is the algorithm for the analytic function that much different than the one for a group by?

Analytic function

  1 explain plan for
  2 select b.*

  3        FROM (
  4          SELECT /*+ partition(a,4) */
  5              col1,col2,   COUNT(*) OVER (PARTITION BY   col1,col2)
  6      ) my_num_rows
  7            FROM BIG_TABLE a) b
  8*      WHERE my_num_rows > 1

SQL> /

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 378M| 11G| 3152K (1)| 02:37:38 |

|*  1 |  VIEW                        |                    |   378M|    11G|  3152K  (1)| 02:37:38 |


|   2 |   WINDOW BUFFER   |                    |   378M|  5773M|  3152K  (1)| 02:37:38 |

|   3 |    INDEX FULL SCAN| BIG_TABLE_IND      |   378M|  5773M|  3152K  (1)| 02:37:38 |

----------------------------------------------------------------------------------------

GROUP BY:   select col1,col2, COUNT(*)
  from BIG_TABLE
  group by col1,col2
 having count(*) > 1

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 18M| 288M| 471K (36)| 00:23:35 |

|*  1 |  FILTER                |                    |       |       |            |          |


|   2 |   SORT GROUP BY        |                    |    18M|   288M|   471K (36)| 00:23:35 |

| 3 | INDEX FAST FULL SCAN| BIG_TABLE_IND| 378M| 5773M| 324K (7)| 00:16:14 |

---

>>> This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 15 2011 - 15:02:38 CDT