Slow select 2

[chrissie1]

I have a select on an oracle database

SELECT ZK_NR, ...
FROM NIC.PIMS_ZAKEN Z 
WHERE Z.ZK_NR = :ZK_NR

ZK_NR being the Primary key

this one takes 0.1 seconds

and then I have this

SELECT ZK_NR, ... 
FROM NIC.PIMS_ZAKEN 
WHERE OUDE_REF_NICC IS NULL 
AND REDEN_ANNULATIE IS NULL

wich takes approx. 2.5 seconds to return anything. Both selects return the same record. And I (or they) have an index on OUDE_REF_NICC not on REDEN_ANNULATIE.

NIC is the schema and PIMS_ZAKEN is a simple view.

Does anybody have any idea how to make this thing go faster?

BTW I made them put the index on OUDE_REF_NICC this morning in an attempt to make it faster. But it din't help.


Christiaan Baes
Belgium

"My new site" - Me

 

[jimbopalmer]

Searching for NULL is always a full table scan as NULL is never indexed.

Assuming OUDE_REF_NICC is normally a positive integer, if you make it -1 at creation and then assign it a positive value once it is known, that will index better than creating it as NULL.

I tried to remain child-like, all I acheived was childish.

 

[chrissie1]

Thanks, I will ask if they can do that for me.

Christiaan Baes
Belgium

"My new site" - Me

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state10c.htm#2063338

Bitmap indexes can contain null values.

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/ch6_acce.htm#7559

 

[chrissie1]

Cool, thanks I'll try that.

Christiaan Baes
Belgium

"My new site" - Me