I am running the following query on the tables which have 50lacs of records each and hence the time taken is very huge:
select table1.column1 , table1.column2, table2.column2 from table1, table2 where
and table1.column2 <> table2.column2
Please help me to optimise the above query.
Does that column1 and column2 are primary key columns. if not create then as primary key column.
When you create primary key that time index is also created by defauld with the same name of the primary key so you performance will not be less.
Otherwise your query looks like ok.
There must be column columns in both the tables on which you are joining both the table.
in where condition provide that only and if you provide other column which are not having index then you may suffer for performance.
50 Lacs is 5 million. I dont think, it is huge data. Even if you are running on a PC, this should work.
Unless you show us the plan of this query, its very difficult to say anything conclusive.
If you are using sql*plus to run query, run command "set autotrace traceonly explain" and then run your query.
This will show you the plan of your query, without actually running the query and post it here.
There are standard tuning request threads to help with this type of request:
[url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request[urll]
url When your query takes too long ... your query takes too long
Any meaningful feedback requires information about the current execution plan and where the time is currently spent.
You are getting all the data from the join of these two tables.
Generally an index will only help if this join is significantly selective - i.e. it actually significantly reduces the amount of data returned - and, if it is, that the optimizer recognises that the join is selective.
Otherwise you're looking at doing more work in parallel.
But, bottom line, more information required.