Home » RDBMS Server » Performance Tuning » Which one is to choose? (Oracle9i)
Which one is to choose? [message #345165] Tue, 02 September 2008 08:14 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
For a particular query following below the plan after making changes in query.

Plan got  for the condition like '%.zip' in where clause


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |     1 |    37 |   167 |
|   1 |  SORT GROUP BY               |                             |     1 |    37 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSFER_DATA_TBL           |     1 |    29 |     4 |
|   3 |    NESTED LOOPS              |                             |     1 |    37 |   167 |
|   4 |     INDEX FAST FULL SCAN     | I3_C_ITEM                   |     1 |     8 |   163 |
|   5 |     INDEX RANGE SCAN         | TRANSFER_DATA_TBL_PKEY      |     1 |       |     2 |
--------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

Plan got after creating a FBI and replacing like  by substr
Using FBI (substr(col,-4) I am getting below explain plan and response time s for the query is less here



---------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     1 |    37 |   166 |
|   1 |  SORT GROUP BY                |                             |     1 |    37 |       |
|   2 |   HASH JOIN                   |                             |     1 |    37 |   166 |
|   3 |    INDEX FAST FULL SCAN       | I3_C_ITEM       	    |     1 |     8 |   163 |
|   4 |    TABLE ACCESS BY INDEX ROWID| TRANSFER_DATA_TBL           |  7895 |   223K|     2 |
|   5 |     INDEX RANGE SCAN          | I4_TRANSFER_DATA_TBL        |  3196 |       |     1 |
---------------------------------------------------------------------------------------------



Want to know which plan would is better?

Thanks in adavance,
Oli



Re: Which one is to choose? [message #345172 is a reply to message #345165] Tue, 02 September 2008 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Want to know which plan would is better?

Better based upon which quantifiable metric?
Re: Which one is to choose? [message #345514 is a reply to message #345172] Wed, 03 September 2008 22:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Personally, I would pick the faster one
Re: Which one is to choose? [message #345539 is a reply to message #345514] Thu, 04 September 2008 01:32 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
rleishman wrote on Wed, 03 September 2008 22:36
Personally, I would pick the faster one


@rleishman
Thanks for that opinion.Thats what I wanted to know.


Regards,
Oli
Previous Topic: alternative for DISTINCT
Next Topic: Index not being used for Min/Max
Goto Forum:
  


Current Time: Wed Dec 07 14:27:16 CST 2016

Total time taken to generate the page: 0.22240 seconds