This is a simple question. Suppose I have a massive table (5 million rows), and I have no option but to do a full table scan.
SELECT * FROM Table1
Is there any way at all to make my database return the results faster?
Background:
We have an application provided to us by a third party which was designed to be robust/flexible - there are very few, large tables in it's database. For example, one of the tables is the "Object" table, which stores all objects. Another table is the "Relationship" table, that captures all relationships between objects. It essentially allows you to store anything without changing its schema.
My task is to design a separate reporting application - an application that queries this database. It has to be live data. And because of the size of the tables, plus the complexity of the queries, there's performance issues.
I was wondering how I should go about handling the performance issues. I've created indexes after indexes, but the queries are still very complex, and at the end of the day, I still have to make multiple full table scans.
The good news is, I'm still at the design phase - so I'd like to hear as many suggestions as possible, in case I've missed something.
There are at least 9 ways to improve full table scans. But improving full table scans may not necessarily improve overall performance depending on exactly what is done with the data. For example, if 5 million rows are being sent to an application, the network will be the bottleneck and improving full table scans won't help.
Some of these methods are extreme, may only help in rare cases, and may only slightly improve performance. Think carefully before you apply esoteric tuning ideas. Make sure that full table scans are the bottleneck before you spend a lot of time doing something that may only improve performance by 1%.
SELECT /*+ PARALLEL */ * FROM Table1;
Parallelism can easily improve full table scan performance by an order of magnitude on many systems. But there are many limitations - sane configuration, large table size, Enterprise Edition only, etc.PCTFREE 0
to save the normal 10% of space that is saved for changes. Also, if the table has changed a lot in the past, ALTER TABLE MY_TABLE MOVE;
will re-organize it and potentially fill in some empty space. Shrinking the physical size of the table may make it faster to read.CREATE TABLESPACE ... EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;