0

I have the below users and network information in a USER table. I would like to fetch all the Users for a given NetworkID. 

ID  Name        Value      Owner
1   UserID      123        111
2   NetworkID   567        111
3   FName       ABC        111
4   LName       BCD        111
5   UserID      234        222
6   NetworkID   567        222
7   FName       DEF        222
8   LName       EFG        222
9   UserID      345        333
10  NetworkID   567        333
11  FName       GHI        333
12  LName       HIJ        333

Below is the Self Join query, I have written to achieve the expected result set

select distinct U1.value NetworkID
              , U2.value Users
from User U1
join User U2 on U2.owner = U1.owner and U2.name = 'UserID'
where U1.name = 'NetworkID' and U1.value = '567'

Expected Result

NetworkID   Users
567         123
567         234
567         345

The volume of the table is very large and it is taking very long time to fetch the results using this self join. Based on the DB restrictions, I cannot make changes to the existing schema (adding Indexes). I need suggestion on how this query can be rewritten effectively to achieve same result set.

Bruce
  • 109
  • 8
  • Are there indexes on the table now? – Dave Costa Apr 15 '20 at 17:01
  • @DaveCosta Unique Index on "ID" and NonUnique Index on "Name" – Bruce Apr 15 '20 at 17:07
  • 1
    I'm guessing that the values of `Name` are fairly evenly distributed throughout the table, so the index on that is not likely to be very helpful. (An index scan would need to read some blocks from the index and most blocks from the table to get all rows for a given value.) – Dave Costa Apr 15 '20 at 17:20
  • 1
    I agree. An index on `user(value, name, owner)` and another on `user(owner, name)` would help here. But anyway, key/value tables have many disadvantages. One of them is comparatively slow access to the data. That's just the way it is. If it has been decided to go for a key/value table, then there is probably a compelling reason for this, but that comes at a price. – Thorsten Kettner Apr 15 '20 at 17:25
  • Do you know how many records match the query before the DISTINCT is implied? It seems unlikely that, if you do have the indexes you've mentioned in other comments, and there are only 3 matching rows, that the query would be very slow. – Dave Costa Apr 15 '20 at 17:40

1 Answers1

0

Your query is fine:

select U1.value as NetworkID, U2.value Users
from User U1 join
     User U2
     on U2.owner = U1.owner and U2.name = 'UserID'
where U1.name = 'NetworkID' and U1.value = '567';

For this query, you want indexes on (owner, name) and (name, value, owner).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! I do have these indexes on the table. But based on the volume, the query is taking a long time to execute. Just wanted to check if there is any other effective approach in writing this query – Bruce Apr 15 '20 at 17:18
  • 1
    @Bruce, those aren't the same indexes that you said you have in your comment above – Dave Costa Apr 15 '20 at 17:21
  • @DaveCosta Sorry I missed to mention in above comment. I have a Unique index on (Value, name, owner). NonUnique on (Name) & (Owner), Unique on (ID) – Bruce Apr 15 '20 at 17:27
  • I suggest you edit your question to include the details on the indexes that are in place. I also suggest adding the execution plan for the query (e.g. see https://stackoverflow.com/questions/30070910/how-do-i-view-the-explain-plan-in-oracle-sql-developer) – Dave Costa Apr 15 '20 at 17:38
  • @Bruce . . . You need an index where `owner` is the first key (preferably with `name` as the second key). – Gordon Linoff Apr 15 '20 at 17:44
  • @GordonLinoff Thanks for the advise. Apart from adding Index, can we modify the above self join query? – Bruce Apr 16 '20 at 04:11
  • @Bruce . . . Yes, but an aggregation query is likely to have even worse performance. – Gordon Linoff Apr 16 '20 at 10:52
  • @DaveCosta Can we use a pivot to achieve the same result set? – Bruce Apr 16 '20 at 22:35
  • @Bruce . . . A `pivot` probably has comparable (or worse) performance than aggregation. – Gordon Linoff Apr 16 '20 at 22:37
  • I don't see how in this case an aggregation or pivot would avoid the self-join. If you were starting with an owner ID, you could easily select all the rows for that owner and aggregate or pivot them. But you're not. You're starting with a network ID. You have to locate that network ID, then locate the other rows with the same owner. Again .. post an execution plan and *maybe* we can get some ideas. – Dave Costa Apr 16 '20 at 22:43