why am I getting an index full scan instead of a fast full scan with an analytic function
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 |(1)| 02:37:38 |
| 2 | WINDOW BUFFER | | 378M| 5773M| 3152K
(1)| 02:37:38 |
| 3 | INDEX FULL SCAN| BIG_TABLE_IND | 378M| 5773M| 3152K
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-lReceived on Fri Jul 15 2011 - 14:13:04 CDT