SEO

SELECT *
FROM (
SELECT ssl_user_code,
ssl_user_code ssl,
i.container,
out_date,
cl.code LENGTH_CODE,
out_trucker_code,
decode ((SELECT ih.in_date
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id >
i.container_id
AND ihh.container =
i.container)), NULL,
(SELECT ic.in_date
FROM inv_containers ic
WHERE ic.container = i.container)) IN_DATE,
decode ((SELECT ih.in_trucker_code
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id >
i.container_id
AND ihh.container =
i.container)), NULL,
(SELECT ic.in_trucker_code
FROM inv_containers ic
WHERE ic.container = i.container)) IN_TRUCKER_CODE,
decode ((SELECT ih.chassis
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id >
i.container_id
AND ihh.container =
i.container)), NULL,
(SELECT ic.chassis
FROM inv_containers ic
WHERE ic.container = i.container)) IN_CHASSIS
FROM HIS_containers i,
container_masters cm,
tml_container_lhts clht,
tml_container_lengths cl
WHERE i.chassis IS NULL AND
i.out_mode = 'T'
-- i.out_date BETWEEN to_date ('1-JAN-2005','DD-MON-YYYY') AND
to_date ('01-FEB-2005','DD-MON-YYYY')*/
-- AND SSL_USER_CODE = 'ACL'
-- AND ((OUT_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
HH24:MI:SS')
-- AND TO_DATE('01-MAR-05 23:59:59', 'DD-MON-RR
HH24:MI:SS'))
-- OR
-- (IN_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
HH24:MI:SS')
-- AND TO_DATE('01-MAR-05 23:59:59', 'DD-MON-RR
HH24:MI:SS')))
AND
cm.container = i.container AND
cm.lht_code = clht.code AND
clht.length_code = cl.code AND
decode ((SELECT ih.container_id
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id >
i.container_id
AND ihh.container =
i.container)), NULL,
(SELECT ic.container_id
FROM inv_containers ic
WHERE ic.container = i.container)) IS NOT NULL
)
WHERE
SSL_USER_CODE = 'ACL'
AND
((OUT_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
HH24:MI:SS')
AND TO_DATE('31-MAR-05 23:59:59', 'DD-MON-RR
HH24:MI:SS'))
OR
(IN_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
HH24:MI:SS')
AND TO_DATE('31-MAR-05 23:59:59', 'DD-MON-RR
HH24:MI:SS')))
and in_date is null

On 22 Jun 2005 12:27:40 -0700, "lou_nyc" <lnlx@aol.com> wrote:

>SELECT *
>FROM (
>SELECT ssl_user_code,
> ssl_user_code ssl,
> i.container,
> out_date,
> cl.code LENGTH_CODE,
> out_trucker_code,
> decode ((SELECT ih.in_date
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.in_date
> FROM inv_containers ic
> WHERE ic.container = i.container)) IN_DATE,
> decode ((SELECT ih.in_trucker_code
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.in_trucker_code
> FROM inv_containers ic
> WHERE ic.container = i.container)) IN_TRUCKER_CODE,
> decode ((SELECT ih.chassis
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.chassis
> FROM inv_containers ic
> WHERE ic.container = i.container)) IN_CHASSIS
> FROM HIS_containers i,
> container_masters cm,
> tml_container_lhts clht,
> tml_container_lengths cl
> WHERE i.chassis IS NULL AND
> i.out_mode = 'T'
> -- i.out_date BETWEEN to_date ('1-JAN-2005','DD-MON-YYYY') AND
>to_date ('01-FEB-2005','DD-MON-YYYY')*/
>-- AND SSL_USER_CODE = 'ACL'
> -- AND ((OUT_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> -- AND TO_DATE('01-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS'))
> -- OR
> -- (IN_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> -- AND TO_DATE('01-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS')))
> AND
> cm.container = i.container AND
> cm.lht_code = clht.code AND
> clht.length_code = cl.code AND
> decode ((SELECT ih.container_id
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.container_id
> FROM inv_containers ic
> WHERE ic.container = i.container)) IS NOT NULL
>)
>WHERE
>SSL_USER_CODE = 'ACL'
>AND
>((OUT_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> AND TO_DATE('31-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS'))
> OR
> (IN_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> AND TO_DATE('31-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS')))
>and in_date is null

Convert the bunch of correlated inline views into *ONE* join.

--
Sybrand Bakker, Senior Oracle DBA

"lou_nyc" <lnlx@aol.com> wrote in message
news:1119468460.838886.122110@g47g2000cwa.googlegr oups.com...
same response to the last time you posted it

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
....