Hi,
To improve your performance there are 2 thinks you may do:
- Access to your data via an index and not a full table scan
- Avoid to re-order your data each time.
To avoid that oracle reorders you data each time you can create one index for each field which may be used for ordering. The problem is that Oracle can ONLY use an index for ordering when the field has the NOT NULL constraint. So in your table add to every column which doesnt containt any NULLs value the NOT NULL constraint:
ALTER TABLE table_name MODIFIY(
id NUMBER NOT NULL,
text VARCHAR2(100) NOT NULL
...
)
Then you can create a index for each of your columns and try the following query
SELECT t3.* FROM (
SELECT
t2.*,
ROWNUM rn
FROM (
SELECT /*+ INDEX(t1 index_name) */
id,
text
FROM table_name t1
ORDER BY unique_id
)t2
WHERE ROWNUM <=10010
)t3
WHERE t3.rn >10000
You dont always need the hint /*+ INDEX(t1 index_name) */ but I noticed that if it is not an unique index it doesnt always use it by default. In you explain plan (SET AUTOTRACE ON) you should not see any full table scans anymore.
This is only realy performant for NOT NULL columns. If you want that it also works for columns having nulls you need something like:
SELECT t3.* FROM (
SELECT
t2.*,
ROWNUM rn
FROM (
SELECT /*+ INDEX(t1 index_name) */
id,
text
FROM table_name t1
WHERE id IS NOT NULL
ORDER BY id
)t2
WHERE ROWNUM <=10010
)t3
WHERE t3.rn >10000
but in this case you would only get the rows with not null values.
Hope that helps
Mike
] 
Blog
Search
Help
Members
Register
Login
Home
