( select rownum rnum, a.* from (your_query) a where rownum <= :M )
where rnum >= :N;
select * from (
select /*+ first_rows(25) */ your_columns, row_number() over (order by something unique) rn from your_tables )
where rn between :n and :m
order by rn;
Table scans that occur unintentionally are a major source of poorly performing SQL. Causes include:
a. Missing Index
b. Using “!=“, “<>” or NOT
i. Use inclusive range conditions or IN lists
c. Looking for values that are NULL
i. Use NOT NULL values with a default value
d. Using functions on indexed columns – Function Based Indexes.
Use “functional” indexes in Oracle8i and above
One of the most fundamental SQL tuning problems is the “accidental” table scan. Accidental table scans usually occur when the SQL programmer tries to perform a search on an indexed column that can’t be supported by an index. This can occur when:
Using != (not equal to). Even if the not equals condition satisfies only a small number of rows, Oracle will not use an index to satisfy such a condition. Often, you can re-code these queries using > or IN conditions, which can be supported by index lookups.
Searching for NULLS. Oracle won’t use an index to find null values, since null values are not usually stored in an index (the exception is a concatenated index entry where only some of the values are NULL). If you’re planning to search for values which are logically missing, consider changing the column to NOT NULL with a DEFAULT clause. For instance, you could set a default value of “UNKNOWN” and use the index to find these values.
Using functions on indexed columns. Any function or operation on an indexed column will prevent Oracle from using an index on that column. For instance, Oracle can’t use an index to find SUBSTR(SURNAME,1,4)=’PWAR’. Instead of manipulating the column, try to manipulate the search condition. In the previous example, a better formulation would be SURNAME LIKE ‘PWAR%’. In Oracle8i you can create functional indexes, which are indexes created on functions, providing that the function always returns the same result when provided with the same inputs. This allows you, for instance, to create an index on UPPER(surname).
Every application big or small at some point in time will have performance issues in its life spam. Some of the common complaints are
1) Application is very slow
2) Reports taking hours to generate
3) Schedule jobs taking way too long to complete
4) Tired of throwing money @ Oracle consulting.
By taking some pro-active approach and setting the right expectation some of the issues can be handled. There is no single Oracle init parameter which will fix all the problems.
Always design and develop with performance in mind
All too often, SQL tuning is performed on an application designed and developed with virtually no consideration given to performance requirements. SQL tuning is often the best option for tuning such applications, but it is both more efficient and effective to design an application with performance in mind.
Producing good performance by design requires that the following activities occur within the development life-cycle:
1) Identify performance targets
2) Measure performance as early as possible.
3) Focus on key areas of the application
4) Prototype critical portions of the application
5) Consider de-normalization where ever applicable.
Establish a tuning and development environment
1) A significant portion of SQL that performs poorly in production was originally crafted against empty or nearly empty tables.
2) Make sure you establish a reasonable sub-set of production data that is used during development and tuning of SQL
3) Make sure your developers understand EXPLAIN PLAN and tkprof, or equip them with commercial tuning tools.
Keep it Simple.
1) Balance/Reduce the workload (If Possible).
2) Pre – Generate reports overnight when there is less load on the system.
3) Parallelize the queries – Use the full potential of the system.
Execute Smart Queries
1) Index wisely - Use of Indexes
2) Eliminate Full table scans.
3) Use concatenated indexes where appropriate
4) Consider advanced indexing options
a. Hash Clusters
b. Bit mapped indexes
c. Index only tables
