Re: Full Table Scan - Bind Variables

There are situations where Oracle's implicit type conversion rules 
prevent indexes from begin used.

To investigate, try a query along these lines:

select dump(id, 1016), dump(:id, 1016), dump(sub_id, 1016), 
dump(:sub_id, 1016)
from asset
where id = :id and sub_id = :sub_id and rownum <= 1;

The dump function will display the type, value and character set of its 
first argument. This should tell you if the column and the parameter 
have different types. If one is VARCHAR, and the other is NVARCHAR, then 
implicit conversion of the column to match the parameter will prevent 
the index from being used. Try to make the conversion apply to the 
parameter instead of the column.

While not exactly natural to write, you might try something like:
*SELECT BATCH FROM ASSET WHERE :ID = ID and :SUB_ID = SUB_ID*

While that works when mixing char and numeric types, I'm not sure if it 
will work for different string types. A better solution may be to 
explicitly coerce the query parameters to the same data type as the 
columns. There are several ways to do this, either on the python side or 
the database side.

- Doug

On 2013-04-24 21:53, Anurag Chourasia wrote:
> Dear Friends,
>
> My query is doing a full table scan when running from within the Web App.
>
> I captured the plan of execution using LAB128 and this is what i see 
> http://dpaste.com/1072780/ The plan indicates a full table scan.
>
> The query in question is *SELECT BATCH FROM ASSET WHERE ID = :ID and 
> SUB_ID = :SUB_ID*
>
> The columns in the where clause are both *varchar2* and in cx_oracle, 
> the query looks like this
>
> WMS_cursor.execute("SELECT BATCH FROM ASSET WHERE ID = :ID and SUB_ID 
> = :SUB_ID" ,{'ID':ID, 'SUB_ID':SUB_ID})
>
> ID and SUB_ID are both *unicode* type variables in Python.
>
> If i generate the plan of the same query in SQL Tools, i see that the 
> index is being used. Plan as seen in SQLTools is 
> http://dpaste.com/1072779/
>
> The index *ASSET_IND_7* is a new index that was created on this table.
>
> Why should the query do a FULL TABLE SCAN when running from inside the 
> WEB App? Is it something wrong with the Bind Variables?
>
> Please help.
>
> Regards,
> Guddu
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


-- 
Doug Henderson, Calgary, Alberta, Canada

------------------------------------------------------------------------------
Try New Relic Now & We'll Send You this Cool Shirt
New Relic is the only SaaS-based application performance monitoring service 
that delivers powerful full stack analytics. Optimize and monitor your
browser, app, & servers with just a few lines of code. Try New Relic
and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users