Difference in the number of rows returned

Posted on 2011-09-09
Medium Priority
Last Modified: 2013-12-19
select distinct col1 , col2 from table;

//75048 Records Returned

with temp as (select distinct col1, col2 from table)
select count(*) from temp

// 75833 Records Returned

I am not able to figure out why the difference despite the same query.. something elusive hidden somewhere.. requesting all the experts to help me please make me understand.. is this something basic or is this something normal confusion for everyone... Please help me understand
Question by:XxtremePro
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36511809
This was posted in a 10.x Zone.  Please confirm you are running 10g.

I have seen something like this in 11g with results caching.
LVL 74

Accepted Solution

sdstuber earned 1002 total points
ID: 36511966
Just in case,  I'll ask the obvious....

Were 785 rows added to the table between the time you ran the queries?

assuming no...

10g has a few bugs related to WITH clauses,  you may have encountered one. Are you fully patched?

A little more obscure,  check for  equivalencies created with dbms_advanced_rewrite

LVL 15

Expert Comment

by:Franck Pachot
ID: 36527616
Can you try:
select count(*) from (select distinct col1 , col2 from table)

Open in new window

in order to see if it comes with a subquery factoring bug ?
Can you show the execution plan with predicates (set autotrace trace explain)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 36531477
Thanks Franck

I am a newbie in the world of programming with database, your statements are way advanced to what I know in sql.

ok now looking at what I would need I would say the need is to pick up just two values from the table as per requirement

select distinct col1, col2 from table1;

col1, col2 both are primary keys. Would the above query ensure that I get only distinct col1 values and col2 values?? If not then what should be done to ensure both the col1 and associate col2 values are distinct ?
LVL 74

Assisted Solution

sdstuber earned 1002 total points
ID: 36531502
yes that will make them distinct

like this...


but not necessarily ordered
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 498 total points
ID: 36531532
>>col1, col2 both are primary keys

Are they the only two columns defined in the Primary Key?

>>. Would the above query ensure that I get only distinct col1 values and col2 values??

Yes.  Just set up a simple test on your development database to see what effects different queries have.

Now that we have answered that, please answer the other questions we have asked.
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','b');
insert into tab1 values('a','c');
insert into tab1 values('a','c');

select distinct col1, col2 from tab1;

Open in new window


Author Closing Comment

ID: 37135653
Thanks to all

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question