| Index and null values (merged 2) [message #595744] |
Fri, 13 September 2013 03:50  |
 |
mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm
|
Junior Member |
|
|
I have a table with a non-unique index consisting of three columns. The first column is not null while the remaining two are nullable. Queries using this index will chiefly be made in two ways.
1. Column one and two having values. Column three is null.
2. Column one and thre having values. Column two is null.
In both cases I expect range scan will be used since it's non-unique. In the first case the scan will be on values in column one and two.
But what happens in case two. Will the range be on colum one, column two(being null) and cxolumn three? Or will it be on just column one since the second column is null?
I have done some testing. I can see , using EXPLAN PLAN, that range scan is used in both cases. But is there a way to see in detail how the index is used?
Is there any other drawbacks with an index like this?
I could ofcourse create two seperate indexes.
/Mats
|
|
|
|
|
|
| Re: Index and null values [message #595758 is a reply to message #595744] |
Fri, 13 September 2013 05:01  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Oracle index indexes rows where at least one indexed column is not null. Since in your case one column is defined as NOT NULL, your "non-unique index consisting of three columns" will index all rows, so NULL in column 2 will be indexed. The rest depends on WHERE clause, select list and stats.
SY.
|
|
|
|
| Re: Index and null values [message #595766 is a reply to message #595744] |
Fri, 13 September 2013 06:30 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
mats-larsson@iname.com wrote on Fri, 13 September 2013 09:50
But what happens in case two. Will the range be on colum one, column two(being null) and cxolumn three? Or will it be on just column one since the second column is null?
/Mats
I would expect that you would see the same plan (with the same hash value) used both times so far as the plan operations are concerned, but a difference in the predicate information. In your first case, there will be an access step only, but the second will have an access followed by a filter.
|
|
|
|
|
|
|
|
|
|
| Re: Index and null values [message #595785 is a reply to message #595783] |
Fri, 13 September 2013 09:29 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Please don't create multiple copies of the same topic, it just makes work for us moderators.
I've merged the two. I suggest you have a read through the answers already given.
|
|
|
|
|
|
| Re: Index and null values [message #595798 is a reply to message #595786] |
Fri, 13 September 2013 13:38 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
Why don't you check out the explain plan, as suggested by John, both plans should remain same, except the predicate filters applied. The best is to generate a trace file and see what's actually going on in DB. And do not get confused by the COST which you see in EXPLAIN PLAN.
|
|
|
|
| Re: Index and null values [message #596200 is a reply to message #595798] |
Wed, 18 September 2013 15:31 |
|
mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm
|
Junior Member |
|
|
Hi.
First a reminder. A non-unique index consisting of three columns. The first is not null and the remaining two are nullable.
Two cases of reading.
1. Column one and two having values. Column three is null.
2. Column one and three having values. Column two is null.
Index-range scan is expected. I want to know which columns are used in the index-range scan.
I checked explain plan (using Toad) and sql-trace. The start and stop values used in index range-scan are unfortunately not shown. But they show that the plans are equal in the two cases. This I interpret as the range for case 2 will be on colum one, column two(being null) and column three. So the narrowest possible index-range seems to be used. And column two is not even mentioned in the where-clause.
Am I on the right track?
Here is case 2:
SELECT ID_SLUTSKATT,
to_char(id_pers) id_pers,
ar_tax,
status,
ursprung,
to_number(to_char(dat_besk_from,'yyyymmdd')) dat_besk_from,
to_number(to_char(dat_besk_tom,'yyyymmdd')) dat_besk_tom
FROM sk_db01.t_slutskatt
WHERE id_pers = 197401010041 -- column one in index
AND dat_besk_from = to_date(20120101,'yyyymmdd') -- column three in index
ORDER BY ar_tax, dat_besk_from, id_slutskatt
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 1 0.00 0.00 0 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 5 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 34
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=5 pr=0 pw=0 time=116 us cost=2 size=25 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID T_SLUTSKATT (cr=5 pr=0 pw=0 time=76 us cost=1 size=25 card=1)
1 1 1 INDEX RANGE SCAN I_SLUTSKATT_1 (cr=3 pr=0 pw=0 time=53 us cost=1 size=0 card=1)(object id 102296)
********************************************************************************
SQL ID: 8sxw8w85tu46d Plan Hash: 0
begin dbms_output.get_line(line => :line, status => :status); end;
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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 34
|
|
|
|
|
|
| Re: Index and null values [message #596212 is a reply to message #596200] |
Wed, 18 September 2013 16:54 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mats-larsson@iname.com wrote on Thu, 19 September 2013 02:01
1. Column one and two having values. Column three is null.
2. Column one and three having values. Column two is null.
Index-range scan is expected. I want to know which columns are used in the index-range scan.
And column two is not even mentioned in the where-clause.
As per my understanding from your posts -
In a nut-shell, these are the important things -
1. Column 1 - id_pers
2. Column 2 - id_slutskatt
3. Column 3 - dat_besk_from
A non-unique composite index on all the three columns where column 1 is defined "NOT NULL". Column 2 and 3 are NULLABLE. However, column 3 has values, but, Column 2 is NULL.
1. Index range scan because you have specified a leading indexed column (id_peers) in the predicate.
2. Column 1 is NOT NULL, hence, Oracle will index all the rows and thus the column 2 having NULL will be indexed.
3. The cost of accessing the table to get the table cardinality depends on the cardinality estimate of the index. In the plan, both the table cardinality and index cardinality are same(=1) and thus a good option for optimizer. From 11g, the distinct_keys are used quite differently when compared to 10g.
4. You have specified an ORDER BY, which could bring a lot of differences. Cost of sorting the keys could be balanced by indexing that column itself. So, you see I_SLUTSKATT_1 in the plan.
Regards,
Lalit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
