Home » RDBMS Server » Performance Tuning » Alternative way to avoid full scan? (Oracle9i)
Alternative way to avoid full scan? [message #345116] Tue, 02 September 2008 06:22 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I sthere any alternative way for


 like '%.zip' 
as using it in where clause uses FULL Table scan

Regards,
Oli
Re: Alternative way to avoid full scan? [message #345122 is a reply to message #345116] Tue, 02 September 2008 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64128
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
contains(col,'.zip') > 0

You can also create a FBI on "substr(col,-4)" and text this.

Regards
Michel

[Updated on: Tue, 02 September 2008 06:36]

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345124 is a reply to message #345122] Tue, 02 September 2008 06:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel. But if the column we are using in instr is an index than will it be good to use it in the query?

Regards,
Oli
Re: Alternative way to avoid full scan? [message #345126 is a reply to message #345116] Tue, 02 September 2008 06:43 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@Michel

Quote:

You can also create a FBI on "substr(col,-4)" and text this.




Didn't get what you mean.


Regards,
Oli
Re: Alternative way to avoid full scan? [message #345133 is a reply to message #345126] Tue, 02 September 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64128
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Should be "test" not "text".

create index t_i on t (substr(col,-4));

then

substr(col,-4)='.zip'

Regards
Michel
Re: Alternative way to avoid full scan? [message #345136 is a reply to message #345133] Tue, 02 September 2008 06:55 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the suggesion.
The column that has been used there index has already been created.Can I create function based index for the same?


Regards,
Oli
Re: Alternative way to avoid full scan? [message #345142 is a reply to message #345136] Tue, 02 September 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64128
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
Now it depends on your queries, treatments, and so on.
An index is not without any performances impact on update/insert/delete.

Regards
Michel
Re: Alternative way to avoid full scan? [message #345146 is a reply to message #345142] Tue, 02 September 2008 07:17 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel It worked Smile
The query is giving much faster response now..



Regards
Oli
Re: Alternative way to avoid full scan? [message #345156 is a reply to message #345116] Tue, 02 September 2008 07:35 Go to previous 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

* Corrected: Plan wrongly copied earlier

[Updated on: Tue, 02 September 2008 07:45]

Report message to a moderator

Previous Topic: Query to check statistics are up to date
Next Topic: UPDATE ISSUE
Goto Forum:
  


Current Time: Wed Dec 07 06:48:18 CST 2016

Total time taken to generate the page: 0.05804 seconds