Merge Cartesian Join?

From: Brian Benton <briangbenton_at_yahoo.com>
Date: Thu, 29 Aug 2002 14:09:02 GMT
Message-ID: <3D6E2AF6.702_at_yahoo.com>

I have a query that, in some rare instances, is performing a merge join cartesian in the execution of its query plan. I am not sure why this is happening since the first condition in the where clause should pare the query immediately. This scenario occurs when the application is being tested under multi user loads. Below is a portion of the trace file:

---

select pi.physical_interface_id, pi.name, pi.stechid, pi.sporttypeid,

pi.ip_address, pi.subnet_mask, pi.primary, pi.description, pi.enabled,
pi.secondary_ip_address, pi.secondary_subnet_mask, pi.bandwidth, 
pi.interface_num,
pi.manual_configuration, i.type, i.multipoint, i.delay, i.supported, 

i.use_dns, i.dns_name,
i.is_half_connected, csu_dsu_id, cp.controller_pinterface_props_id from pwrup_qa_new.rnd_link_lcard_pinterface lpi, pwrup_qa_new.rnd_physical_interface pi, pwrup_qa_new.rnd_controller_pinterface_prop cp, pwrup_qa_new.rnd_interface i where lpi.line_card_id = hextoraw('0F1BA8BFF659EF3F1FDB1B91EF000000') and lpi.physical_interface_id = pi.physical_interface_id and pi.physical_interface_id = i.interface_id and pi.physical_interface_id = cp.physical_interface_id

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2841489 5 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 3 0.00 0.00 0 2841489 5 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation

------- ---------------------------------------------------

0 NESTED LOOPS
1420716 MERGE JOIN CARTESIAN
6204 MERGE JOIN CARTESIAN
6204 TABLE ACCESS FULL RND_INTERFACE
6204 BUFFER SORT
1 INDEX RANGE SCAN (object id 17083)
1420716 BUFFER SORT
229 TABLE ACCESS FULL RND_CONTROLLER_PINTERFACE_PROP 0 TABLE ACCESS BY INDEX ROWID RND_PHYSICAL_INTERFACE 0 INDEX UNIQUE SCAN (object id 17412)

---

There are three possible joins that would result in a Cartesian join. Namely, lpi * i, lpi * cp, and cp * i. But it is not clear to me why the optimizer (Optimizer goal: choose) would choose to perform a cartesian join. As I stated earlier, the query executes without performing a cartesian join in most cases.

Any insight is appreciated.

Thanks,
Brian Benton (brian.benton_at_powerupnetworks.com) Received on Thu Aug 29 2002 - 16:09:02 CEST