Question: I have written a query to fetch records from an oracle table. But when I use a distinct function It goes into time out and does not give any results. Is there an alternative to using a distinct and still get results?

Only Test3 works without distinct. In Test2, I tried to use a subquery but doesn't work either.

In the sample of the table, there are more than 100k rows. But I have shown only 1 percent of it.

Table1:

```
id                     country
100003                 Poland
PL10005-100001         England
100007                 Finland 
100005                 Sweden
100004                 Norway
100002                 Chicago

```

Table2:

```
id                     country
100003                Poland
100007                Finland
100006                Austria
100004                Norway
100001                India

```

Test1:

```
SELECT
distinct
t1.id,
t2.country,
from table1 t1 inner join table2 t2 on t1.id = t2.id

```

Test2:

```
Select distinct
id, 
country
from 
(
select 
t1.id,
t2.country,
from table1 t1 inner join table2 t2 on t1.id = t2.id)

```

Test3:

```
SELECT
t1.id,
t2.country,
from table1 t1 inner join table2 t2 on t1.id = t2.id

```

Answer: 
The issue is probably not `distinct`. The issue is probably that you have zillions of duplicate rows. Without `distinct`, Oracle is probably returning rows as they are generated, so the time to the *first* row is quick. But getting the whole result set would take a long, long time.

You can see if there are duplicates by using:

```
select id, count(*)
from table1
group by id
having count(*) > 1 

```

And:

```
select id, count(*)
from table2
group by id
having count(*) > 1;

```

You can get the size of the result set (without generating it) using:

```
select sum(t1.cnt * t2.cnt)
from (select id, count(*) as cnt
      from table1
      group by id
      having count(*) > 1 
     ) t1 join
     (select id, count(*) as cnt
      from table2
      group by id
      having count(*) > 1
     ) t2
     on t1.id = t2.id;

```

I am guessing this is much larger than you would expect. You need to figure out what to do about duplicates to fix your performance problem.
Your join statement has the wrong syntax. Try this:

```
SELECT
distinct
t1.id,
t2.country
from table1 t1 inner join table2 t2 on t1.id = t2.id

```