| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: WHERE NOT ...
A copy of this was sent to Pablo Colmenero <pabloc_at_intec.es>
(if that email address didn't require changing)
On Fri, 03 Jul 1998 10:20:50 +0200, you wrote:
>Maybe this quesion will seem trivial for some database experts, but I
>cannot find an easy and clean solution, so I ask it.
>
>Suppose you have a table T with 3 fields A, B and C and you've got an
>index to all these fields.
>I have noticed that when you make some query like
>
>SELECT A,B,C
>FROM T
>WHERE A ="" AND
> B >"first"
>ORDER BY B,C
>
>It runs quickly, because of the index. But if in the WHERE line you
>write A<>"" instead of A="", the negated condition makes the query very
>slow.
>Right now, I run this query over an Access 97 database, but in the
>future it will run in Oracle 7.
>
I assume, by your description above, that you have an index on (A,B) (or maybe just A?? -- not enough information to be sure)...
So, where A= constant and B > constant runs 'fast' because if you have an index on (A,B) it can index range scan to find all of the rows that meet the query criteria and then table access by rowid (fast) to get the value for C. If you have an index on just A, its still pretty fast because it can find all of the A's quickly and then table access by rowid to see if B is the right value..
When you run where A <> constant and B > constant, the indexes on (A,B) or (A) cannot be used. Unless there exists an index on B, a full table scan will be performed to answer the question.
What is the indexing scheme on this table? Have you looked at the query plans for the queries to see what access paths are being taken?
>The question is: is there an easy way to write this query (maybe
>changing the index, or something like that) in order to make it fast,
>even in Access?
>If not, is there a solution in Oracle 7?
>
>Thank you.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jul 03 1998 - 10:02:30 CDT
 |
 |