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

Am I the only one, who sees this query as simply wrong?

>
> 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@xxxxxxxxx>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@xxxxxxxxx>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@xxxxxxxxx>
>> *To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
>> *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 |
>>
>> ---------------------------------------------------------------------------------------------
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>

Other related posts: