| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help on ORDER BY
A copy of this was sent to "Terry Somekh" <terry_at_memco.co.il>
(if that email address didn't require changing)
On Mon, 14 Dec 1998 09:22:48 +0200, you wrote:
>Hello,
>
> I have a table with a unique index defined on the column 'entryid' and a
>non unique index defined on the column 'timstamp'.
>
>If I do
> select * from table order by entryid
>it works very fast.
>
>If I do
> select * from table order by timstamp
>it takes about 4 minutes to get the first record on a 100,000 records table.
>
>Any ideas ?
>
It is probably something like the following example:
SQL> create table t ( entryid int not null, timestamp date ); Table created.
SQL> create unique index t_idx1 on t(entryid); Index created.
SQL> create index t_idx2 on t(timestamp); Index created.
SQL> set autotrace on explain;
SQL> select * from t order by entryid;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' 2 1 INDEX (FULL SCAN) OF 'T_IDX1' (UNIQUE) SQL> select * from t order by timestamp; no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
See, the select ORDER BY on entryid uses an index and would tend to get the
first rows for the query almost immediately. The order by on timestamp does not
use the index and would have to read and then sort the entire table (100,000
rows in your case) before giving you the first row.
why?
the entryid column is made NOT NULL. I'm going to assume that you have made entryid your primary key and primary keys are always NOT NULL. I'm also going to assume you did not specify any rules on the NULLNESS of timestamp and hence timestamp is a column that allows NULLS.
NULLS are never indexed. that means, if a query against an indexed column would allow it to return a NULL value -- that index cannot be used as it would miss all of the rows that returned NULL.
How to fix? 2 (of possibly more) ways if you don't care about rows with NULL timestamps:
1.) use a where clause that precludes NULL from being returned. Here, we use the smallest date we can make and use it in the predicate. Now, if timestamp was NULL, it won't be returned. This uses the index and will be fast for the first rows.
1* select * from t where timestamp > to_date(1,'j') order by timestamp SQL> / no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' 2 1 INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) 2.) if timestamp is really NOT NULL, then:
SQL> alter table t modify timestamp NOT NULL; Table altered.
SQL> select * from t order by timestamp; no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' 2 1 INDEX (FULL SCAN) OF 'T_IDX2' (NON-UNIQUE) that will allow for the use of the index since all rows in T must be in the index now.
>
>Terry
>
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 14 1998 - 07:21:25 CST
 |
 |