here is my explain plan of my query,.............


Target:
CS22DB

Version: Oracle 9.2.0.1.0

Database: CS22DB

Schema: CS22USER

Date: 04-May-07 7:00:00 AM


SQL Statement:

SELECT DISTINCT ph.k_po_num, pd.supp_id supp_id, ph.po_status_flg po_status_flg,
cs_udf.cs_getdatefromjulian(ph.created_dt) created_dt,
ph.source_doc_type
FROM po_headers ph, po_details pd, item_dcs id
WHERE cs_udf.cs_trim(pd.item_id) = cs_udf.cs_trim(id.k_item_id)
AND pd.dc_id = id.k_dc_id
AND pd.k_comp_id = id.k_comp_id
AND id.owner_id IN (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12,
:13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26,
:27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40,
:41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54,
:55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68,
:69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82,
:83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96,
:97, :98, :99, :100, :101, :102, :103, :104, :105, :106, :107, :108,
:109, :110, :111, :112, :113, :114, :115, :116, :117, :118, :119,
:120, :121, :122, :123, :124, :125, :126, :127, :128, :129, :130,
:131, :132, :133, :134, :135, :136, :137, :138, :139, :140, :141,
:142, :143, :144, :145, :146, :147, :148, :149, :150, :151, :152,
:153, :154, :155, :156, :157, :158, :159, :160, :161, :162, :163,
:164, :165, :166, :167, :168, :169, :170, :171, :172, :173, :174,
:175, :176, :177, :178, :179, :180, :181, :182, :183, :184, :185,
:186, :187, :188, :189, :190, :191, :192, :193, :194, :195, :196,
:197, :198, :199, :200, :201, :202, :203, :204, :205, :206, :207,
:208, :209, :210, :211, :212, :213, :214, :215, :216, :217, :218,
:219, :220, :221, :222, :223, :224, :225, :226, :227, :228, :229,
:230, :231, :232, :233, :234, :235, :236, :237, :238, :239, :240,
:241, :242, :243, :244, :245, :246, :247, :248, :249, :250, :251,
:252, :253, :254, :255, :256, :257, :258, :259, :260)
AND pd.supp_id LIKE :261
AND ph.owner_id <> :262
AND id.active_flg = 'A'
AND ph.k_po_num = pd.k_po_num
AND ph.k_comp_id = pd.k_comp_id
AND ph.k_comp_id = :263
AND ph.active_flg = 'A'
AND pd.active_flg IN ('A', 'I')
AND ph.k_po_num LIKE :264
AND pd.item_id BETWEEN cs_udf.cs_nvlc(:265, pd.item_id) AND
cs_udf.cs_nvlc(:266, pd.item_id)
AND ph.created_by LIKE :267
AND pd.dc_id BETWEEN cs_udf.cs_nvlc(:268, pd.dc_id) AND
cs_udf.cs_nvlc(:269, pd.dc_id)
AND pd.eta_dt BETWEEN cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:270, 'YYYYMMDD')), pd.eta_dt) AND
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(cs_udf.cs_to_date(:271,
'YYYYMMDD')), pd.eta_dt)
AND ph.po_stage_flg LIKE :272
AND ph.po_status_flg LIKE :273
AND ph.created_dt BETWEEN cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:274, 'YYYYMMDD')), ph.created_dt) AND
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(cs_udf.cs_to_date(:275,
'YYYYMMDD')), ph.created_dt)
AND ph.close_dt BETWEEN cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:276, 'YYYYMMDD')), ph.close_dt) AND
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(cs_udf.cs_to_date(:277,
'YYYYMMDD')), ph.close_dt)


Optimizer Mode Used:

COST ALL ROWS (optimizer: CHOOSE)

Total Cost:

93

Execution Steps:

Step # Step Name
10 SELECT STATEMENT
9 SORT [UNIQUE]
8 CS22USER.PO_DETAILS TABLE ACCESS [BY INDEX ROWID]
7 NESTED LOOPS
5 MERGE JOIN [CARTESIAN]
2 CS22USER.PO_HEADERS TABLE ACCESS [BY INDEX ROWID]
1 CS22USER.PK_PO_HEADERS INDEX [RANGE SCAN]
4 BUFFER [SORT]
3 CS22USER.ITEM_DCS TABLE ACCESS [FULL]
6 CS22USER.PK_PO_DETAILS INDEX [RANGE SCAN]

Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
1 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index PK_PO_HEADERS. 2 10 --
2 This plan step retrieves rows from table PO_HEADERS through ROWID(s) returned by an index. 10 1 0.052
3 This plan step retrieves all rows from table ITEM_DCS. 79 15,333 449.209
4 This plan step sorts the buffer row source. 79 15,333 449.209
5 This plan step accepts two sets of rows and builds the set of all possible combinations of row pairs. The result set grows exponentially with the size of the row sets joined. 89 1 0.081
6 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index PK_PO_DETAILS. 1 1 --
7 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 91 1 0.137
8 This plan step retrieves rows from table PO_DETAILS through ROWID(s) returned by an index. 2 1 0.056
9 This plan step accepts a row set (its only child) and sorts it in order to identify and eliminate duplicates. 93 1 0.137
10 This plan step designates this statement as a SELECT statement. 93 -- --