| Index usage in order by clause on nullable column [message #490944] |
Fri, 28 January 2011 04:19  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hi
I came across situation where a Nullable column is not using index for 'order by' clause.
I added Not Null condition in the 'where' condition but it wasn't useful.
I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'
So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting
here are the steps
Initially I kept the column Nullable
SQL> create sequence s5;
Sequence created.
SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001;
Table created.
SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';
SQL> create index i5 on t5(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T5',cascade=>true);
PL/SQL procedure successfully completed.
exit
SQL> alter session set events '10046 trace name context forever, level 12';
select *
from
t5 where id is not null order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.00 0 16 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 49.49 49.72
********************************************************************************
select /*+ index(t i5) */ *
from
t5 t where id is not null order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 69 0.00 0.00
SQL*Net message from client 69 22.89 28.04
Now I modified the 'id' column to Not Null
SQL> alter table t5 modify id not null;
SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';
COLUMN_NAME N
------------------------------ -
ID N
OWNER Y
OBJECT_NAME Y
SUBOBJECT_NAME Y
OBJECT_ID Y
DATA_OBJECT_ID Y
OBJECT_TYPE Y
CREATED Y
LAST_DDL_TIME Y
TIMESTAMP Y
STATUS Y
TEMPORARY Y
GENERATED Y
SECONDARY Y
14 rows selected.
select *
from
t5 order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 29 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 45 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 37.74 37.91
********************************************************************************
select /*+ index(t i5) */ *
from
t5 t order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 8.28 8.45
select id
from
t5 order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 6 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 6 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us)
1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 1.88 1.89
Questions are
1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?
Thanks and Regards,
OraKaran
|
|
|
|
|
|
| Re: Index usage in order by clause on nullable column [message #490965 is a reply to message #490950] |
Fri, 28 January 2011 05:16  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Michel
I understood the intitial part of the reply
I am re-reading the following line
Quote:
In any case, Oracle will trigger an index operation just to satisfy an "order by" clause
The Metalink note 418708.1 has covered some cases but they have forced optimizer mode for index usage in the cases and mainly the topic focuses on nullable column
Could you please suggest on my 4th question?
Quote:
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?
Regards,
OraKaran
|
|
|
|
|
|
| Re: Index usage in order by clause on nullable column [message #491032 is a reply to message #490944] |
Fri, 28 January 2011 09:05 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
When you are issuing a "select * ...", Oracle knows that it will have to go to the table to retrieve other column(s), the query cannot be satisfied by the index alone. Next it has to evaluate whether it should use the Index to identify the candidate rows before going to the table OR whether it should just go to the table directly (i.e. do a FullTableScan -- which appears as TABLE FULL SCAN).
If you look at the tkprof output, the 1000 rows FullTableScan was satified by reading only 16 blocks. Even assuming 8block reads, it is only 2 read calls to the OS. On the other hand, when you added the Index Hint, you forced Oracle to read the Index and then go to the table for each row. This total effort was 150 blocks to be read. Therefore, the Index was a poor choice to execute the "select *..." query and the optimizer was correct to not use it when left to make a decision on it's own.
On to your questions :
1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
The FullTableScan was the faster and better way to retrieve the data you required, even if it had to sort the 1000 rows. Remember that the order by was the last operation -- it had to first retrieve the 1000 rows from the table block.
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
The query where Oracle did an IndexFastFullScan was only for the 'id' column. Oracle did not have to go to the table blocks. This query could be satisfied by the Index alone. The FastFullScan of the Index is like the MultiBlock Read (FullTableScan) of a table -- with as few I/O calls as necessary. It had to read 6 blocks -- which wa satisfied with one single I/O call. It is easier for Oracle to sort the rows after the read call.
Had Oracle chosen to do an Index Full Scan (which is how it could have retrieved the 'id' values in an ordered manner), it would have had to read the Index Blocks in SingleBlock Read Calls. That would have been slower.
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
No it is not necessary. If Oracle finds that it can use an Index Full Scan, it retrieves the values ordered. However, a query may have more than one ORDER BY column -- and not all of them may be indexed or in the same index. It is not necesssary that the ORDER BY column be indexed. (e.g. think of the GROUP BY ... ORDER BY where ordering is done on values that are grouped after they are fetched from the table).
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?
An ORDER BY decision is driven by how you want to present the data. Don't link the ORDER BY with the Index (although Oracle can use an Index to retrieve the ordered rows (see my previous answer)).
Hemant K Chitale
[Updated on: Fri, 28 January 2011 09:14] by Moderator Report message to a moderator |
|
|
|
| Re: Index usage in order by clause on nullable column [message #491070 is a reply to message #491032] |
Fri, 28 January 2011 19:12 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try replacing
with
WHERE id >= -9999999999999999
or
for VARCHAR2 columns. You may also need to add an index hint, but providing you don't have any other WHERE clause, you should be able to force the index range scan (even if it is slower than a FULL TABLE SCAN).
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
