Re: O9i: general index question

On 3 Apr., 16:05, Andreas Mosmann <mosm..._at_expires-30-04-2007.news- group.org> wrote:
> >> I want to stay with '0000000000' because Oracle does not store NULL into
> >> a column and so I had bad results using ... IS NULL
>
> Sorry: replace('column','index')

Oracle doesn't store NULL entries in indexes, true. But this is only to save memory and to gain speed and shouldn't make any difference to your select result at all. Maybe you mean bad performance, not bad results?

> >>> SELECT ... WHERE decode(CIDNEBENANLAGE,'0000000000',0,1) = 1
> The decode- solution will not be useful, because I create the queries
> dynamic and the user could have chosen a special CIDNEBENANLAGE. So it
> will only help if there is no one choosen. And I have one more case.

Well, you are right, if you create your query dynamically then you would create a select string containing either   WHERE decode(CIDNEBENANLAGE,'0000000000',0,1) = 1 or
  WHERE TBNEBENANLAGE.CID = '0123456789' and for the latter you would want another index on TBBAEUME.CIDNEBENANLAGE.
> btw: Can you tell me how to specify a driving table?

You cannot really. Oracle uses an optimizer which analyzes your query and tries to make the best of it. In my experience this works very good for more then 99% of all queries. So there is not much you can do. Normally it works automatically, because from your where clause the optimizer can evaluate which tables to access first. You can however use a hint: select /*+RULE*/ ... would switch off the optimizer and would use the tables in the order as your selects states them (I think it was last one first, but I am not sure). Usually however this results in a slower execution, but you can use it for testing anyhow. A compromise is select /*+ORDERED*/ ... which uses the optimizer, but tells it to have a look at your table order. (With hints you can also force usage of a particular index. Advice however is not to use them. We use them when it is obvious that the optimizer fails on a query, which is extremely rare.)

My advice: Try it with NULL instead of '0000000000' once more. This is what we all use, so it should work for you too. Received on Tue Apr 03 2007 - 09:41:41 CDT