Re: any ideas on how to improve this query

that's a pretty ugly looking piece of code. <g> can you explain what you're trying to do?

particularly:

• what do the records returned from this query represent?
• what is the "in_date" field supposed to be in the result set?
• what is the "in_trucker_code" field supposed to be?
• what is the "in_chassis" field supposed to be?

perhaps if you could explain what you're trying to do in plain english, the solution to optomizing your query would become much more clear.

here's what i gather that your query seems to be doing right now:

first off, you've cut-and-pasted the DECODE block four times (three in the select criteria and once in the WHERE clause). that monster seems to be the key to figuring out what you're trying to do and i bet it's what's killing your performance too.

you seem to be working with some sort of snowflake schema, with ihn_container as your fact table and container_masters, tml_container_lhts, and tml_container_lengths as dimension or lookup tables. however, the real catch seems to be with the inv_containers table. i'm not 100% sure of course but it appears to me that you want to join to the inv_containers table for the in_date, in_trucker_code, and chassis fields -- but you only want these fields *if* there does *not* exist a container after the current one within the same container_master. i think this is what you're trying to do with the DECODE block.

my first thoughts, if this is in fact what you're trying to accomplish, is that this sounds like an excellent place to apply Oracle analytics...

lou_nyc wrote:
> SELECT *
> FROM (
> SELECT ssl_user_code,
> ssl_user_code ssl,

...
> FROM HIS_containers i,
> container_masters cm,
> tml_container_lhts clht,
> tml_container_lengths cl
> WHERE i.chassis IS NULL AND

... Received on Thu Jun 23 2005 - 09:59:31 CDT