Re: [gdal-dev] Problems with OGROpen, OCI Driver (OGROCITableLayer::ReadTableDefinition) and Oracle Views in GDAL 1.8.1

[Ivan Lucena]

Ethan,


That code has changed a little bit since last time I saw it on
http://trac.osgeo.org/gdal/ticket/3025 but I can take a look on that for
you and commit with your suggestion if that works well.


Regards,


Ivan


	-------Original Message-------
  From: Ethan Alpert
  To: gdal-dev at lists.osgeo.org
  Subject: [gdal-dev] Problems with OGROpen, OCI Driver
(OGROCITableLayer::ReadTableDefinition) and Oracle Views in GDAL 1.8.1
  Sent: Sep 21 '11 13:48


All,


I’ve run into some very serious problems (for me) with changes made to the
OCI driver for 1.8.1. Specifically lines the following lines (270-274).


        oDimCmd.Append( "SELECT a." );

        oDimCmd.Append( pszGeomName  );

        oDimCmd.Append( ".GET_DIMS() DIM FROM " );

        oDimCmd.Append( pszTableName );

        oDimCmd.Append( " a WHERE ROWNUM = 1" );


Our schema has many large  tables and several views which use these tables.
The views are referenced in all_sdo_geom_metadata. When the query “select
a.geometry.get_dims() dim from a  where rownum = 1” is run it results in
FULL Table accesses. This has result in running sorted queries on very
large tables and I managed to blow out the temp table space on our oracle
instance just calling OGROpen. To say the least our DBA’s are not happy
with me.


I’m considering changing :


        oDimCmd.Append( "SELECT a." );

        oDimCmd.Append( pszGeomName  );

        oDimCmd.Append( ".GET_DIMS() DIM FROM " );

        oDimCmd.Append( pszTableName );

        oDimCmd.Append( " a WHERE ROWNUM = 1" );


to:


        oDimCmd.Append( "SELECT COUNT(t.SDO_DIMNAME) DIM FROM
ALL_SDO_GEOM_METADATA A, TABLE(A.DIMINFO) T WHERE a.TABLE_HAME=’" );

        oDimCmd.Append( pszTableName );

        oDimCmd.Append( "’" );


The following code was not in 1.7.2.


"ogrocitablelayer.cpp" Lines 259-316

   /* --------------------------------------------------------------------
*/

    /*      Identify Geometry dimension
*/

    /* --------------------------------------------------------------------
*/


    if( pszGeomName != NULL && strlen(pszGeomName) > 0 )

    {

        OGROCIStringBuf oDimCmd;

        OGROCIStatement oDimStatement( poSession );

        char **papszResult;

        int iDim = -1;


        ODIMCMD.APPEND( "SELECT A." );

        ODIMCMD.APPEND( PSZGEOMNAME  );

        ODIMCMD.APPEND( ".GET_DIMS() DIM FROM " );

        ODIMCMD.APPEND( PSZTABLENAME );

        ODIMCMD.APPEND( " A WHERE ROWNUM = 1" );


        oDimStatement.Execute( oDimCmd.GetString() );


        papszResult = oDimStatement.SimpleFetchRow();


        if( CSLCount(papszResult) < 1 )

        {

            OGROCIStringBuf oDimCmd2;

            OGROCIStatement oDimStatement2( poSession );

            char **papszResult2;


            oDimCmd2.Appendf( 1024,

                "select m.sdo_index_dims\n"

                "from   all_sdo_index_metadata m, all_sdo_index_info i\n"

                "where  i.index_name = m.sdo_index_name\n"

                "   and i.sdo_index_owner = m.sdo_index_owner\n"

                "   and i.table_name = upper('%s')",

                pszTableName );


            oDimStatement2.Execute( oDimCmd2.GetString() );


            papszResult2 = oDimStatement2.SimpleFetchRow();


            if( CSLCount( papszResult2 ) > 0 )

            {

                iDim = atoi( papszResult2[0] );

            }

        }

        else

        {

            iDim = atoi( papszResult[0] );

        }


        if( iDim > 0 )

        {

            SetDimension( iDim );

        }

        else

        {

            CPLDebug( "OCI", "get dim based of existing data or index
failed." );

        }

    }

This electronic communication and any attachments may contain confidential
and proprietary   information of DigitalGlobe, Inc. If you are not the
intended recipient, or an agent or employee   responsible for delivering
this communication to the intended recipient, or if you have received
this communication in error, please do not print, copy, retransmit,
disseminate or   otherwise use the information. Please indicate to the
sender that you have received this   communication in error, and delete the
copy you received. DigitalGlobe reserves the   right to monitor any
electronic communication sent or received by its employees, agents   or
representatives.
          --------------------
_______________________________________________
  gdal-dev mailing list
  [LINK: compose.php?to=gdal-dev at lists.osgeo.org] gdal-dev at lists.osgeo.org
  [LINK: http://lists.osgeo.org/mailman/listinfo/gdal-dev]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20110921/51cb4db1/attachment.html