Re: Full Table Scan - Bind Variables

Hi Doug,

Thanks a lot. You made my day. I was struggling with this.

Please have a look at http://dpaste.com/1073386/

In summary, if I change the parameter type variable from Unicode to str
then the query executes in no time at all.

Here is the result of the query with the DUMP function

>>> WMS_cursor.execute("SELECT
dump(ID,1016),dump(:ID,1016),dump(SUB_ID,1016),dump(:SUB_ID,1016) FROM
ASSETS WHERE ID = :ID and SUB_ID =:SUB_ID" ,{'ID':ID, 'SUB_ID':SUB_ID})
<__builtin__.OracleCursor on <cx_Oracle.Connection to local@local_prod>>
>>> row=WMS_cursor.fetchone()
>>> print row
('Typ=1 Len=3 CharacterSet=AL32UTF8: 30,31,32', 'Typ=1 Len=6
CharacterSet=AL16UTF16: 0,30,0,31,0,32', 'Typ=1 Len=6
CharacterSet=AL32UTF8: 32,31,37,32,30,34', 'Typ=1 Len=12
CharacterSet=AL16UTF16: 0,32,0,31,0,37,0,32,0,30,0,34')

Database column is shown to be AL32UTF8 but the bind parameter value was
AL16UTF16

I changed the parameter value to str from unicode and now it works
impeccable.

This has been very helpful. Appreciate If you would have more information
to share.

Thanks again.

Regards,
Guddu

On Thu, Apr 25, 2013 at 3:15 AM, Doug Henderson <djhender-sK6dKysfGH7D0D/[email protected]>wrote:

>  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 listcx-oracle-users-5NWGOfrQmneRv+LV9MX5ugtCRVl27V+i0wdF1cv0I5s@public.gmane.org://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
>
>

------------------------------------------------------------------------------
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