Optimization Of Query... Pls help me

[richarddilip]

Could you please tell me how to optimize the query below .
1 SELECT A.firstname FirstName,
2 A.lastname LastName,
3 A.state State,
4 A.zip Zip,
5 A.dob DOB,
6 B.INSPATID SubscriberId,
7 B.intakeid CareCentrixIntakeId,
8 B.GRPNUMB GroupNumber,
9 D.carname InsuranceName
10 FROM tblpatient A,
11 tblpatintakeplan B,
12 tblnetworkplan C,
13 tblcarrier D
14 WHERE B.intakeid =2335540
15 AND A.patientnumber=B.patientnumber
16 AND B.planid = C.planid
17 AND C.carid = D.carid
18* AND B.PLANLEVELCD = 1
SQL> /

1 row selected.

Statistical data about the query:
------------------------------------
Elapsed: 00:00:08.01

Execution Plan
---------------------------------------------------------- Plan hash value: 809815659

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 15592 (1)| 00:03:08 |
| 1 | NESTED LOOPS | | 1 | 98 | 15592 (1)| 00:03:08 |
| 2 | NESTED LOOPS | | 1 | 65 | 15590 (1)| 00:03:08 |
| 3 | NESTED LOOPS | | 1 | 44 | 15589 (1)| 00:03:08 |
|* 4 | TABLE ACCESS FULL | TBLPATINTAKEPLAN | 1 | 37 | 15588 (1)| 00:03:08 |
| 5 | TABLE ACCESS BY INDEX ROWID| TBLNETWORKPLAN | 1 | 7 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_TBLNETWORKPLAN | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | TBLCARRIER | 1 | 21 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_TBLCARRIER | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TBLPATIENT | 1 | 33 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_TBLPATIENT | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."INTAKEID"=2335540 AND TO_NUMBER("B"."PLANLEVELCD")=1)
6 - access("B"."PLANID"="C"."PLANID")
8 - access("C"."CARID"="D"."CARID")
10 - access("A"."PATIENTNUMBER"="B"."PATIENTNUMBER")
Statistics
----------------------------------------------------------
65 recursive calls
0 db block gets
57112 consistent gets
57031 physical reads
0 redo size
844 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

1 row selected.

Elapsed: 00:00:07.60

Execution Plan
----------------------------------------------------------
Plan hash value: 809815659
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 15592 (1)| 00:03:08 |
| 1 | NESTED LOOPS | | 1 | 98 | 15592 (1)| 00:03:08 |
| 2 | NESTED LOOPS | | 1 | 65 | 15590 (1)| 00:03:08 |
| 3 | NESTED LOOPS | | 1 | 44 | 15589 (1)| 00:03:08 |
|* 4 | TABLE ACCESS FULL | TBLPATINTAKEPLAN | 1 | 37 | 15588 (1)| 00:03:08 |
| 5 | TABLE ACCESS BY INDEX ROWID| TBLNETWORKPLAN | 1 | 7 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_TBLNETWORKPLAN | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | TBLCARRIER | 1 | 21 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_TBLCARRIER | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TBLPATIENT | 1 | 33 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_TBLPATIENT | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."INTAKEID"=2335540 AND TO_NUMBER("B"."PLANLEVELCD")=1)
6 - access("B"."PLANID"="C"."PLANID")
8 - access("C"."CARID"="D"."CARID")
10 - access("A"."PATIENTNUMBER"="B"."PATIENTNUMBER")


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

SQL> /

1 row selected.

[chulett]

Could you please tell me how to optimize the query below

Typically by having a conversation with your DBA or Data Architect, people who have access / knowledge of the structures & volumetrics involved. All anyone could do here is give you generic advice about full table scans, hash joins v. nested loops and other possibly misplaced advice since we know nothing about your environment other than what you posted.

However, from a quick look at the Oracle explain plan, it would seem this particular query would be helped by adding an index over the "B.intakeid" column.