Karen,
There is a little more going on here.
First you said you are using a view, I assume that view is joining some tables
together. This is going to effect how indexes the optomizer selects to use.
Have you reloaded your database reciently? There are some system tables that
keep track of what it thinks are good and bad indexes and then the query
optomizer uses that infor to decide what indexes to use. This info only gets
updated when you do a pack or reload.
The index on trantype is probably not a very good index to begin with because
I bet that field only has a few values in it which will create a lot of
entries on the multiple occurance entries for the index, thus makeing it a bad
index to start with.
In this case the query optomizer is making different choices with the 2
querise. Try setting MANOPT ON and play around with the order of the where
clause items and the table order of the view. MANOPT ON turns to the R:base
query optomizer. Don't forget to turn it back off.
Troy Sosamon
>===== Original Message From [EMAIL PROTECTED] =====
>Hello!
>
>I have a view that will hold a lot of data. I wanted to
>search it 2 ways for these where clauses:
> repdate between .... and trantype <> 'fr'
> repdate between .... and trantype = 'fr'
>
>The first search is very fast. The second search I had to
>reboot RBase after it was still chugging after 20 minutes.
>
>For the heck of it I changed it to
> repdate between .... and trantype = ('fr')
>
>Now it's as fast as the first search! Rbase won't use indexes
>when the where clause is "<> 'fr'", so I had to do the same for
>the "= 'fr'". Putting it in parenthesis did the job!
>
>Put this on your list of "gotta remember to try that the next
>time I have a slow view"....
>
>
>Karen
>
>
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: INTRO rbase-l
>================================================
>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: UNSUBSCRIBE rbase-l
>================================================
>TO SEARCH ARCHIVES:
>http://www.mail-archive.com/rbase-l%40sonetmail.com/
Troy Sosamon
Denver Co
[EMAIL PROTECTED]
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
