| Again Execution Plan after Analyzing the table [message #65697] |
Wed, 08 December 2004 20:47 |
Gurinder Mann
Messages: 7
Registered: October 2004
|
Junior Member |
|
|
HI Frank/Mahesh
I am again sending the execution plan
after analyzing the table but as i told earlier also
that my query is retreving only 207 records out of 159241
Records in the table so
its cost is becomg very high when
every user starts search on
Name please help to get out of this problem.
SQL> SET AUTOT TRACEONLY EXP STAT
SQL> SELECT
2 opno,Name,Ward,Address1 Home, Address3 Atol ,
3 Place Island,cancel
4 FROM
5 PMOPDetails
6 WHERE
7 Upper(Name) LIKE 'AH% HUS%';
207 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=979 Card=7895 Bytes=
386855)
1 0 TABLE ACCESS (FULL) OF 'PMOPDETAILS' (Cost=979 Card=7895 B
ytes=386855)
Statistics
----------------------------------------------------------
1019 recursive calls
6 db block gets
6781 consistent gets
6526 physical reads
0 redo size
20423 bytes sent via SQL*Net to client
2206 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
207 rows processed
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 opno,Name,Ward,Address1 Home, Address3 Atol ,
3 Place Island,cancel
4 FROM
5 PMOPDetails
6 WHERE
7* Upper(Name) LIKE 'AH% HUS_%'
SQL> /
207 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=7895 Bytes=3
86855)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PMOPDETAILS' (Cost=10 Ca
rd=7895 Bytes=386855)
2 1 INDEX (RANGE SCAN) OF 'PMOPDETAILS_NAME' (NON-UNIQUE) (C
ost=2 Card=7895)
Statistics
----------------------------------------------------------
63 recursive calls
0 db block gets
323 consistent gets
85 physical reads
0 redo size
18773 bytes sent via SQL*Net to client
2206 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
207 rows processed
SQL> SPOOL OFF
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
