Rewrite the Oracle SQL Query

Question

I am using Oracle DB and wrote a query with self join to fetch some data. The table is very large and the query is running for a long time and there is no scope to add index. The value for the records is in SELECT and in WHERE condition. I was advised to modify the query by removing the value from WHERE condition, by resulting in same output. Can someone share your thoughts?

select a.xid, a.sIdentifier
from    tbllarge a
        inner join tbllarge b on a.xid = b.xid
where   a.sIdentifier ='A1'

Answer 1

This seems like a curious query:

select a.xid, a.sIdentifier
from tbllarge a join
     tbllarge b
     on a.xid = b.xid
where a.sIdentifier = 'A1'

I would interpret it as:

select a.*
from tbllarge a
where a.sIdentifier = 'A1' and
      exists (select 1 from tbllarge b where a.xid = b.xid);

For either query, you want two indexes: (sIdentifier, xid) and (xid). If xid is the primary key, then that index already exists.

Answer 2

Rewrite the query as

SELECT XID, SIDENTIFIER
  FROM TBLLARGE
  WHERE SIDENTIFIER = 'A1'

XID is the primary key on the table so the self-join just joined rows to themselves, which doesn't really do anything important. Skip the self-join and your query will run faster, although without an index on SIDENTIFIER I doubt it will run particularly quickly.