Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
3 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Difference in the number of rows returned
Posted on 2011-09-09
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
//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
[X]
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
7 Comments
Active today
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.
I have seen something like this in 11g with results caching.
Active today
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
select * from DBA_REWRITE_EQUIVALENCES
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
select * from DBA_REWRITE_EQUIVALENCES
Hi,
Can you try:
Can you show the execution plan with predicates (set autotrace trace explain)
Regards,
Franck.
Can you try:
select count(*) from (select distinct col1 , col2 from table)
Can you show the execution plan with predicates (set autotrace trace explain)
Regards,
Franck.
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 ?
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 ?
Active today
yes that will make them distinct
like this...
1,1
1,2
1,3
a,b
c,d
e,f
e,g
e,h
but not necessarily ordered
like this...
1,1
1,2
1,3
a,b
c,d
e,f
e,g
e,h
but not necessarily ordered
Active today
>>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.
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');
commit;
select distinct col1, col2 from tab1;
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
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…
How to Create User-Defined Aggregates in Oracle
Before we begin creating these things, what are user-defined aggregates? They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month3 days, 2 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.