Oracle : Is my DISTINCT function working properly? I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. But I get a doubt if DISTINCT function is working properly for 1 TB full data of alphanumeric values?

First query

SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_1;
SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_2;


Difference of count is 3910

Second query

SELECT COUNT(*) DOC_ID FROM
(SELECT DISTINCT(DOC_ID) FROM TABLE_1
MINUS
SELECT DISTINCT(DOC_ID) FROM TABLE_2);


count is 0

Does DISTINCT work properly on alphanumeric DOC_ID, especially if my data is huge?
Your two queries are counting the same thing. 

Consider this data:

create table table1 (doc_id varchar(20));
create table table2 (doc_id varchar(20));

insert into table1 values ('foo');
insert into table1 values ('foo');
insert into table1 values ('bar');
insert into table1 values ('bar');

insert into table2 values ('foo');
insert into table2 values ('foo');
insert into table2 values ('bar');
insert into table2 values ('bar');


count(distinct doc_id) returns 2 for both tables. 

But the following returns no rows at all:

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;


So the count of that is of course zero. 

If you change the sample data your second query still counts something different: 

delete from table2;
insert into table2 values ('foo2');
insert into table2 values ('foo2');
insert into table2 values ('bar2');
insert into table2 values ('bar2');


count(distinct doc_id) still returns 2 for both tables, so the difference is still 0 between those two counts

But, 

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;


Now only returns foo and bar because foo2 and bar2 are not in table1. 

If you add another distinct value to table2: 

insert into table2 values ('bar3');


The difference in the distinct count from both tables is 1 (2 distinct values in table1, 3 in table2), but the minus still returns the two distinct values from the first table.