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

[Ethan Alpert]

Ivan,

 

Thanks for your reply. I took at look at the ogrocitablelayer.cpp code
on this ticket. The way the dims where queried in this
ogrocitablelayer.cpp attachment is very similar to what I proposed.

 

However it does not look like ogrocitablelayer.cpp was committed as a
result of this ticket.

 

-e

 

From: Ivan Lucena [mailto:ivan.lucena at pmldnet.com] 
Sent: Wednesday, September 21, 2011 2:28 PM
To: Ethan Alpert; ""
Subject: Re: [gdal-dev] Problems with OGROpen, OCI Driver
(OGROCITableLayer::ReadTableDefinition) and Oracle Views in GDAL 1.8.1

 

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                &nb sp;
*/

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

 

    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

        {< /span>

            iDim = atoi( papszResult[0] );

        }

 

        if( iDim > 0 )

        {

            SetDimension( iDim );

        }

        else

        {

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

        }

    }< o:p>

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
gdal-dev at lists.osgeo.org
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/d50e7f96/attachment-0001.html