Re: Help on HINTS

On Wed, 19 Dec 2001 23:27:13 +0100, Sybrand Bakker <oradba_at_sybrandb.demon.nl> wrote:

>On 18 Dec 2001 08:19:36 -0800, millhill_at_iname.com (millhill) wrote:
>
>>Hi,
>>
>>I have a table with approx 50 million rows.
>>One of my developers queries access this table with a set of joins and
>>where clauses. I know that this select-query only gathers rows where a
>>column is not NULL and this is approx just 2% of the total rows. I
>>have an index on this column.
>>
>>Here's an example:
>>
>>t1 contains 50 million rows, t2 and t3 are smaller tables.
>>
>>select *
>>from t1, t2, t3
>>where t1.column1 is not null
>>and t1.id = t2.id
>>and t2.id = t3.id
>>and t1.something = something
>>and t1.something2 = something2
>>
>>t1.column1 is not null for approx 2% of all rows in t1 and therefore I
>>would like to let oracle use the first where-clause (t1.column1 is not
>>null) and only work with this sub-set for the rest of the clauses and
>>joins. Is this possible?
>>
>>Thanx,
>>-Chris
>
>is not null won't use and index. You need to use
> >= <some arbritary minimum value for that column>
>
>Hth
>
>Sybrand Bakker, Senior Oracle DBA
>
>To reply remove -verwijderdit from my e-mail address

I just tried this:

CREATE TABLE A (A NUMBER);
CREATE INDEX AA ON A(A); I then did an explain plan on

SELECT * FROM A WHERE A IS NOT NULL It did a full table scan.

But when I added a hint:

SELECT /*+ INDEX (A AA) */ * FROM A WHERE A IS NOT NULL It did use the index.

Could not then Chris use /*+ INDEX (T1 index_name) */?

Brian Received on Thu Dec 20 2001 - 07:54:14 CST