RE: How to calculate cardinality for inequality (CASE indeed seems better than decode)
Date: Thu, 11 Jul 2013 18:27:37 -0400
Message-ID: <028101ce7e85$d9e7f290$8db7d7b0$_at_rsiz.com>
I only ran one trial of each, so your mileage may vary, but it appears CASE is a little better for the insert when all the values end up null and about 33% better when all the values are not null:
13:12:03 SQL> alter table junk23 add (id1_lt_id2_case number as (case when id1<id2 then id1-id2 when id1>=id2 then null end));
Table altered.
Elapsed: 00:00:00.03
13:12:44 SQL> create index id1_lt_id2_case_n1 on junk23(id1_lt_id2_case);
Index created.
Elapsed: 00:00:00.01
13:13:20 SQL> insert into junk23(id1,id2) 13:13:58 2 select a.obj#+1,a.obj# from 13:13:58 3 sys.obj$ a, sys.obj$ b 13:13:58 4 where rownum < 10000001;
10000000 rows created.
Elapsed: 00:00:16.05
13:14:19 SQL> insert into junk23(id1,id2) values (1,2);
1 row created.
Elapsed: 00:00:00.00
13:15:17 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
13:15:23 SQL> set autotrace on;
13:15:30 SQL> select id1,id2,id1_lt_id2_case from junk23 where id1_lt_id2_case < 0
ID1 ID2 ID1_LT_ID2_CASE
---------- ---------- ---------------
1 2 -1
Elapsed: 00:00:00.01
Execution Plan
Plan hash value: 1377434902
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ------------------ | 0 | SELECT STATEMENT | | 1 | 8 | 0
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| JUNK23 | 1 | 8 | 0
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ID1_LT_ID2_CASE_N1 | 1 | | 0
(0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("ID1_LT_ID2_CASE"<0)
The similar test with:
12:18:11 SQL> alter table junk23 add (id1_lt_id2_decode number as (decode(least(id1-id2,0),0,null,id1-id2)));
Table altered.
12:19:21 SQL> create index id1_lt_id2_decode_n1 on junk23(id1_lt_id2_decode);
Index created.
Took 17.02 seconds. Again, only one trial so I'm not going to bet the ranch on 16.05 seconds versus 17.02 seconds on 10 million rows.
However, when I inserted 10 million rows that did require the index entry, CASE took only Elapsed: 00:02:16.96, while the DECODE took 03:12.37. This was also only one trial, and I'm a bit surprised, not that CASE won, but that insert into the table (essentially alone, skipping the index entry for all those nulls) was less than 20 seconds but using the index required more than 120 seconds extra. I may just have to trace things to see where all the time went.
In with either CASE or DECODE the query bringing back a single row from the sparse index range scan is too fast to reliably time with sqlplus elapsed time, but CASE was the winner .01 to .03 seconds on my single trial. I'd say that is a pretty spanky demonstration of the value of sparse indexes either way.
mwf
-----Original Message-----
From: Christopher.Taylor2_at_parallon.net [mailto:Christopher.Taylor2_at_parallon.net]
Sent: Thursday, July 11, 2013 11:44 AM
To: mwf_at_rsiz.com; IIotzov_at_newsamerica.com; Martin.Klier_at_klug-is.de; oracle-l_at_freelists.org
Subject: RE: How to calculate cardinality for inequality in WHERE
This is a really interesting discussion and I'm curious if you can use a CASE instead of DECODE in the virtual column you created? (DECODE seems more expensive than CASE if I remember correctly in normal SQL)
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Thursday, July 11, 2013 10:37 AM
To: IIotzov_at_newsamerica.com; Martin.Klier_at_klug-is.de; oracle-l_at_freelists.org
Subject: RE: How to calculate cardinality for inequality in WHERE
Iotzov: That is a slick idea.
Martin: If it were worthwhile in your situation to be extremely fast at finding just the relevant rows, then:
alter table junk23 add (id1_lt_id2 number as (decode(id1-id2,-1,id1-id2,0,null,null)));
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 12 2013 - 00:27:37 CEST