Oracle SQL performance low due to a lot of data -

Oracle SQL performance low due to a lot of data -

one of our table has ~500000 items , because of queries (with joins) works slow:

select table_with_a_lot_of_data t left bring together table1 t1 on t1.id=t.t1_id left bring together table2 t2 on t2.id=t1.t2_id <some complicated clause subqueries , on>;

what's more, business logic of our application complex , where clauses complicated. question is: what ways increment performance of sql queries search info through tables lot of data? aren't db specialists , read can utilize partitions and/or materialized views. there other options? 1 best?

there not 1 reply question this. if there single magic secret database performance utilize it. also, it's worth noting table mentioned not particularly large, have worked on databases tables several orders of magnitude larger, still within scales can handled designed database. (the point of saying shouldn't conclude there much info able performance relational database).

the obvious avenue downwards whether have indexes need. first of think whether queries performing intended homecoming little number of records. if targeting little proportion of overall table building indexes on columns filter can effective. think of using indexes in book, if have search whole book each word looking (like total table scans) slow. on other hand if plan homecoming big proportion of table e.g. >~15% indexes may not solution (at point becomes more efficient @ every record rather going , forth index of time.

also, don't go crazy indexes, adding them add together overhead inserts / updates.

this 1 consideration, easiest potential gain if appropriate scenario. broad question, not effort cover of factors improve performance.

sql oracle oracle11g database-performance