Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
2012/8/22 Jukka Rahkonen jukka.rahko...@mmmtike.fi
Hi,
I learned to use ogrinfo with --debug on lately and it revealed what
all happens after sending plain ogrinfo through OCI driver. A few
lines interest me. What is the meaning to doing the select from
ALL_SDO_GEOM_METADATA then insert into USER_SDO_GEOM_METADATA as
follows?
OCI: Prepare(select min(case when r=1 then sdo_lb else null end) minx,
min(case when r=2 then sdo_lb else null end) miny, min(case when r=1
then sdo_ub else null end) maxx, min(case when r=2 then sdo_ub else
null end) maxy from (SELECT d.sdo_dimname, d.sdo_lb, sdo_ub,
sdo_tolerance, rownum r FROM ALL_SDO_GEOM_METADATA m, table(m.diminfo)
d where m.table_name = UPPER('TABLE') and m.COLUMN_NAME =
UPPER('GEOM') ) )
OCI: Prepare(UPDATE USER_SDO_GEOM_METADATA SET DIMINFO =
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2899987.997,400.003,
0.001),MDSYS.SDO_DIM_ELEMENT('Y',587.997,800.003,0.001)) WHERE
TABLE_NAME = 'TABLE')
I guess that meaning is to check all the metadata rows from the
ALL_SDO_GEOM_METADATA view which are referring to the queried table.
There can be several rows created be different Oracle users. Next the
maximum BBOX is constructed and that is updated into
USER_SDO_GEOM_METADATA view.
It seems to me that first statement should include an OWNER filter in the
WHERE clause, or it will fetch all records for table with same name and
same geometric filedname.
Regards
stefano
---
41.95581N 12.52854E
http://www.linkedin.com/in/stefanoiacovella
http://twitter.com/#!/Iacovellas
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
Stefano Iacovella wrote: I guess that meaning is to check all the metadata rows from the ALL_SDO_GEOM_METADATA view which are referring to the queried table. There can be several rows created be different Oracle users. Next the maximum BBOX is constructed and that is updated into USER_SDO_GEOM_METADATA view. It seems to me that first statement should include an OWNER filter in the WHERE clause, or it will fetch all records for table with same name and same geometric filedname. Not really, it is normal in managed envinronment that db admins or data managers are creating and updating the tables but reporting is done with minimal rights. So reading ALL_SDO_GEOM_METADATA does make sense. And actually USER_SDO_GEOM_METADATA is a view into the same data than ALL_SDO_GEOM_METADATA filtered by the current username. Oracle holds the data in a real table named mdsys.sdo_geom_metadata_table but users cannot handle that table directly, everything goes through views and triggers. I do not know if it makes much sense really to have several metadata rows with different owners in the metadata table but it happens and it must be done sometimes. Some programs do not even try to read ALL_SDO_GEOM_METADATA. That means that developers have not worked too much in a real production environment and they are creating their test tables with some Oracle admin account and then they are reading the data with their clients with the same admin account. Bad habbit but totally understandable, it is much faster and easier so. It is OK but before finalizing with coding it would be good to make a test: What if I try to do this with limited rights? It would be good also to write it into documentation what rights and into which tables are needed. Policy in our house is that a non-admin Oracle user has by default no rights to do anything. Everything must be explicitly granted. Now we have usually learned by trial and error by adding grants one by one and following what is blocking us next. -Jukka Rahkonen- Regards stefano --- 41.95581N 12.52854E http://www.linkedin.com/in/stefanoiacovella http://twitter.com/#!/Iacovellas ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
Jukka, I'm responsible for the code the calculates the min/max extents for inserting/updating the USER_SDO_GEOM_METADATA view. This is supposed to only be run via ogr2ogr when loading/updating tables (I didn;t write that part, just the query to get those extents). There shouldn't be any updating going on in an ogrinfo call. Personally, I do think that any calls against the ALL_SDO_GEOM_METADATA should really only go against USER_SDO_GEOM_METADATA. There were a couple of tickets about 2 years ago that implemented thee changes. I'll look them up and post the tickets. Mike -- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/22/12 7:36 AM, Rahkonen Jukka jukka.rahko...@mmmtike.fi wrote: Stefano Iacovella wrote: I guess that meaning is to check all the metadata rows from the ALL_SDO_GEOM_METADATA view which are referring to the queried table. There can be several rows created be different Oracle users. Next the maximum BBOX is constructed and that is updated into USER_SDO_GEOM_METADATA view. It seems to me that first statement should include an OWNER filter in the WHERE clause, or it will fetch all records for table with same name and same geometric filedname. Not really, it is normal in managed envinronment that db admins or data managers are creating and updating the tables but reporting is done with minimal rights. So reading ALL_SDO_GEOM_METADATA does make sense. And actually USER_SDO_GEOM_METADATA is a view into the same data than ALL_SDO_GEOM_METADATA filtered by the current username. Oracle holds the data in a real table named mdsys.sdo_geom_metadata_table but users cannot handle that table directly, everything goes through views and triggers. I do not know if it makes much sense really to have several metadata rows with different owners in the metadata table but it happens and it must be done sometimes. Some programs do not even try to read ALL_SDO_GEOM_METADATA. That means that developers have not worked too much in a real production environment and they are creating their test tables with some Oracle admin account and then they are reading the data with their clients with the same admin account. Bad habbit but totally understandable, it is much faster and easier so. It is OK but before finalizing with coding it would be good to make a test: What if I try to do this with limited rights? It would be good also to write it into documentation what rights and into which tables are needed. Policy in our house is that a non-admin Oracle user has by default no rights to do anything. Everything must be explicitly granted. Now we have usually learned by trial and error by adding grants one by one and following what is blocking us next. -Jukka Rahkonen- Regards stefano --- 41.95581N 12.52854E http://www.linkedin.com/in/stefanoiacovella http://twitter.com/#!/Iacovellas ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
Actually, thinking about it, ALL_SDO_GEOM_METADATA has to be used to get the metadata info otherwise cross schema access would not be possible. Eg I'm connected as user_a but want to read data from user_b. Normal Oracle grants would control whether reading (or other operation) is possible. Mike -- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/22/12 8:01 AM, Smith, Michael ERDC-RDE-CRREL-NH michael.sm...@erdc.dren.mil wrote: Jukka, I'm responsible for the code the calculates the min/max extents for inserting/updating the USER_SDO_GEOM_METADATA view. This is supposed to only be run via ogr2ogr when loading/updating tables (I didn;t write that part, just the query to get those extents). There shouldn't be any updating going on in an ogrinfo call. Personally, I do think that any calls against the ALL_SDO_GEOM_METADATA should really only go against USER_SDO_GEOM_METADATA. There were a couple of tickets about 2 years ago that implemented thee changes. I'll look them up and post the tickets. Mike -- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/22/12 7:36 AM, Rahkonen Jukka jukka.rahko...@mmmtike.fi wrote: Stefano Iacovella wrote: I guess that meaning is to check all the metadata rows from the ALL_SDO_GEOM_METADATA view which are referring to the queried table. There can be several rows created be different Oracle users. Next the maximum BBOX is constructed and that is updated into USER_SDO_GEOM_METADATA view. It seems to me that first statement should include an OWNER filter in the WHERE clause, or it will fetch all records for table with same name and same geometric filedname. Not really, it is normal in managed envinronment that db admins or data managers are creating and updating the tables but reporting is done with minimal rights. So reading ALL_SDO_GEOM_METADATA does make sense. And actually USER_SDO_GEOM_METADATA is a view into the same data than ALL_SDO_GEOM_METADATA filtered by the current username. Oracle holds the data in a real table named mdsys.sdo_geom_metadata_table but users cannot handle that table directly, everything goes through views and triggers. I do not know if it makes much sense really to have several metadata rows with different owners in the metadata table but it happens and it must be done sometimes. Some programs do not even try to read ALL_SDO_GEOM_METADATA. That means that developers have not worked too much in a real production environment and they are creating their test tables with some Oracle admin account and then they are reading the data with their clients with the same admin account. Bad habbit but totally understandable, it is much faster and easier so. It is OK but before finalizing with coding it would be good to make a test: What if I try to do this with limited rights? It would be good also to write it into documentation what rights and into which tables are needed. Policy in our house is that a non-admin Oracle user has by default no rights to do anything. Everything must be explicitly granted. Now we have usually learned by trial and error by adding grants one by one and following what is blocking us next. -Jukka Rahkonen- Regards stefano --- 41.95581N 12.52854E http://www.linkedin.com/in/stefanoiacovella http://twitter.com/#!/Iacovellas ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
2012/8/22 Rahkonen Jukka jukka.rahko...@mmmtike.fi Stefano Iacovella wrote: I guess that meaning is to check all the metadata rows from the ALL_SDO_GEOM_METADATA view which are referring to the queried table. There can be several rows created be different Oracle users. Next the maximum BBOX is constructed and that is updated into USER_SDO_GEOM_METADATA view. It seems to me that first statement should include an OWNER filter in the WHERE clause, or it will fetch all records for table with same name and same geometric filedname. Not really, it is normal in managed envinronment that db admins or data managers are creating and updating the tables but reporting is done with minimal rights. So reading ALL_SDO_GEOM_METADATA does make sense. Yes but you may find duplicate rows for tableName if user 1 owns tableGeom and user 2 owns tableGeom and the geometry column name is the same. ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
Hi, If I do ogrinfo with --debug on against Oracle l can see updates for USER_SDO_GEOM_METADATA. Same happens if I do ogr2ogr with output to for example GML. For me is seems not to happen only with loading and updating. I have not an easy access to Oracle logs and I have studied only the OGR debug messages on my screen. -Jukka Rahkonen- Smith, Michael wrote: Jukka, I'm responsible for the code the calculates the min/max extents for inserting/updating the USER_SDO_GEOM_METADATA view. This is supposed to only be run via ogr2ogr when loading/updating tables (I didn;t write that part, just the query to get those extents). There shouldn't be any updating going on in an ogrinfo call. Personally, I do think that any calls against the ALL_SDO_GEOM_METADATA should really only go against USER_SDO_GEOM_METADATA. There were a couple of tickets about 2 years ago that implemented thee changes. I'll look them up and post the tickets. Mike -- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/22/12 7:36 AM, Rahkonen Jukka jukka.rahko...@mmmtike.fi wrote: Stefano Iacovella wrote: I guess that meaning is to check all the metadata rows from the ALL_SDO_GEOM_METADATA view which are referring to the queried table. There can be several rows created be different Oracle users. Next the maximum BBOX is constructed and that is updated into USER_SDO_GEOM_METADATA view. It seems to me that first statement should include an OWNER filter in the WHERE clause, or it will fetch all records for table with same name and same geometric filedname. Not really, it is normal in managed envinronment that db admins or data managers are creating and updating the tables but reporting is done with minimal rights. So reading ALL_SDO_GEOM_METADATA does make sense. And actually USER_SDO_GEOM_METADATA is a view into the same data than ALL_SDO_GEOM_METADATA filtered by the current username. Oracle holds the data in a real table named mdsys.sdo_geom_metadata_table but users cannot handle that table directly, everything goes through views and triggers. I do not know if it makes much sense really to have several metadata rows with different owners in the metadata table but it happens and it must be done sometimes. Some programs do not even try to read ALL_SDO_GEOM_METADATA. That means that developers have not worked too much in a real production environment and they are creating their test tables with some Oracle admin account and then they are reading the data with their clients with the same admin account. Bad habbit but totally understandable, it is much faster and easier so. It is OK but before finalizing with coding it would be good to make a test: What if I try to do this with limited rights? It would be good also to write it into documentation what rights and into which tables are needed. Policy in our house is that a non-admin Oracle user has by default no rights to do anything. Everything must be explicitly granted. Now we have usually learned by trial and error by adding grants one by one and following what is blocking us next. -Jukka Rahkonen- Regards stefano --- 41.95581N 12.52854E http://www.linkedin.com/in/stefanoiacovella http://twitter.com/#!/Iacovellas ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
Hi, This part of query takes UB min values as maxx and maxy. Wouldn't it be better to select the max values instead? min(case when r=1 then sdo_ub else null end) maxx, min(case when r=2 then sdo_ub else null end) maxy -Jukka- Smith, Michael Jukka, I'm responsible for the code the calculates the min/max extents for inserting/updating the USER_SDO_GEOM_METADATA view. This is supposed to only be run via ogr2ogr when loading/updating tables (I didn;t write that part, just the query to get those extents). There shouldn't be any updating going on in an ogrinfo call. Personally, I do think that any calls against the ALL_SDO_GEOM_METADATA should really only go against USER_SDO_GEOM_METADATA. There were a couple of tickets about 2 years ago that implemented thee changes. I'll look them up and post the tickets. Mike -- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/22/12 7:36 AM, Rahkonen Jukka jukka.rahko...@mmmtike.fi wrote: Stefano Iacovella wrote: I guess that meaning is to check all the metadata rows from the ALL_SDO_GEOM_METADATA view which are referring to the queried table. There can be several rows created be different Oracle users. Next the maximum BBOX is constructed and that is updated into USER_SDO_GEOM_METADATA view. It seems to me that first statement should include an OWNER filter in the WHERE clause, or it will fetch all records for table with same name and same geometric filedname. Not really, it is normal in managed envinronment that db admins or data managers are creating and updating the tables but reporting is done with minimal rights. So reading ALL_SDO_GEOM_METADATA does make sense. And actually USER_SDO_GEOM_METADATA is a view into the same data than ALL_SDO_GEOM_METADATA filtered by the current username. Oracle holds the data in a real table named mdsys.sdo_geom_metadata_table but users cannot handle that table directly, everything goes through views and triggers. I do not know if it makes much sense really to have several metadata rows with different owners in the metadata table but it happens and it must be done sometimes. Some programs do not even try to read ALL_SDO_GEOM_METADATA. That means that developers have not worked too much in a real production environment and they are creating their test tables with some Oracle admin account and then they are reading the data with their clients with the same admin account. Bad habbit but totally understandable, it is much faster and easier so. It is OK but before finalizing with coding it would be good to make a test: What if I try to do this with limited rights? It would be good also to write it into documentation what rights and into which tables are needed. Policy in our house is that a non-admin Oracle user has by default no rights to do anything. Everything must be explicitly granted. Now we have usually learned by trial and error by adding grants one by one and following what is blocking us next. -Jukka Rahkonen- Regards stefano --- 41.95581N 12.52854E http://www.linkedin.com/in/stefanoiacovella http://twitter.com/#!/Iacovellas ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] When and why ogrinfo sends update for Oracle?
Jukka, Actually, it doesn't matter since only one value is used in the query, min or max return the same result. Its just using the min function with the group by to aggregate the values into one row. All the other values are null. Mike -- Michael Smith Remote Sensing/GIS Center US Army Corps of Engineers On 8/22/12 8:41 AM, Rahkonen Jukka jukka.rahko...@mmmtike.fi wrote: Hi, This part of query takes UB min values as maxx and maxy. Wouldn't it be better to select the max values instead? min(case when r=1 then sdo_ub else null end) maxx, min(case when r=2 then sdo_ub else null end) maxy -Jukka- Smith, Michael Jukka, I'm responsible for the code the calculates the min/max extents for inserting/updating the USER_SDO_GEOM_METADATA view. This is supposed to only be run via ogr2ogr when loading/updating tables (I didn;t write that part, just the query to get those extents). There shouldn't be any updating going on in an ogrinfo call. Personally, I do think that any calls against the ALL_SDO_GEOM_METADATA should really only go against USER_SDO_GEOM_METADATA. There were a couple of tickets about 2 years ago that implemented thee changes. I'll look them up and post the tickets. Mike -- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/22/12 7:36 AM, Rahkonen Jukka jukka.rahko...@mmmtike.fi wrote: Stefano Iacovella wrote: I guess that meaning is to check all the metadata rows from the ALL_SDO_GEOM_METADATA view which are referring to the queried table. There can be several rows created be different Oracle users. Next the maximum BBOX is constructed and that is updated into USER_SDO_GEOM_METADATA view. It seems to me that first statement should include an OWNER filter in the WHERE clause, or it will fetch all records for table with same name and same geometric filedname. Not really, it is normal in managed envinronment that db admins or data managers are creating and updating the tables but reporting is done with minimal rights. So reading ALL_SDO_GEOM_METADATA does make sense. And actually USER_SDO_GEOM_METADATA is a view into the same data than ALL_SDO_GEOM_METADATA filtered by the current username. Oracle holds the data in a real table named mdsys.sdo_geom_metadata_table but users cannot handle that table directly, everything goes through views and triggers. I do not know if it makes much sense really to have several metadata rows with different owners in the metadata table but it happens and it must be done sometimes. Some programs do not even try to read ALL_SDO_GEOM_METADATA. That means that developers have not worked too much in a real production environment and they are creating their test tables with some Oracle admin account and then they are reading the data with their clients with the same admin account. Bad habbit but totally understandable, it is much faster and easier so. It is OK but before finalizing with coding it would be good to make a test: What if I try to do this with limited rights? It would be good also to write it into documentation what rights and into which tables are needed. Policy in our house is that a non-admin Oracle user has by default no rights to do anything. Everything must be explicitly granted. Now we have usually learned by trial and error by adding grants one by one and following what is blocking us next. -Jukka Rahkonen- Regards stefano --- 41.95581N 12.52854E http://www.linkedin.com/in/stefanoiacovella http://twitter.com/#!/Iacovellas ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
