Home » RDBMS Server » Performance Tuning » SQL Tuning
SQL Tuning [message #65626] |
Wed, 17 November 2004 08:53 |
Sounder S. Raja
Messages: 4 Registered: November 2003
|
Junior Member |
|
|
Hi,
I did an explain plan on the following query. The plan is appended below. I am wondering if there is anyway to tune this query. I tried essentially moving things around (order of the tables and the WHERE and AND clauses) but nothing changed. I am a novice to performance tuning so please bear with me.
Many thanks in advance!
Sankar.
Query:
=====
EXPLAIN PLAN
SET STATEMENT_ID = 'SMTEST02'
FOR SELECT a.deal_id,
a.customer_company_nm,
a.deal_nm,
a.deal_revenue,
a.deal_status_val,
a.create_dt deal_action_date,
b.sun_id sales_rep_id,
c.first_nm sales_rep_first_nm,
c.last_nm sales_rep_last_nm
FROM nsda_user c,
deal_account_team b,
deal a
WHERE b.title_nm = 'PRIMARY SALES REP'
AND a.deal_id = b.deal_id
AND b.sun_id = c.sun_id (+);
===========================================
Table Counts : FYI
===========================================
SQL> select count(*) from deal_account_team;
COUNT(*)
----------
10000
SQL> select count(*) from nsda_user;
COUNT(*)
----------
34
SQL> select count(*) from deal;
COUNT(*)
----------
30000
========================================
Plan Output:
===========
SELECT
lpad(' ',level-1)||operation||' '||options||' '||object_name "Query Plan",
cardinality "Rows",
cost "Cost"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = 'SMTEST02'
START WITH id = 0
ORDER BY id;
Query Plan Rows Cost
---------- ---- ----
SELECT STATEMENT 10000 47
HASH JOIN 10000 47
HASH JOIN OUTER 10000 11
INDEX FAST FULL SCAN SYS_C00156567 10000 5
TABLE ACCESS FULL NSDA_USER 34 2
TABLE ACCESS FULL DEAL 30000 30
|
|
|
|
|
|
|
Re: SQL Tuning [message #65673 is a reply to message #65626] |
Mon, 29 November 2004 19:07 |
Venkat
Messages: 110 Registered: February 2001
|
Senior Member |
|
|
what IS THE significance OF HASH JOIN IN execution PLAN
iam using the function in a sql query.
iam comparing the date column of a table with some function (logical date function ).
which type of the index will help to optiize this query
|
|
|
Goto Forum:
Current Time: Sat Jan 11 09:16:44 CST 2025
|