Re: sql query (all about indexing)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 08 Apr 1999 02:30:56 GMT
Message-ID: <370e1276.5117238@192.86.155.100>

A copy of this was sent to silveys_at_my-dejanews.com (if that email address didn't require changing) On Thu, 08 Apr 1999 01:11:50 GMT, you wrote:

> tkyte_at_us.oracle.com wrote:
>> >> You are saying "if (a = b) or ( a = somevalue and b = somevalue )".
>
>> If the query was rewritten as:
>>
>> select * from t1, t2 where t1.a = t2.b
>> UNION ALL
>> select * from t1, t2 where t1.a = x and t2.b = x
>>
>> We could in fact use the three indexes.
>
>It's not clear to me why the previous where clause (quoted at the top there)
>and this compound select would be affected differently by indexing on the
>separate columns. Care to expound on that?
>

ok, i mixed a couple of things up here. If we go back up through the thread, it started with the query:

>SELECT DISTINCT count(*)
>FROM tb_EASY_HOUSE, tb_EASY_RE
>WHERE (
>(tb_EASY_HOUSE.STREET_CODE=tb_EASY_RE.STREET_CODE OR
> (tb_EASY_HOUSE.STREET_CODE IS NULL AND tb_EASY_RE.STREET_CODE IS NULL) )
>AND
>(tb_EASY_HOUSE.BLOCK=tb_EASY_RE.BLOCK OR
> (tb_EASY_HOUSE.BLOCK IS NULL AND tb_EASY_RE.BLOCK IS NULL) )
>AND
>(tb_EASY_HOUSE.MAIN_HSE=tb_EASY_RE.MAIN_HSE OR
> (tb_EASY_HOUSE.MAIN_HSE IS NULL AND tb_EASY_RE.MAIN_HSE IS NULL) )
>AND
>(tb_EASY_HOUSE.SUB_HSE=tb_EASY_RE.SUB_HSE OR
> (tb_EASY_HOUSE.SUB_HSE IS NULL AND tb_EASY_RE.SUB_HSE IS NULL) )
>);
>

somewhere along the line, i switched "x is null and y is null" into "a = somevalue and b = somevalue". I made a mistake doing that.

If I take the query:

SQL> create table t1 ( a int );
SQL> create table t2 ( b int );
SQL> create index t1idx on t1(a);
SQL> create index t2idx on t2(b);

SQL> set autotrace on explain

SQL>
SQL> select * from T1, T2 where t1.a = t2.b or ( t1.a = 5 and t2.b = 10 )   2 /

no rows selected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 CONCATENATION

   2    1     NESTED LOOPS
   3    2       INDEX (RANGE SCAN) OF 'T2IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'T1IDX' (NON-UNIQUE)
   5    1     NESTED LOOPS
   6    5       TABLE ACCESS (FULL) OF 'T2'
   7    5       INDEX (RANGE SCAN) OF 'T1IDX' (NON-UNIQUE)

I see the optimizer rewrote that as:

select * from t1, t2 where t1.a = t2.b
union -- (not union all but union)
select * from t1, t2 where t1.a = 5 and t2.b = 10

It does use the indexes. If i put the nulls back in tho:

SQL> select * from T1, T2 where t1.a = t2.b or ( t1.a is null and t2.b is null )   2 /

no rows selected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'T2'
   3    1     TABLE ACCESS (FULL) OF 'T1'

it cannot.

sorry for creating the confusion. I messed up by turning is null into = somevalue.

>Thanks,
>
>Scott
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Apr 07 1999 - 21:30:56 CDT