OraFAQ Forum: Performance Tuning » Alternative way to avoid full scan?

Home » RDBMS Server » Performance Tuning » Alternative way to avoid full scan? (Oracle9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alternative way to avoid full scan? [message #345116]

Tue, 02 September 2008 06:22

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

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345122 is a reply to message #345116]

Tue, 02 September 2008 06:33

Michel Cadot
Messages: 68665
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

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

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345126 is a reply to message #345116]

Tue, 02 September 2008 06:43

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

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345133 is a reply to message #345126]

Tue, 02 September 2008 06:50

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345136 is a reply to message #345133]

Tue, 02 September 2008 06:55

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

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345142 is a reply to message #345136]

Tue, 02 September 2008 07:08

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345146 is a reply to message #345142]

Tue, 02 September 2008 07:17

Olivia
Messages: 519
Registered: June 2008

Senior Member

Thanks Michel It worked Smile

The query is giving much faster response now..

Regards
Oli

Report message to a moderator

Re: Alternative way to avoid full scan? [message #345156 is a reply to message #345116]

Tue, 02 September 2008 07:35

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 16:14:44 CDT 2024