Re: why am I getting an index full scan instead of a fast full scan with an 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
>

First, there are more closing ")" than opening "(". Second, it seems that in line 6 "my_num_row" is used as inline_view alias, while in line 8 it's used as a column alias. But, probably this is related to parenthesis mismatch...

Could you provide the query with at least correct syntax?

Regards,
Igor Neyman

On Mon, Jul 18, 2011 at 11:05 AM, Dba DBA <oracledbaquestions_at_gmail.com>wrote:

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