Printable View
Julian,
Read the question again -- it's about using an index for ORDER BY, not for WHERE. I stand by by comment on the NOT NULL contraint.
Kiewer
Oracle can choose to use an index (with certain limitations, particularly to do with character types and NLS) to perform an ORDER BY. It can negate the need for a sort operation
The index scan is definatly going to help on ordering many millions of rows.
You are right. The question *is* about ORDER BY. Then it is really up to if the column is nullable or not. I did test that.
9.2.0.2.0Quote:
Originally posted by pando
is that 8i or 9i Julian?
But the guy is right. With only order by (no where clause) the optimizer goes for FTS although I have an index. But when the column is a NOT NULL one, then it goes for the IFS.
I suppose this restriction would be lifted if the index were of BITMAP type, since they do store null values.
2 slimdaveQuote:
Originally posted by slimdave
Kiewer
Oracle can choose to use an index (with certain limitations, particularly to do with character types and NLS) to perform an ORDER BY. It can negate the need for a sort operation
The index scan is definatly going to help on ordering many millions of rows.
I thought we were speaking about specific query
But nevertheless I can't imagine how does index help to perform "order by". Please, give me an example.Quote:
select * from log_record order by tran_datetime desc;
Regarding to sort, index could be helpfull for count(*) queries with counting on unique column or for "group by" queries if unique index exists on group of columns which are included in "group by". But not more.
2 Julian
How did you get it for this type of query? I mean the query above.Quote:
But when the column is a NOT NULL one, then it goes for the IFS.
A BTree index is basically just a sorted list of values for one or more columns, with a pointer to rowid's in the table, right?
So if you wanted to "select my_column from my_table order by my_column", Oracle can just scan an index on my_column from start to finish (for an ascending index) and return the result, no sorting required.
For "select my_column from my_table order by my_column DESC", Oracle would reverse the direction of the scan.
Now if you wanted "select my_column, other_column from my_table order by my_column, other_column" and you have a composite index on (my_column,other_column) the same applies. If your index was on (other_column,my_column), Oracle could scan the index and then sort the result, but still wouldn't touch the table.
Now suppose that you try "select my_column, other_column from my_table order by my_column" but other_column is not indexed. Oracle could still scan the index on my_column and access the table based on the order of rowid's retrieved from the index. No sort would be required here either.
All of this applies only if every value requested in the query is in the index. For a btree index the NULL values of my_column would not be present, so if there is no NOT NULL constraint on my_column Oracle will assume that there are null values in the table, and that the ORDER BY cannot be satisfied on the index alone. Therefore a full table scan and a sort would be required. A bitmap index would contain any NULL values, so it could be used to satisfy the ORDER BY.
My comment on character date types and NLS referred to the NLS_SORT parameter. Since indexes store values in binary order, not linguistic order, a linguistic sort (AaBbCcDd ...) cannot be performed just by scanning the index, which would give you (ABCD ... abcd ...). You can overcome this by setting NLS_SORT=BINARY.
2 slimdave
2 julian
Yes, you are both right. Sorry.
For me it just still a question whether "FTS+sort" always more expensive then "IFS+access by rowid" for querieing more then only indexed columns (of sure, supposed no "where" clause).
That's a difficult question to answer for sure, but I'd bet that the index access is faster in 99.9% of cases. The comparative efficiency would depend on many factors, but I would expect that the benefits of the index method would increase as the number of rows/blocks increases, and as the clustering factor of the index decreases.
2 slimdave
Ok. I can give you 0.1% of cases :) I mean that "gets" of 1289 block alwase takes longer then "gets" of 35 blocks. Sure, it also depends on sort_area_size which I have big enough for doing everything in memory.
PHP Code:SQL> create table t as select * from all_objects;
Tabelle wurde angelegt.
SQL> set autot trace
SQL> select * from t order by object_id;
1532 Zeilen ausgewõhlt.
Ausf³hrungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistiken
----------------------------------------------------------
0 recursive calls
12 db block gets
23 consistent gets
19 physical reads
0 redo size
113511 bytes sent via SQL*Net to client
11743 bytes received via SQL*Net from client
104 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1532 rows processed
SQL> create unique index it on t(object_id);
Index wurde angelegt.
SQL> set autot off
SQL> analyze table t compute statistics;
Tabelle wurde analysiert.
SQL> analyze index it compute statistics;
Index wurde analysiert.
SQL> alter session set optimizer_index_cost_adj = 1;
Sitzung wurde geõndert.
SQL> set autot trace
SQL> select * from t order by object_id;
1532 Zeilen ausgewõhlt.
Ausf³hrungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1532 Bytes=1
31752)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=12 Card=1532 By
tes=131752)
2 1 INDEX (FULL SCAN) OF 'IT' (UNIQUE) (Cost=4 Card=1532)
Statistiken
----------------------------------------------------------
5 recursive calls
0 db block gets
1289 consistent gets
0 physical reads
0 redo size
113511 bytes sent via SQL*Net to client
11743 bytes received via SQL*Net from client
104 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1532 rows processed
SQL>
I'll agree that the block gets take more work, but the payoff comes from eliminating a large (potentially disk-based) sort, and that should compensate for the gets.
Could you run timings for your queries -- I see similar patterns in the gets for your example, but faster execution time on the index access.