Increase SELECT Query performance without using indexing
I have a SELECT query which is fetching data from approx 4 tables with data in range of 90,000 to 1lac records, it is a long query with multiple inner joins between tables.The problem is taking damn long time to fetch the data.Now the problem is in 2 parts:
1---> Out of 4, 3 tables used are not indexed there is no primary key, i know by indexing performance will increase but i cannot index the tables esp. cant add a new column with unique values in each table of such huge volumes of data so to make it primary key.I tried using non-unique(coz i cant use unique),non-clustered indexing which was not of much help.
Now i need some resolution to increase the query performance without indexing in some other way.
2.---> Other problem is, after i get data after a looooong patience of atleast 30 to 45 mins through running query in TOAD or SQL Developer.It is taking hell longer to get its record count or export data in excel.Its already been 4 hrs now since i have put in for exporting into excel but still i could not get it, i m sure that the database or system has not hung till now becoz the displayed count of no. of rows fetched is increasing with time.It is now 1500 rows after 4 hrs and expect the total records to be between 25000 to 30000 approx.Please tell me if there is any way to export data in excel or other format faster.
Will be reall grateful for any assistance provided on above 2 queries.
Click Here to Expand Forum to Full Width