I've been doing some more work on this script, however during testing I've discovered that it's producing a cartasian merge join, any help or advice on how to tune - prevent this would be great - and yes I understand that there are no indexes, it's not the tuning for speed that I care about right now - just the sql working...

thanks v much.

SELECT v.id,
v.registrationnumber,
v.dateofregistration,
vm.name vehicle_model,
vm.abicd,
vma.manufname vehicle_manufacturer,
vma.vmabicd manufacturer_abi_code,
vm.cc engin_size,
m.transmission,
m2.transmission fueltype,
vm.doors,
v.numofseats,
m4.transmission paintworkcolour,
m3.transmission vehicle_body_style,
v.vinnumber,
m5.transmission overnight_parking_provision,
m6.transmission overnight_parking_location,
v.odometerreading,
v.dateofodometerreading,
(SELECT count(*) FROM vehiclemodifications vmof
WHERE vmof.vehicleid(+) = v.id) numofmodifications,
(SELECT count(*) FROM vehiclesecuritydevice vsd
WHERE vsd.vehicleid(+) = v.id) nomofsecuritydevices,
vu.annualdistancepleasure,
vu.annualdistancebusiness,
v.purchaseprice,
v.currentvalue,
a.addressline1,
a.addressline2,
a.addressline3,
a.addressline4,
a.postcode
FROM vehicle v,
vehiclemodel vm,
vehiclemanufacturer vma,
vehicleusage vu,
mktlistitem m,
mktlistitem m2,
mktlistitem m3,
mktlistitem m4,
mktlistitem m5,
mktlistitem m6,
address a
WHERE v.modelcd = vm.abicd
AND vm.vehiclemanufid = vma.vmabicd
AND v.vehicleusageid = vu.id
AND (vm.transtypedc = m.abicd
AND m.mktlistnameid = 49)
AND (vm.fueltypedc = m2.abicd
AND m2.mktlistnameid = 45)
AND (to_char(v.bodystylecd) = m3.abicd
AND m3.mktlistnameid = 55)
AND (v.paintworkcolourcd = m4.abicd
AND m4.mktlistnameid = 9)
AND (to_char(vu.overnightparkingprovisioncd) = m5.abicd
AND m5.mktlistnameid = 12)
AND (to_char(vu.overnightparkinglocationcd) = m6.abicd
AND m6.mktlistnameid = 1012)
AND a.id = vu.addressid;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=293 Card=3 Bytes=963)
1 0 HASH JOIN (Cost=293 Card=3 Bytes=963)
2 1 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
3 1 HASH JOIN (Cost=281 Card=122 Bytes=36112)
4 3 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
5 3 HASH JOIN (Cost=269 Card=122 Bytes=33062)
6 5 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
7 5 HASH JOIN (Cost=257 Card=122 Bytes=30012)
8 7 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
9 7 HASH JOIN (Cost=245 Card=122 Bytes=26962)
10 9 TABLE ACCESS (FULL) OF 'VEHICLEMANUFACTURER' (Cost=1 Card=356 Bytes=3560)
11 9 HASH JOIN (Cost=243 Card=122 Bytes=25742)
12 11 TABLE ACCESS (FULL) OF 'VEHICLEMODEL' (Cost=67 Card=37352 Bytes=1157912)
13 11 HASH JOIN (Cost=87 Card=8771 Bytes=1578780)
14 13 TABLE ACCESS (FULL) OF 'ADDRESS' (Cost=1 Card=159 Bytes=9063)
15 13 HASH JOIN (Cost=85 Card=8771 Bytes=1078833)
16 15 TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=1 Card=123 Bytes=7626)
17 15 MERGE JOIN (CARTESIAN) (Cost=83 Card=7416 Bytes=452376)
18 17 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
19 17 SORT (JOIN) (Cost=72 Card=103 Bytes=3708)
20 19 TABLE ACCESS (FULL) OF 'VEHICLEUSAGE' (Cost=1 Card=103 Bytes=3708)