Home » RDBMS Server » Performance Tuning » SQL Needs to be tuned (Oracle 10g)
SQL Needs to be tuned [message #325190] Thu, 05 June 2008 04:55 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to tune the below query as this is taking around 4 seconds when the record when there are only 200 record and one user connected. There can be more than 1000 users connected and more records.
Please suggest me something which can make this query run little more faster. Will any hint help if yes, which one...I tried some randomly but couldn't get much help.

SELECT   COUNT (ryg), basedata.ryg
    FROM (SELECT (SELECT SUBSTR (vrygbs, 3, 1)
                    FROM vwsrygbs v1
                   WHERE v1.vrygbs =
                                 (SELECT MIN (v2.vrygbs)
                                    FROM vwsrygbs v2
                                   WHERE v1.vwkor_id_pk = v2.vwkor_id_pk)
                     AND ROWNUM = 1
                     AND owo.wkor_id_pk = v1.vwkor_id_pk) ryg,
                 owo.wkor_id_pk workorderid, owo.wkor_order_id_fk orderid,
                 vupdateddate currentstatustime,
                 vwostate.vstatunkey stateuniqueid,
                 owo.wkor_required_quantity orderquantity,
                    oli.oli_sku_number
                 || '-'
                 || oli.oli_skurevision_number skunumber,
                 oo.ord_deliverydatetime deliverydatetime,
                 oo.ord_deliverydatetime duedate, owsi.wsi_name workstepname,
                 asti.stai_state_name currentstatus,
                 asti.stai_state_name status,
                 ast.sta_state_id_pk currentstatusid,
                 owwm.wowsm_bom_id_fk bomid,
                 owo.wkor_parent_wo_id parentworkorderid,
                 owo.wkor_master_wo_id masterworkorderid,
                 owo.wkor_status_code workorderstatusid,
                 ows.wrs_id_pk workstepid
            FROM ord_orders oo,
                 vworkorder_states vwostate,
                 ord_wkor_ws_mapping owwm,
                 ord_work_steps ows,
                 ord_lineitems oli,
                 ord_work_steps_in owsi,
                 adm_states ast,
                 adm_states_in asti,
                 ord_work_order owo
           WHERE wkor_order_id_fk = oo.ord_id_pk
             AND vwostate.vwoid = owo.wkor_id_pk
             AND vwostate.vupdateddate = (
                    SELECT MAX (vwostate1.vupdateddate)
                      FROM vworkorder_states vwostate1
                     WHERE vwostate1.vwoid = owo.wkor_id_pk
                       AND vwostate1.voprid = ows.wrs_operation_type_id_fk
                       AND vwostate1.vwsid = ows.wrs_id_pk)
             AND vwostate.voprid = ows.wrs_operation_type_id_fk
             AND owwm.wowsm_wo_id = owo.wkor_id_pk
             AND owwm.wowsm_ws_id = ows.wrs_id_pk
             AND oli.oli_order_id_fk = owo.wkor_order_id_fk
             AND wkor_ord_lineitem_id_fk = oli_id_pk
             AND owsi.wsi_id_fk = ows.wrs_id_pk
             AND ast.sta_state_id_pk = asti.stai_state_id_fk
             AND vwostate.vstatunkey = ast.sta_state_key_un
             AND oo.ord_completed_date IS NULL
             AND NVL (oo.ord_type, 0) != 'D'
             AND NVL (oli.oli_type, 0) != 'P'
             AND NVL (owo.wkor_type, 0) NOT IN ('P', 'X')
             AND owsi.wsi_language_id_fk = 1
             AND owsi.wsi_language_id_fk = asti.stai_language_id_fk
             AND owwm.wowsm_ws_id = vwostate.vwsid
             AND owo.wkor_status_code != 129
             AND owo.wkor_is_complete != 1
             AND vwostate.vupdateddate = (
                                        SELECT MAX (vwostate1.vupdateddate)
                                          FROM vworkorder_states vwostate1
                                         WHERE vwostate1.vwoid =
                                                                owo.wkor_id_pk)) basedata,
         adm_states as1,
         adm_states as2
   WHERE as1.sta_state_id_pk = basedata.currentstatusid
     AND as2.sta_state_id_pk = basedata.workorderstatusid
     AND (   (    (ryg = 'B' OR ryg = 'S')
              AND (as1.sta_operation_type_id_fk = as2.sta_operation_type_id_fk
                  )
             )
          OR (ryg = 'R' OR ryg = 'Y' OR ryg = 'G')
         )
     AND basedata.ryg != 'S'
GROUP BY ryg


I am also attaching the explain_plan which doesn't look in a good format. I copied but its not coming formatted...don't know how to do this.
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	110  	 	      	 	 
  COUNT STOPKEY		  	 	 	 	      	 	 
    NESTED LOOPS		1  	36  	24  	 	      	 	 
      VIEW	VW_SQ_1	1  	18  	12  	 	      	 	 
        SORT GROUP BY		1  	44  	12  	 	      	 	 
          HASH JOIN OUTER		3  	132  	12  	 	      	 	 
            MERGE JOIN CARTESIAN		1  	38  	8  	 	      	 	 
              NESTED LOOPS		1  	32  	5  	 	      	 	 
                NESTED LOOPS		1  	20  	3  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	12  	2  	 	      	 	 
                    INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                    INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                  INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
              BUFFER SORT		1  	6  	6  	 	      	 	 
                TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
            TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
      VIEW	VWSRYGBS	1  	18  	13  	 	      	 	 
        SORT ORDER BY		3  	141  	13  	 	      	 	 
          HASH JOIN OUTER		3  	141  	12  	 	      	 	 
            MERGE JOIN CARTESIAN		1  	41  	8  	 	      	 	 
              NESTED LOOPS		1  	35  	5  	 	      	 	 
                NESTED LOOPS		1  	23  	3  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	15  	2  	 	      	 	 
                    INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                    INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                  INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
              BUFFER SORT		1  	6  	6  	 	      	 	 
                TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
            TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
  COUNT STOPKEY		  	 	 	 	      	 	 
    NESTED LOOPS		1  	36  	24  	 	      	 	 
      VIEW	VW_SQ_1	1  	18  	12  	 	      	 	 
        SORT GROUP BY		1  	44  	12  	 	      	 	 
          HASH JOIN OUTER		3  	132  	12  	 	      	 	 
            MERGE JOIN CARTESIAN		1  	38  	8  	 	      	 	 
              NESTED LOOPS		1  	32  	5  	 	      	 	 
                NESTED LOOPS		1  	20  	3  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	12  	2  	 	      	 	 
                    INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                    INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                  INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
              BUFFER SORT		1  	6  	6  	 	      	 	 
                TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
            TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
      VIEW	VWSRYGBS	1  	18  	13  	 	      	 	 
        SORT ORDER BY		3  	141  	13  	 	      	 	 
          HASH JOIN OUTER		3  	141  	12  	 	      	 	 
            MERGE JOIN CARTESIAN		1  	41  	8  	 	      	 	 
              NESTED LOOPS		1  	35  	5  	 	      	 	 
                NESTED LOOPS		1  	23  	3  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	15  	2  	 	      	 	 
                    INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                    INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                  INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
              BUFFER SORT		1  	6  	6  	 	      	 	 
                TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
            TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
  HASH GROUP BY		1  	136  	110  	 	      	 	 
    FILTER		  	 	 	 	      	 	 
      TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	1  	8  	1  	 	      	 	 
        NESTED LOOPS		3  	408  	68  	 	      	 	 
          HASH JOIN		3  	384  	65  	 	      	 	 
            TABLE ACCESS FULL	ORD_WKOR_WS_MAPPING	1 K	14 K	9  	 	      	 	 
            NESTED LOOPS		4 K	560 K	55  	 	      	 	 
              HASH JOIN		4 K	536 K	54  	 	      	 	 
                TABLE ACCESS FULL	ORD_WORK_STEPS	345  	1 K	3  	 	      	 	 
                NESTED LOOPS		333  	35 K	51  	 	      	 	 
                  NESTED LOOPS		333  	34 K	50  	 	      	 	 
                    NESTED LOOPS		333  	32 K	49  	 	      	 	 
                      NESTED LOOPS		333  	30 K	48  	 	      	 	 
                        HASH JOIN		333  	28 K	46  	 	      	 	 
                          TABLE ACCESS FULL	ORD_WORK_STEPS	345  	1 K	3  	 	      	 	 
                          HASH JOIN		333  	26 K	43  	 	      	 	 
                            TABLE ACCESS FULL	ORD_LINEITEMS	356  	3 K	5  	 	      	 	 
                            NESTED LOOPS		724  	51 K	37  	 	      	 	 
                              MERGE JOIN		724  	48 K	35  	 	      	 	 
                                TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	537  	10 K	10  	 	      	 	 
                                  INDEX FULL SCAN	ORD_WORK_ORDER_PK	39  	 	3  	 	      	 	 
                                    COUNT STOPKEY		  	 	 	 	      	 	 
                                      NESTED LOOPS		1  	36  	24  	 	      	 	 
                                        VIEW	VW_SQ_1	1  	18  	12  	 	      	 	 
                                          SORT GROUP BY		1  	44  	12  	 	      	 	 
                                            HASH JOIN OUTER		3  	132  	12  	 	      	 	 
                                              MERGE JOIN CARTESIAN		1  	38  	8  	 	      	 	 
                                                NESTED LOOPS		1  	32  	5  	 	      	 	 
                                                  NESTED LOOPS		1  	20  	3  	 	      	 	 
                                                    TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	12  	2  	 	      	 	 
                                                      INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                                                    TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                                                      INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                                                  TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                                                    INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
                                                BUFFER SORT		1  	6  	6  	 	      	 	 
                                                  TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
                                              TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
                                        VIEW	VWSRYGBS	1  	18  	13  	 	      	 	 
                                          SORT ORDER BY		3  	141  	13  	 	      	 	 
                                            HASH JOIN OUTER		3  	141  	12  	 	      	 	 
                                              MERGE JOIN CARTESIAN		1  	41  	8  	 	      	 	 
                                                NESTED LOOPS		1  	35  	5  	 	      	 	 
                                                  NESTED LOOPS		1  	23  	3  	 	      	 	 
                                                    TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	15  	2  	 	      	 	 
                                                      INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                                                    TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                                                      INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                                                  TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                                                    INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
                                                BUFFER SORT		1  	6  	6  	 	      	 	 
                                                  TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
                                              TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
                                SORT JOIN		1 K	48 K	25  	 	      	 	 
                                  VIEW		1 K	48 K	24  	 	      	 	 
                                    SORT UNIQUE		1 K	39 K	24  	 	      	 	 
                                      UNION-ALL		  	 	 	 	      	 	 
                                        TABLE ACCESS FULL	ORD_WO_STATES_HISTORY	270  	8 K	11  	 	      	 	 
                                        TABLE ACCESS FULL	ORD_WORK_ORDER	774  	30 K	11  	 	      	 	 
                              TABLE ACCESS BY INDEX ROWID	ADM_STATES	1  	5  	1  	 	      	 	 
                                INDEX UNIQUE SCAN	SYS_C0091764	1  	 	0  	 	      	 	 
                        TABLE ACCESS BY INDEX ROWID	ADM_STATES	1  	6  	1  	 	      	 	 
                          INDEX UNIQUE SCAN	SYS_C0091764	1  	 	0  	 	      	 	 
                      TABLE ACCESS BY INDEX ROWID	ADM_STATES	1  	6  	1  	 	      	 	 
                        INDEX UNIQUE SCAN	CONS_STA_STATE_KEY_UN	1  	 	0  	 	      	 	 
                    TABLE ACCESS BY INDEX ROWID	ADM_STATES	1  	5  	1  	 	      	 	 
                      INDEX UNIQUE SCAN	SYS_C0091764	1  	 	0  	 	      	 	 
                  INDEX UNIQUE SCAN	SYS_C0091767	1  	5  	0  	 	      	 	 
              INDEX UNIQUE SCAN	ORD_WORK_STEPS_IN_PK	1  	5  	0  	 	      	 	 
          INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
      COUNT STOPKEY		  	 	 	 	      	 	 
        NESTED LOOPS		1  	36  	24  	 	      	 	 
          VIEW	VW_SQ_1	1  	18  	12  	 	      	 	 
            SORT GROUP BY		1  	44  	12  	 	      	 	 
              HASH JOIN OUTER		3  	132  	12  	 	      	 	 
                MERGE JOIN CARTESIAN		1  	38  	8  	 	      	 	 
                  NESTED LOOPS		1  	32  	5  	 	      	 	 
                    NESTED LOOPS		1  	20  	3  	 	      	 	 
                      TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	12  	2  	 	      	 	 
                        INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                      TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                        INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                    TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                      INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
                  BUFFER SORT		1  	6  	6  	 	      	 	 
                    TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
                TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
          VIEW	VWSRYGBS	1  	18  	13  	 	      	 	 
            SORT ORDER BY		3  	141  	13  	 	      	 	 
              HASH JOIN OUTER		3  	141  	12  	 	      	 	 
                MERGE JOIN CARTESIAN		1  	41  	8  	 	      	 	 
                  NESTED LOOPS		1  	35  	5  	 	      	 	 
                    NESTED LOOPS		1  	23  	3  	 	      	 	 
                      TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	15  	2  	 	      	 	 
                        INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
                      TABLE ACCESS BY INDEX ROWID	ORD_ORDERS	360  	2 K	1  	 	      	 	 
                        INDEX RANGE SCAN	ORDER_ID	1  	 	0  	 	      	 	 
                    TABLE ACCESS BY INDEX ROWID	ORD_WKOR_WS_MAPPING	1  	12  	2  	 	      	 	 
                      INDEX RANGE SCAN	IDX_ORD_WKOR_WS_MAPPING_WO_ID	1  	 	1  	 	      	 	 
                  BUFFER SORT		1  	6  	6  	 	      	 	 
                    TABLE ACCESS FULL	ADM_SITE_CONFIGURATION	1  	6  	3  	 	      	 	 
                TABLE ACCESS FULL	ADM_STATES	75  	450  	3  	 	      	 	 
      SORT AGGREGATE		1  	56  	 	 	      	 	 
        NESTED LOOPS OUTER		1  	56  	8  	 	      	 	 
          NESTED LOOPS		1  	53  	8  	 	      	 	 
            TABLE ACCESS BY INDEX ROWID	ORD_WORK_STEPS	1  	5  	1  	 	      	 	 
              INDEX UNIQUE SCAN	SYS_C0092089	1  	 	0  	 	      	 	 
            VIEW		1  	48  	7  	 	      	 	 
              SORT UNIQUE		2  	72  	7  	 	      	 	 
                UNION-ALL		  	 	 	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WO_STATES_HISTORY	1  	31  	3  	 	      	 	 
                    INDEX RANGE SCAN	IDX_ORD_WO_ST_HIST_WO_ID_FK	4  	 	1  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	41  	2  	 	      	 	 
                    INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
          INDEX UNIQUE SCAN	SYS_C0091764	1  	3  	0  	 	      	 	 
      SORT AGGREGATE		1  	54  	 	 	      	 	 
        NESTED LOOPS		5  	270  	7  	 	      	 	 
          NESTED LOOPS OUTER		5  	255  	7  	 	      	 	 
            VIEW		5  	240  	7  	 	      	 	 
              SORT UNIQUE		5  	165  	7  	 	      	 	 
                UNION-ALL		  	 	 	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WO_STATES_HISTORY	4  	124  	3  	 	      	 	 
                    INDEX RANGE SCAN	IDX_ORD_WO_ST_HIST_WO_ID_FK	4  	 	1  	 	      	 	 
                  TABLE ACCESS BY INDEX ROWID	ORD_WORK_ORDER	1  	41  	2  	 	      	 	 
                    INDEX UNIQUE SCAN	ORD_WORK_ORDER_PK	1  	 	1  	 	      	 	 
            INDEX UNIQUE SCAN	SYS_C0091764	1  	3  	0  	 	      	 	 
          INDEX UNIQUE SCAN	SYS_C0092089	1  	3  	0  	 	      	 	 

Sorry for the bad format of the explain_plan given above as I do not know how to copy in a better formatted way from Toad. If anyone knows that please let me know.

Thanks in advance,
Mahi

[Updated on: Thu, 05 June 2008 05:18]

Report message to a moderator

Re: SQL Needs to be tuned [message #325383 is a reply to message #325190] Fri, 06 June 2008 01:45 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Remember following Points:-

1) Use less joins of tables

when you are joining tables oracle first get the data in
buffer and then apply join conditions on the data.
use inline views to eliminate rows from each table.

2) First ask yourself what data you require from each table.
Their can be cases where you are accessing only one field
from a particular table and that can be easily get by using
user functions.


Try to remove this one:- MERGE JOIN CARTESIAN??
Re: SQL Needs to be tuned [message #325431 is a reply to message #325383] Fri, 06 June 2008 03:32 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks for your answer Raja,
Can you please give me a small example of removing extra joins as my query... just a example with refernece to my code...so that I can eliminate Merge cartesian as I am not very comfortable with tuning queries. Your help would let me learn things and tune queries in future.

Thanks in advance,
Mahi
Re: SQL Needs to be tuned [message #325444 is a reply to message #325431] Fri, 06 June 2008 04:05 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please study Explain Plan first.Then you can understand the logic how Oracle process a query.

How to eliminate join check this out:-

Example:-

Suppose you have two tables Table1 and Table2.
Now your requirement is that you need data from Table1 and need
to eliminate rows returned on basis of certain conditions based on Table2.

You use JOIN for this.

Select Table1.* from Table1,Table2
where Table1.<Column1>=Table2.<Column1>
and Table2.<Column2>=<Certain Value>


But This will access Table1 and Table2 and then eliminate the rows.Oracle takes time to fetch data from both tables and then
eliminate the data returned.

You can use EXISTS to eliminate this:-
Select Table1.* from Table1
Where EXISTS(select 1 from Table2 where Table1.<Column1>=Table2.<Column1>
and Table2.<Column2>=<Certain Value>)




Re: SQL Needs to be tuned [message #325446 is a reply to message #325444] Fri, 06 June 2008 04:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Regarding Merge Join CARTESIAN:-

you are missing some join conditions while accessing your tables.

For Example:-

if we join two tables without a condition then it will result in CARTESIAN product.

select Table1.* from Table1,Table2


Output:--CARTESIAN product
Re: SQL Needs to be tuned [message #325470 is a reply to message #325446] Fri, 06 June 2008 05:20 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thank you so much Raja for explaining the basics to me. Now I really got some idea by your useful easy example.

I am looking into my query and trying to find if it takes less time by changing the code.....

I will come back to you in case I face any more problem.

Thanks,
Mahi
Re: SQL Needs to be tuned [message #327084 is a reply to message #325190] Fri, 13 June 2008 12:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi rajatratewal, I do not wish to contradict you. Your description of fewer table joins was accurate many years ago for the Oracle Database.

But these days there would be no difference between these two versions of the query. The real thing I would focus on would be to see if the right steps had been taking for the database:

1) are the proper indexes and constraints in place?
2) have statistics been gathered?
3) is this the correct query?

In particular, with #3 above, should the query actually need to retrieve data from the second table, then it is not possible to construct the exists version of the query anyway.

Also, an index on the second table TALBLE2 (COLUMN1,COLUMN2), would likely mean be no access to table2 at all, only its index.

If you believe there is a difference between how these query versions would be executed, can you please provide the details of the process oracle would follow when retrieving the data? A simple psuedo code example would be fine. I am certainly not above believing that I am not familiar with something as Oracle changes every day.

But.. I am pretty sure both versions of this query would be executed the same way on a 8i, 9i, 10g, or 11g database.

I mention all this only because I feel that it would not benefit the OP to spend time re-writing his queries with this approach in mind. They should be focusing on something else.

What do you think?

Thanks, Kevin
Re: SQL Needs to be tuned [message #327279 is a reply to message #327084] Sun, 15 June 2008 23:06 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Mr. Kevin my main concern was that pick only required data.
Their is no advantage of unnecesaary joins.


Yes the points that you have mentioned are equally important.

But my friend their is no Hard and fast rule for SQL tuning.

I take this approach.I eliminate unnecesary joins first.Check stats & indexes are available.

And it works for me.



Previous Topic: delete statement is causing lot of performance hurt in DB
Next Topic: Performance improvement measures
Goto Forum:
  


Current Time: Sat Dec 03 18:08:28 CST 2016

Total time taken to generate the page: 0.14849 seconds