Re: Help on HINTS

I believe a bitmap index can be used in conjunction with IS NULL / IS NOT NULL. This would work if the table is used for reporting / data warehousing rather than transactions. Otherwise, updating the index for new/changed rows requires more overhead than with a regular index.

In some cases, I've found it useful to trim down one of the joined tables in advance using subselects:

select x from (select * from t1 where c1 is not null) t1, t2, t3 ...

However, I've no idea about how well this scales to 50 million rows.

John

On Thu, 20 Dec 2001 13:54:14 GMT,
SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote:

>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
>

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/