Link to home
Create AccountLog in
Avatar of toooki
toooki

asked on

Oracle performance problem with a query

I have a table MyTab:

F1    F2                     F3
-------------------------------
A      F2_val1            F3_val1a
A       F2_val2            F3_val2a
B       F2_val3            F3_val3a
C      F2_val3            F3_val3a      

This table is very huge 1.7M records.

I need an output like:

F1    F2                     F3                     F4
--------------------------------------------------
A      F2_val1            F3_val1a          2
A       F2_val2            F3_val2a         2
B       F2_val3            F3_val3a         1
C      F2_val3            F3_val3a          1

The output is same as input with an additional column F4.
The value of F4 is the number of times the F1 value appear in that table.
I wrote a query that takes too long. Could you help me get an optimal query for it...
The contents of F2 and F3 could be ignored.
Thanks...
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

what is your query
SOLUTION
Avatar of stergium
stergium
Flag of Greece image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of toooki
toooki

ASKER

Thanks a lot to all.
I was using the group by function like below that worked but it seems somewhere it was taking long
select t.f1,t.f2,t.f3,count(t.f1) f4 from mytab t
group by t.f1,t.f2,tf3

but it seems
select f1, f2, f3, count(f1) over(partition by f1 order by f1) f4 from table;
the above works faster...and works without issues for me.

Thank you all.