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