Home » SQL & PL/SQL » SQL & PL/SQL » Why optimizer select plan with higher cost?
Why optimizer select plan with higher cost? [message #221305] Mon, 26 February 2007 04:06 Go to next message
oskarsdba
Messages: 7
Registered: January 2007
Location: Latvia
Junior Member
Hi,
Why optimizer select plan with higher cost?

SQL with hint:
SELECT /*+ index(ordm ORDA_PK) */
ordm.orders_id h_docid, ordm.customer_nr h_clientid,
ordm.cl_doc_type_code h_doctype,
ordm.cl_doc_status_code cl_doc_status_code,
ordm.cl_external_error_code h_errorcode, ordm.sys_version_id h_version,
ordm.doc_number po_number, ordm.curdate po_curdate,
ordm.cl_currency_code po_curr,
TO_CHAR (ordm.amount, 'FM999999999999990.00') po_amount,
ordm.account_nr po_cust_accnum, ordm.customer_name po_cust_name,
ordd.cl_currency_cust_code po_cust_curr,
TO_CHAR (ordd.cust_rate, 'FM999999999990.0099999999') po_cust_rate,
ordd.cust_confirm po_cust_conf, ordd.ben_name po_ben_name,
ordd.ben_accnum po_ben_accnum,
ordd.cl_external_payment_code po_cust_amk, ordd.ben_info po_ben_info,
ordd.comments po_comments
FROM FINIX_IB.orders_archive ordm, FINIX_IB.orders_archive_fields ordd
WHERE ordm.orders_id = ordd.orders_id (+)
AND ordm.orders_id = NVL (4353, ordm.orders_id)
/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4918 Card=1 Bytes=185)
1 0 NESTED LOOPS (OUTER) (Cost=4918 Card=1 Bytes=185)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS_ARCHIVE' (TABLE) (Cost=4916 Card=1 Bytes=87)
3 2 INDEX (FULL SCAN) OF 'ORDA_PK' (INDEX (UNIQUE)) (Cost=4915 Card=1)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS_ARCHIVE_FIELDS' (TABLE) (Cost=2 Card=1 Bytes=98)
5 4 INDEX (RANGE SCAN) OF 'ORDAF_ORDA_FK' (INDEX) (Cost=1 Card=1)



SQL without hint:
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4792 consistent gets
4786 physical reads
0 redo size
1020 bytes sent via SQL*Net to client
237 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9675 Card=1 Bytes=185)
1 0 NESTED LOOPS (OUTER) (Cost=9675 Card=1 Bytes=185)
2 1 TABLE ACCESS (FULL) OF 'ORDERS_ARCHIVE' (TABLE) (Cost=9673 Card=1 Bytes=87)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS_ARCHIVE_FIELDS' (TABLE) (Cost=2 Card=1 Bytes=98)
4 3 INDEX (RANGE SCAN) OF 'ORDAF_ORDA_FK' (INDEX) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
39706 consistent gets
39694 physical reads
0 redo size
1037 bytes sent via SQL*Net to client
237 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

,thanks

[Updated on: Mon, 26 February 2007 04:08]

Report message to a moderator

Re: Why optimizer select plan with higher cost? [message #221311 is a reply to message #221305] Mon, 26 February 2007 04:15 Go to previous messageGo to next message
oskarsdba
Messages: 7
Registered: January 2007
Location: Latvia
Junior Member
Already tried to flush shared pool, also restarted database
Re: Why optimizer select plan with higher cost? [message #221343 is a reply to message #221305] Mon, 26 February 2007 07:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A hint artificially lowers the cost, to 'promote' the hinted path.
Re: Why optimizer select plan with higher cost? [message #221346 is a reply to message #221311] Mon, 26 February 2007 07:35 Go to previous messageGo to next message
oskarsdba
Messages: 7
Registered: January 2007
Location: Latvia
Junior Member
yes, thanks
I got reference to Tom Kyte http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313416745628

also to fix query I will use :
example
7 AND ordm.orders_id = decode(4353,null,ordm.orders_id,4353)

full scan on index.

when null value passed:

7 AND ordm.orders_id = decode(null,null,ordm.orders_id,null)

full scan on table.

Re: Why optimizer select plan with higher cost? [message #221362 is a reply to message #221346] Mon, 26 February 2007 08:07 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
AND ordm.orders_id = NVL (4353, ordm.orders_id)

Here you're saying: if 4354 is empty (null) than replace it with ordm.orders_id. This will never happen, since 4353 is a value, so it is not null and never ever will be (in this universe, that is).

In general, NVL works like:
NVL(expr1,expr2)

If expr1 is null, then use expr2 (if not, keep expr1).
Previous Topic: How to extract parameter value whose name is in variable?
Next Topic: SQL Query
Goto Forum:
  


Current Time: Sat Dec 03 16:15:14 CST 2016

Total time taken to generate the page: 0.05824 seconds