Re: why am I getting an index full scan instead of a fast full scan with an analytic function
Date: Mon, 18 Jul 2011 11:05:25 -0400
Message-ID: <CAE-dsOLdCgUGuX3C6v=2cbpie9NKW_uYrXQt9dcoQjQpj7U-1A_at_mail.gmail.com>
How does this return every row in the table? I am doing "where my_num_rows > 1
That should return the saving this as a group by and a having count(*) > 1
what am I am i missing.
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
On Fri, Jul 15, 2011 at 4:45 PM, David Fitzjarrell <oratune_at_yahoo.com>wrote:
> "2 queries. On analytic and one group by. They produce the same output."
>
> No, they don't. The analytic query returns every row along with the
> count associated with the column values:
>
> ...
> C_1 C_2 MY_COUNT
> ---------- ----------------------------------- ----------
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> 20 Test 20 32768
> ...
>
> The second query returns unique pairs of column values with the associated
> counts:
>
> C_1 C_2 COUNT(*)
> ---------- ----------------------------------- ----------
> 20 Test 20 32768
> 5 Test 5 32768
> 11 NULL 32768
> 13 NULL 32768
> 16 Test 16 32768
> 18 Test 18 32768
> 8 Test 8 32768
> 10 Test 10 32768
> 2 Test 2 32768
> 1 Test 1 32768
> 7 Test 7 32768
> 9 Test 9 32768
> 14 Test 14 32768
> 17 NULL 32768
> 3 Test 3 32768
> 4 Test 4 32768
> 6 Test 6 32768
> 15 NULL 32768
> 12 Test 12 32768
> 19 NULL 32768
> The two queries are not equivalent, and in actual use I get the following
> plan for the analytic query:
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3294411647
>
> --------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
>
> --------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 655K| 96M| 1925 (2)|
> 00:00:24 |
> |* 1 | VIEW | | 655K| 96M| 1925 (2)|
> 00:00:24 |
> | 2 | WINDOW BUFFER | | 655K| 6400K| 1925 (2)|
> 00:00:24 |
> | 3 | INDEX FULL SCAN| TEST1_C1C2 | 655K| 6400K| 1925 (2)|
> 00:00:24 |
>
> --------------------------------------------------------------------------------
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter("B"."MY_COUNT">1)
>
> and I get for the group by:
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3694807667
>
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
>
> -----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 12 | 120 | 474 (25)| 00:00:06
> |
> |* 1 | FILTER | | | | |
> |
> | 2 | SORT GROUP BY | | 12 | 120 | 474 (25)| 00:00:06
> |
> | 3 | TABLE ACCESS FULL| TEST1 | 655K| 6400K| 378 (6)| 00:00:05
> |
>
> -----------------------------------------------------------------------------
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(COUNT(*)>1)
> even though I have an index on both columns (10.2.0.3).
>
>
> David Fitzjarrell
>
>
> *From:* Dba DBA <oracledbaquestions_at_gmail.com>
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *Sent:* Friday, July 15, 2011 12:13 PM
>
> *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 |
>
> ---------------------------------------------------------------------------------------------
>
>
>
>
>
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 18 2011 - 10:05:25 CDT