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

  • From: Dba DBA <oracledbaquestions@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 15 Jul 2011 15:13:04 -0400

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 |
---------------------------------------------------------------------------------------------

Other related posts: