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

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Fri, 15 Jul 2011 15:13:04 -0400
Message-ID: <CAE-dsOK2nVXsAiSMABJZwFTO-Lw4W3She5ibsHvBPpJhCy-1Gg_at_mail.gmail.com>



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 |
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 15 2011 - 14:13:04 CDT

Original text of this message