Home » SQL & PL/SQL » SQL & PL/SQL » Index and null values (merged 2) (Oracle 11.2.0.3)
Index and null values (merged 2) [message #595744] Fri, 13 September 2013 03:50 Go to next message
mats-larsson@iname.com
Messages: 6
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 #595754 is a reply to message #595744] Fri, 13 September 2013 04:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2134
Registered: May 2013
Location: World Wide on the Web
Senior Member
Go through this link Index selectivity by Jonathan Lewis
Re: Index and null values [message #595758 is a reply to message #595744] Fri, 13 September 2013 05:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1998
Registered: January 2010
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 Go to previous messageGo to next message
John Watson
Messages: 4500
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.
Index and null values [message #595776 is a reply to message #595744] Fri, 13 September 2013 08:59 Go to previous messageGo to next message
mats-larsson@iname.com
Messages: 6
Registered: September 2013
Location: Stockholm
Junior Member
Hi.
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 three 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 column three? Or will it be on just column one since the second column is null?
I have done some testing and 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 drawbacks with an index like this?
I could of course create two seperate indexes if it works better.

/Mats
Re: Index and null values [message #595778 is a reply to message #595776] Fri, 13 September 2013 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 22703
Registered: January 2009
Senior Member
> But is there a way to see in detail how the index is used?
I don't understand this question.

dissecting SQL_TRACE file will reveal what occurs behind the scenes.
Re: Index and null values [message #595783 is a reply to message #595778] Fri, 13 September 2013 09:13 Go to previous messageGo to next message
mats-larsson@iname.com
Messages: 6
Registered: September 2013
Location: Stockholm
Junior Member
I want to see what range of index is used. As I wrote in the post : "Will the range be on colum one, column two(being null) and column three? Or will it be on just column one since the second column is null?"
Re: Index and null values [message #595785 is a reply to message #595783] Fri, 13 September 2013 09:29 Go to previous messageGo to next message
cookiemonster
Messages: 10912
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 #595786 is a reply to message #595785] Fri, 13 September 2013 09:37 Go to previous messageGo to next message
mats-larsson@iname.com
Messages: 6
Registered: September 2013
Location: Stockholm
Junior Member
Sorry my mistake
Re: Index and null values [message #595798 is a reply to message #595786] Fri, 13 September 2013 13:38 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2134
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 Go to previous messageGo to next message
mats-larsson@iname.com
Messages: 6
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 #596209 is a reply to message #595754] Wed, 18 September 2013 16:02 Go to previous messageGo to next message
mats-larsson@iname.com
Messages: 6
Registered: September 2013
Location: Stockholm
Junior Member
Hi.
The "Predicate Information" would have been nice to see. How do I get it?

/Mats
Re: Index and null values [message #596212 is a reply to message #596200] Wed, 18 September 2013 16:54 Go to previous message
Lalit Kumar B
Messages: 2134
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
Previous Topic: Oracle Join on Tables
Next Topic: Alternative to NOT IN
Goto Forum:
  


Current Time: Wed Aug 27 08:04:42 CDT 2014

Total time taken to generate the page: 0.09466 seconds