Re: Too many WHERE conditions?
- From: "Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom>
- Date: Fri, 31 Mar 2006 16:33:56 +0100
The SQL statement has
about 10,000 characters, the vast majority of which is 24 repetitive
blocks of SQL, forming the expression in an "IN" statement inside the
WHERE clause (... WHERE id IN ...). I am joining 7 tables together (in
the FROM), some of which can contain millions of records.
You might want get an SQL expert to check it out, see if it is efficient.
Also (if you can), check the indexes for various tables you select from
You also might want to conditionally generate parts of it depending on
customers responses.
I also apply some optimisations for WHERE:
if a list of things contains exactly 1 item , then I say "WHERE id =
item"
and if a list of things is numeric and integral, then I see if the list
of items is the same as maxitem - minitem + 1. If it is then I do "WHERE id
BETWEEN minitem AND maxitem"
Is there a limit on the number of WHERE conditions for an SQL
statement? I am certain that I must have over-complicated the SQL,
since I am not an expert. After all, it works fine in the office, so I
don't understand what could be different at the customer database to
cause such a problem. By the way, I have no access to the customer
database or their setup.
Has your customer got the same version of Oracle as you?
Moveover, has it had all the patches applied (that Oracle issue)?
I would check this out. It could be a bug in Oracle's optimiser that is
fixed in a patch.
On the limit: NAFAIK
I would not worry about too many clauses to WHERE.
Oracle is a sophisticated database of immense strength.
Adding WHERE clauses is good as it cuts down rows considered in the query
and improves performance.
Stephen Howe
.
- Prev by Date: Re: Limit data amount in response to SELECT query
- Previous by thread: Re: Connection to local DB works, but not to DB on network server
- Index(es):
Relevant Pages
|
|
