Selecting records which dont have referencing records [message #377818] |
Thu, 25 December 2008 13:26  |
yoonus.it@gmail.com
Messages: 109 Registered: June 2007 Location: kuwait
|
Senior Member |
|
|
I have a table with data in the following format
rh_sysid rh_txn_type rh_ref_sys_id rh_ref_txn_cod
800 ssmr
801 sspr 800 ssmr
810 ssmr
811 ssmr
812 ssmr
813 sspr 812 ssmr
I want to get only records which has ssmr without sspr
result i am expecting is
rh_sysid rh_txn_type rh_ref_sys_id rh_ref_txn_cod
810 ssmr
811 ssmr
when i use self non equal join its giving me result but it will produce cartesain join giving me many rows.
I am looking at bringing down the cost of a query.
Looking forward to your advising
[Updated on: Thu, 25 December 2008 13:31] by Moderator Report message to a moderator
|
|
|
|
Re: Selecting records which dont have referencing records [message #377825 is a reply to message #377818] |
Thu, 25 December 2008 15:09   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
* Depending on the data provided...
oli@ORAFAQ.US.ORACLE.COM> select * from test_377818;
RH_SY RH_TX RH_RE RH_RE
----- ----- ----- -----
800 ssmr
801 sspr 800 ssmr
810 ssmr
811 ssmr
812 ssmr
813 sspr 812 ssmr
6 rows selected.
oli@ORAFAQ.US.ORACLE.COM> ed
Wrote file afiedt.buf
1 select a.rh_sysid,a.rh_txn_type,a.rh_ref_sys_id,a.rh_ref_txn_cod
2 from test_377818 a where a.rh_sysid not in
3* (select b.rh_ref_sys_id from test_377818 b where b.rh_ref_sys_id is not null ) and a.rh_txn_type<>'sspr'
oli@ORAFAQ.US.ORACLE.COM> /
RH_SY RH_TX RH_RE RH_RE
----- ----- ----- -----
810 ssmr
811 ssmr
Try modifying the query....
Regards,
Oli
[Updated on: Thu, 25 December 2008 15:51] Report message to a moderator
|
|
|
Re: Selecting records which dont have referencing records [message #377924 is a reply to message #377825] |
Fri, 26 December 2008 06:54   |
yoonus.it@gmail.com
Messages: 109 Registered: June 2007 Location: kuwait
|
Senior Member |
|
|
Michel please see the test case down as you have pointed.
create table ot_req (rh_sysid NUMBER(4),rh_txn_type VARCHAR2(5),rh_ref_sys_id NUMBER(4),rh_ref_txn_cod VARCHAR2(5));
INSERT INTO ot_req VALUES(800,'SSMR',NULL,NULL);
INSERT INTO ot_req VALUES(801,'SSPR',800,'SSMR');
INSERT INTO ot_req VALUES(810,'SSMR',NULL,NULL);
INSERT INTO ot_req VALUES(811,'SSMR',NULL,NULL);
INSERT INTO ot_req VALUES(812,'SSMR',NULL,NULL);
INSERT INTO ot_req VALUES(813,'SSPR',812,'SSMR');
INSERT INTO ot_req VALUES(814,'SSMR',NULL,NULL);
INSERT INTO ot_req VALUES(815,'SSPR',814,'SSMR');
SELECT
rh_sysid
FROM
ot_req
WHERE
rh_sysid
NOT IN
(SELECT
rh_ref_sys_id
FROM
ot_req
WHERE
rh_ref_sys_id IS NOT NULL)
AND
rh_txn_type!='SSPR';
RH_SYSID
810
811
I can get result but my issue is to bring down the cost. This table has more than 50000 rows.
|
|
|
|
|
|
|
Re: Selecting records which dont have referencing records [message #377962 is a reply to message #377959] |
Sat, 27 December 2008 01:26   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
select rh_sysid from ot_req where substr(rh_txn_type,3) = 'mr'
minus
select rh_ref_sys_id from ot_req
Add an index on (substr(rh_txn_type,3),rh_sysid).
I don't know the other queries you execute but an index on each column is doubtful.
In addition if parent type always ends with 'mr' and child with 'pr' and parent and child always have the same first 2 characters, this means you shoud have 2 fields and not one.
Regards
Michel
[Updated on: Sat, 27 December 2008 01:30] Report message to a moderator
|
|
|
|
Re: Selecting records which dont have referencing records [message #377999 is a reply to message #377998] |
Sat, 27 December 2008 12:16   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
See Normalization.
If your field contains 2 parts, it means it not a relational attribute it is 2 attributes: the first part that define the real type and the second part that defines if it is a parent or a child (a subtype if you want).
So there should be 2 columns.
More as the second part can be entirely defined by the fact that rh_ref_sys_id is null or not, the second part is not a real value.
Regards
Michel
[Updated on: Sat, 27 December 2008 12:16] Report message to a moderator
|
|
|
|
|