[fdo-users] big oracle describe schema performance boost
Haris Kurtagic
haris at sl-king.com
Tue Aug 26 11:14:02 EDT 2008
I guess I am not clear enough but will try again :)
King.Oracle provider when creating FDO class name will use Oracle schema
name, table name and geometry column name to create FDO class name. It
will use fixed name "kingora" for FDO schema name. That schema name and
class name can be overridden by user using KingFdoClass table.
When provider needs to get Oracle schema table and geometry column it
will not parse Fdo class name but will map FDO class name to Oracle
schema,table and geometry column. Mapping is done using physical schema
mapping , perhaps easiest if you look into code.
Correct me if I am wrong but I think you are talking about GetClasses
from MapGuide API not from FDO API. GetClass is not used to generate FDO
classes from data source but FDO interface describe schema is used.
FDO interface works in such way that FDO client calls describe schema
interface and that is when FDO classes are created.
Describe schema will return FDO schema collection which is then used by
i.e. MapGuide. This FDO Schema collection is cached in MapGuide.
With proposed changes FDO client will be able to call describe schema
just for selected classes instead of all classes as it was case up to
now.
Haris
-----Original Message-----
From: fdo-users-bounces at lists.osgeo.org
[mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
Sent: Tuesday, August 26, 2008 2:48 PM
To: FDO Users Mail List
Subject: Re: [fdo-users] big oracle describe schema performance boost
On Tue, Aug 26, 2008 at 9:32 PM, Haris Kurtagic <haris at sl-king.com>
wrote:
> 1. Perhaps using Oracle schema names for generated FDO schemas could
> help in understanding it faster.
It took me quite a while to get my head around the schemas, because they
didn't
fit the model I was used to / expected. To be honest it was more trial
and error to start
with, I never could quite understand what schema's in FDO as i
expected them to be
Oracle Schema names. Then i discovered by accident with fdo2fdo that
SDF's did behave
like I expected oracle to, with multiple schemas. you can store an
entire database
in SDF's ... ok size limits may apply but you get the idea.
So user:table :)
> I think you are wrong here, there is nothing like "suspicious"
> name for FDO class.
see below
> Provider has exact and simple way to determine what Oracle table is
> used for that FDO class and that has nothing to do with how name of
FDO
> class is generated.
So to paraphrase as that what i understand what you mean,
the provider can take a fdo class name and decode it back into
user.table.geom_col
but that has nothing to do with
the provider can encode "user.table.geom_col" into a fdo class name ?
i'm confused
>
> 2. "kingora" doesn't mean "whole accessible db".
the kingora schema contains all the indexed spatial tables in the
database that the user can access unless the connection is
limited to single schema?
>
> New RFCs for MG and FDO have new parameters for DescribeSchema which
> will allow to request only for clasees of interest. I suppose MG will
be
> changed that instead of calling DescribeSchema for whole, MG will call
> DescribeSchema with parameters set for only those classes used in
> Layers.
>
> I perhaps don't understand your questions about multiple FDO schemas
but
> King.Oracle provider will return one or many FDO schemas. It depends
on
> connection properties. Anyhow it doesn't change in any way how FDO
> providers works if there is one or many FDO schemas.
i have only ever seen two fdo schema's from king.oracle: kingora and
kingfdoclass
It would change the behavior a little because you can then ask the
provider for all tables
from base ie GetClasses('BASE') without either parsing and filtering
all the (expensive)
classnames or using connection strings.
It would encouage fdo clients to actually support using schema for
better performace
Zac
>
> Haris
>
> -----Original Message-----
> From: fdo-users-bounces at lists.osgeo.org
> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
> Sent: Tuesday, August 26, 2008 12:39 PM
> To: FDO Users Mail List
> Subject: Re: [fdo-users] big oracle describe schema performance boost
>
> On Tue, Aug 26, 2008 at 8:07 PM, Haris Kurtagic <haris at sl-king.com>
> wrote:
>> 1. Hm, I could be repeating myself but yes provider can return many
> FDO
>> schema's. And King.Oracle provider does return multiple schemas.
>> For me,d ecision to have all automatically created FDO class names in
> one FDO
>> schema doesn't sound wrong. That is not main and only reason for
using
>> such FDO class naming procedure as King.Oracle uses.
>>
>> I don't understand what you mean by " breaking the commonly
understood
>> existing model" ?
>
> I'm just saying it's pretty common to structure your database by
> placing different
> datasets in different schemas, it's more useable, it easier to browse
by
> schema
> than thru a single list every table in the database...
>
> can't you understand it's just frustrating and confusing that it's not
> simple to understand
> like JDBC or ODBC,
>
> Having to deal with another extra layer of "automatically created FDO
> class names"
> to deal with, which may or may not match after a create and the next
> select statement,
> which aren't parsed, but may be parsed coz they look suspciously like
> schema~table~geom
> but they might not be because there's this KingFdoClass table
involved?
>
> phew,
>
>> If you have big database with many spatial tables and you want to use
>> only some of them you can put the them in one schema or use just
> tables
>> listed in KingFdoClass.
>>
>>
>> Regarding performance and schema users.., FDO client was requesting
> from
>> provider to describe all classes in db. That was request from FDO
> client
>> and provider should not be smarter than the one using it.
>
> that's only because the provider has only ever has one option for
> filtering classes,
> schemas, "kingora" which is the whole accessible db
>
> I just checked using the MG webtier test, when using GetClasses
against
> a king
> oracle featuresource, you can pass "anything" in as schema name and
> always get
> back the kingOra schema
>
>> With latest RFC MapGuide and other FDO clients will be able to
request
>> from provider to describe just FDO classes of interest for that
> client.
>> That will be improvement in speed.
>
> Doesn't that RFC relies on the use of multiple schemas?
>
> GetClassNames takes schemaName as a parameter, the RFC won't help much
> because
> you can only ever issue GetClassNames("KingOra");
>
> Zac
>
>>
>> But still when FDO clients is executing describe classes of all
tables
>> in DB that is exactly what provider needs to do.
>>
>> Haris
>>
>>
>>
>>
>> -----Original Message-----
>> From: fdo-users-bounces at lists.osgeo.org
>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
>> Sent: Tuesday, August 26, 2008 11:05 AM
>> To: FDO Users Mail List
>> Subject: Re: [fdo-users] big oracle describe schema performance boost
>>
>> On Tue, Aug 26, 2008 at 6:31 PM, Haris Kurtagic <haris at sl-king.com>
>> wrote:
>>> 1. Provider could test for multiple geometries in table and use
> Oracle
>>> table name for FDO class name but there would be still case with
>> another
>>> Oracle table with same name but in different Oracle Schemas. Afaian
>> That
>>> case can be tested again or provider could be changed so he will use
>>> Oracle schema names for FDO schema names. But all that doesn't sound
>>> very appealing to me. Truth is that ~ sign in class name and fact
> that
>>> provider will not return same FDO class name for new class created
by
>>> apply schema are also not good. Right now solution to this is using
>>> extra metadata table ( KingFdoClass ). Perhaps some other way...
>>
>> This problem "Oracle table with same name but in different Oracle
>> Schemas"
>> is only a result of not mapping the oracle schemas into individual
FDO
>> schema's....
>>
>> GetSchema returns a stringcollection for a reason, not a single
string
>>
>> By not using the oracle ones and just having "kingora" the multiple
>> table naming problem exists.
>>
>>> 2. When FDO class from Oracle tables are created King.Oracle
provider
>>> keeps "schema overrides" so he knows which FDO class maps to which
>>> Oracle table ( no need to parse from fdo class name ). If in
>> connection
>>> is used KingFdoClass table than FDO class name can be set by user
and
>> it
>>> can be whatever. Provider is also keeping geometry column overrides
> in
>>> case when geometry column is created from table number fields in
that
>>> case also name of geometry column doesn't match Oracle column.
>>>
>>> 3. I am not sure if I understand your point here, but Oracle schema
>> and
>>> Fdo schema doesn't need to be related in any way. I haven't looked
in
>>> exact function " selectFeatures" but many MG functions will expect
>> class
>>> name as just FDO class name or fully qualified FDO class name which
>>> includes FDO schema name. But in any case FDO schema name is not
>> related
>>> to Oracle schema. If you use only FDO class name without FDO schema
>> name
>>> it will work if you have only one class with that name in FDO
> schemas.
>>> But mapping FDO class to Oracle schema.table name is separate
process
>>> and in case of King.Oracle provider is done as I was trying to
>> describe
>>> in point 2.
>>
>> They don't need to be related, but there should be a good reason for
>> breaking the
>> commonly understood existing model... and if it's that good, it
>> shouldn't cause other problems
>> like the describe schema issue performance and the duplicate table
> name
>> problem
>>
>> If I have a DBA login on a big spatial database with lots of schemas
>> and tables,
>> All of those tables have to be slowly Described.
>>
>> Thru the lack of real schemas, I can't log in with a DBA login and
>> access just my own
>> tables quickly because the provider will access the entire spatial
>> database metatdata
>> via the _all views.
>>
>> Zac
>>
>>>
>>> Haris
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: fdo-users-bounces at lists.osgeo.org
>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
>>> Sent: Tuesday, August 26, 2008 3:59 AM
>>> To: FDO Users Mail List
>>> Subject: Re: [fdo-users] big oracle describe schema performance
boost
>>>
>>> On Tue, Aug 26, 2008 at 3:56 AM, Haris Kurtagic <haris at sl-king.com>
>>> wrote:
>>>> 1. I don't quite understand your question. In Oracle, table can
have
>>>> many indexed geometry columns. FDO class can have many geometry
>>>> properties but for each FDO class one geometry property is set as
>>>> primary. Single geometry per table is most used case but having
more
>>>> than one is not so rare and provider has to support those cases
too.
>>>
>>> ok, I understand now.
>>>
>>> Couldn't the provider support using just table name except when
there
>>> are two
>>> or more geometries per table? when there are multiple geometry
> columns
>>> and the user attempts to access by it purely by a table only
> classname
>>> it will throw an error
>>> "Multiple geometry columns found in table, please include specify
>>> primary geom column in className"
>>>
>>> getClasses would then return, tablename for single and tablename
> ~geom
>>> for multiple
>>>
>>>> 2. In link you send me , I think it was suggested that database
>>>> schema/table name is parsed from FDO class name. That is not case.
>>>> Provider maps FDO class name to table name by keep schema
overrides.
>>>> That allows users to set any FDO class name with KingFdoClass
table.
>>>
>>> but when there's are no schema overrides how does the provider map
> the
>>> class name to schema/table/geom?
>>>
>>> sorry if I seem to be keep asking the same question, but it just
>>> doesn't make sense to me at the moment,
>>>
>>> In a schema override free connection, your saying that the classname
>>> doesn't (always)
>>> match to "KingOra:schema~table~geomcolumn" but it sure looks that
way
>>>
>>>> 3. Provider exposes in schema KingOra by default but also schemas
> set
>>> by
>>>> user using KingFdoClass table. I was thinking of using Oracle
schema
>>>> names for FDO schema names but I decided with approach that by
>> default
>>>> all FDO classes will go into on KingOra FDO schema. I don't see how
>>> that
>>>> influence portability. If there is class Kingora.Region or
>> Ora1.Region
>>>> when it is copied to SHP it becomes SHP.Region.
>>>
>>> My point there was that because MGfeatureReader selectFeatures has
no
>>> schemaName
>>> as a parameter, the provider cannot use the zac:region~geom as the
>>> schemaName isn't
>>> passed as a parameter, it will always be passed a 'region~geom'
hence
>>> schema's need to
>>> be in the class names aka 'zac~region~geom'
>>>
>>> I think FDO is showing it's file based origins...
>>>
>>> Take the example of moving data from king sql server to king oracle,
>>> at the moment
>>> the same dataset under same user name will have a different
>>> featureName/class despite
>>> using FDO which is meant to *abstract* the differences between
>>> datasources
>>>
>>> Ignoring single schema providers like SHP just for the moment,
>>> shouldn't FDO allow the same
>>> dataset to reside in sqlserver, Oracle, PostGIS, Mysql and SDF and
be
>>> accessed using
>>> the exactly the same feature / class names.
>>>
>>> In mapguide studio when you change the feature source for a layer,
>>> unless the classes & schema names
>>> are identical you lose all your layer styling, you can edit the xml
>> but
>>> that's
>>>
>>> the performance issue that I thread started with relates to that as
>>> well. By using kingora
>>> you compress an entire database into a single schema, which can be
>>> huge depending
>>> on the users access.
>>>
>>>> I agree with you, that it is good to be able to easily move data
>>> around.
>>>> Also it is not good in King.Oracle provider that when you create
new
>>>> class it will not have same name when you read it back if you are
> not
>>>> using additional metadata tables in Oracle.
>>>
>>> Yeah it doesn't sound good, can you provide an example?
>>>
>>> If i create a class called zac~region~geom with out any metadata
>>> it will always live in zac.region with a geom col right?
>>>
>>>> But truth is also that there are cases when you don't want to have
>>>> additional metadata tables.
>>>>
>>>> Right now I am working on FDO KML provider and I have similar
>> problems
>>>> how to set FDO schema.class name. Additional problem is when client
>>>> application will create FDO class for new KML file (apply schema).
>>>> For KML provider I decided to go with configuration file in which
> you
>>>> will describe what will be name of class and other parameters.
>>>
>>> z
>>>
>>>>
>>>> Haris
>>>>
>>>> -----Original Message-----
>>>> From: fdo-users-bounces at lists.osgeo.org
>>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
>>>> Sent: Monday, August 25, 2008 3:58 PM
>>>> To: FDO Users Mail List
>>>> Subject: Re: [fdo-users] big oracle describe schema performance
> boost
>>>>
>>>> On Mon, Aug 25, 2008 at 11:26 PM, Haris Kurtagic
<haris at sl-king.com>
>>>> wrote:
>>>>> Zac,
>>>>>
>>>>> I have quickly read that discussion, I haven't see it before.
>>>>>
>>>>> I will try to explain myself with example:
>>>>> In case there are two oracle schemas "Ora1" and "Ora2" and two
>>>> geometry
>>>>> tables "Region" (one in each oracle schema) and each table has two
>>>>> geometry columns "Geom1" and "Geom2".
>>>>> So in Oracle we have 4 geometries which are accessed as:
>>>>> "Ora1.Region.Geom1", "Ora1.Region.Geom2", "Ora2.Region.Geom1",
>>>>> "Ora2.Region.Geom2"
>>>>>
>>>>> So provider needs to create 4 different FDO classes and obviously
>> FDO
>>>>> class name can't be just oracle table name "Region".
>>>>
>>>> isn't FDO primarily a table approach, in studio there is always a
>> drop
>>>> down to
>>>> select which geometry column to use.. why does a table make it two
>>>> logical feature
>>>> classes when they are just one?
>>>>
>>>> Is their an assumed primary geometry on a featureclass ?
>>>>
>>>> a single indexed geometry per table is by far the most common use
> out
>>>> there
>>>>
>>>>> Provider keeps physical mapping to real oracle schema.table ( it
is
>>>> not
>>>>> parsed from name ).
>>>>
>>>> i don't understand, not parsing implies not using the class name?
>>>>
>>>>>
>>>>> Oracle username in connection doesn't mean that we access only
that
>>>>> Oracle schema. One user can have access to several Oracle schemas.
>>>>
>>>> But king oracle only ever exposes everything as single schema, that
>>>> being kingOra,
>>>> which makes portability difficult between datasources.
>>>>
>>>> shouldn't getschema's return a list of all the schemas available to
>>>> the current user in oracle?
>>>>
>>>> Looking at the Mapguide API i can see why it's implemented the way
> it
>>> is
>>>>
>>>> virtual MgFeatureReader SelectFeatures(MgResourceIdentifier
> resource,
>>>> string className, MgFeatureQueryOptions options);
>>>>
>>>> shouldn't there be a schemaName in there, that's why you used the
>> full
>>>> approach ?
>>>>
>>>>> As mentioned in discussion it can be problem to keep same
>>>> schema.class
>>>>> name across provider.
>>>>> For example with SHP provider schema is always SHP so it is
problem
>>>> when
>>>>> creating application to work over all providers with preset
>>>> schema.class
>>>>> names.
>>>>
>>>> but then the shape provider could just ignore schema altogether
>>>>
>>>> I have lots of sdf's which have kingora: schemas, it's a good way
to
>>>> be able simply swap
>>>> between oracle and sdf.
>>>>
>>>>>
>>>>> With King.Oracle provider only way to get same FDO class name as
> you
>>>>> used in create class is to have extra table (KingFdoClass). I
think
>>>> that
>>>>> would solve Bruno problem with class names for King.Oracle.
>>>>>
>>>>> Haris
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: fdo-users-bounces at lists.osgeo.org
>>>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac
Spitzer
>>>>> Sent: Monday, August 25, 2008 2:06 PM
>>>>> To: FDO Users Mail List
>>>>> Subject: Re: [fdo-users] big oracle describe schema performance
>> boost
>>>>>
>>>>> On Mon, Aug 25, 2008 at 9:34 PM, Haris Kurtagic
<haris at sl-king.com>
>>>>> wrote:
>>>>>>
>>>>>> I think that automatically creating FDO Schema and Class name
from
>>>>>> Oracle schema/table/column is not straight forward .
>>>>>
>>>>> I can't see why at least the schema part is not done fdo style
>>>>>
>>>>> ZAC:REGION~GEOMETRY
>>>>>
>>>>> but why is the geometry column needed in the first place?
>>>>>
>>>>> this was previously discussed here
>>>>>
>>>>
>>>
>>
>
http://www.nabble.com/Is-there-a-Feature-class-naming-convention-td15021
>>>>> 175.html
>>>>> http://trac.osgeo.org/fdo/ticket/374
>>>>>
>>>>> z
>>>>>
>>>>>
>>>>>> For example in case you mentioned in Oracle table Region there
>> could
>>>>> be
>>>>>> more than one geometry column.
>>>>>> Also there could be different Region tables in different Oracle
>>>>> Schemas.
>>>>>>
>>>>>> King.Oracle added one table in which you can override names FDO
>>>>>> Schema.Class names created by provider.
>>>>>> One of things I was considering to add is to allow that this
> naming
>>>>>> rules can be written in local xml configuration file. One of
>>>>> advantages
>>>>>> would be that it is not necessary to add table to Oracle.
>>>>>>
>>>>>> I think that bigger problem than that query itself is that for
>> every
>>>>>> geometry table provider runs 2 more SQL statements.
>>>>>> In that case big improvements for MapGuide will be when MapGuide
>>> will
>>>>>> request FDO schema only for classes he needs to know about.
>>>>>>
>>>>>> I will check sequence you mentioned and thank you for your
>> analyses.
>>>>>>
>>>>>> Haris
>>>>>>
>>>>>>
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: fdo-users-bounces at lists.osgeo.org
>>>>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac
> Spitzer
>>>>>> Sent: Monday, August 25, 2008 11:16 AM
>>>>>> To: FDO Users Mail List
>>>>>> Subject: Re: [fdo-users] big oracle describe schema performance
>>> boost
>>>>>>
>>>>>> oops, i forgot about them being system tables.. sorry
>>>>>>
>>>>>> in regards to having tables/views in different schema, isn't that
>>>> what
>>>>>> fdo schema's aren't for?
>>>>>>
>>>>>> ie
>>>>>> ZAC:REGION
>>>>>> rather than
>>>>>> KingOra:ZAC~REGION~GEOMETRY
>>>>>>
>>>>>> if that was the case then, if the fdo schema is the current user,
>>> use
>>>>>> the faster USER_ approach,
>>>>>> otherwise fall back on the slower ALL_ views for when the user
>>>>>> explicity asks for something
>>>>>> which isn't in the default schema.
>>>>>>
>>>>>> The performance gets worse and worse depending on the number of
>>>>>> objects in the oracle database
>>>>>>
>>>>>> The following is a solution for the sequence problem
>>>>>>
>>>>>> decode(s.sequence_owner,a.owner,s.sequence_name,null)
>> sequence_name,
>>>>>>
>>>>>> z
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Sat, Aug 23, 2008 at 12:36 AM, Barbara Zoladek
>>>>>> <barbara.zoladek at autodesk.com> wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> Using USER_* views is possible only if the connected user and
the
>>>>>> schema are the same. ALTER SESSION SET CURRENT_SCHEMA sets the
>>>> current
>>>>>> schema so you don't need to qualify the objects with the schema
>>> name,
>>>>>> but it does not change the session user. Another reason for
using
>>>>> ALL_*
>>>>>> views rather than USER_* is that you may have tables/views in
>>>>> different
>>>>>> schema rather than in one.
>>>>>>> USER_* views are based on the same objects as ALL_* views, the
>> only
>>>>>> difference is the USER_* views have additional where clause
>>>>> 'owner=...'.
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Barbara.
>>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: fdo-users-bounces at lists.osgeo.org
>>>>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac
> Spitzer
>>>>>>> Sent: Thursday, August 21, 2008 5:56 PM
>>>>>>> To: FDO Users Mail List
>>>>>>> Subject: [fdo-users] big oracle describe schema performance
boost
>>>>>>>
>>>>>>> this is the query being used at the moment for an oracle
describe
>>>>>> schema,
>>>>>>> (i did rewrite it without the where clause )
>>>>>>>
>>>>>>> SELECT a.owner, a.table_name, a.column_name, a.srid,
a.diminfo,
>>>>>> b.cs_name,
>>>>>>> b.wktext, c.index_name, d.sdo_layer_gtype,
>> s.sequence_name,
>>>>>>> d.sdo_root_mbr, NULL o1, NULL o2, NULL o3, NULL o4, NULL
>>> o5,
>>>>>> NULL o6,
>>>>>>> NULL o7, NULL o8, NULL o9, NULL o10, NULL o111, NULL o12
>>>>>>> FROM all_tab_columns t
>>>>>>> INNER JOIN all_sdo_geom_metadata a
>>>>>>> on t.owner = a.owner
>>>>>>> AND t.table_name = a.table_name
>>>>>>> AND t.column_name = a.column_name
>>>>>>> LEFT JOIN MDSYS.cs_srs b
>>>>>>> ON a.srid = b.srid
>>>>>>> LEFT JOIN all_sdo_index_info c
>>>>>>> ON a.owner = c.table_owner
>>>>>>> AND a.table_name = c.table_name
>>>>>>> LEFT JOIN all_sdo_index_metadata d
>>>>>>> ON c.sdo_index_owner =
>>>>>> d.sdo_index_owner
>>>>>>> AND c.index_name =
>>>>>> d.sdo_index_name
>>>>>>> LEFT JOIN all_sequences s
>>>>>>> ON s.sequence_name = CONCAT
>>>>>> (a.table_name, '_FDOSEQ')
>>>>>>> ORDER BY a.owner, a.table_name
>>>>>>>
>>>>>>> it's pretty slow because of the use of the all tables, i think
>>> there
>>>>>>> is a bug in there as well with
>>>>>>> the sequence join, it will match on duplicate table names in
> other
>>>>>>> schemas, but the performance
>>>>>>> goes to hell if you add the s.sequence_owner=t.owner crtieria
>>>>>>>
>>>>>>> if we just use the user views, that problem disappears
>>>>>>>
>>>>>>> SELECT a.table_name, a.column_name, a.srid, a.diminfo,
>> b.cs_name,
>>>>>>> b.wktext, c.index_name, d.sdo_layer_gtype,
>> s.sequence_name,
>>>>>>> d.sdo_root_mbr, NULL o1, NULL o2, NULL o3, NULL o4, NULL
>>> o5,
>>>>>> NULL o6,
>>>>>>> NULL o7, NULL o8, NULL o9, NULL o10, NULL o111, NULL o12
>>>>>>> FROM user_tab_columns t
>>>>>>> INNER JOIN user_sdo_geom_metadata a
>>>>>>> on t.table_name = a.table_name
>>>>>>> AND t.column_name = a.column_name
>>>>>>> LEFT JOIN MDSYS.cs_srs b
>>>>>>> ON a.srid = b.srid
>>>>>>> LEFT JOIN user_sdo_index_info c
>>>>>>> on a.table_name = c.table_name
>>>>>>> LEFT JOIN user_sdo_index_metadata d
>>>>>>> on c.index_name =
>>>>>> d.sdo_index_name
>>>>>>> LEFT JOIN user_sequences s
>>>>>>> ON s.sequence_name = CONCAT
>>>>>> (a.table_name, '_FDOSEQ')
>>>>>>> ORDER BY a.table_name
>>>>>>>
>>>>>>>
>>>>>>> the performance improvement and load reduction is quite
dramatic,
>>>> the
>>>>>>> difference is that user_tab_columns
>>>>>>> lists the objects owner by the users, whereas, the
> all_tab_columns
>>>>>>> lists all accessible tables
>>>>>>>
>>>>>>> when using these tables, a ALTER SESSION SET
>>>>>>> CURRENT_SCHEMA='%kingoracle.oracleschema%'
>>>>>>> would be required at the start of the session if you use a
>>> different
>>>>>>> username than the specified oracleschema
>>>>>>>
>>>>>>> small schema
>>>>>>> q_user_schema (Datasource=gis, Time=16ms, Records=4)
>>>>>>> q_all_schema (Datasource=gis, Time=1750ms, Records=4)
>>>>>>>
>>>>>>> big schema
>>>>>>> q_user_schema (Datasource=gis, Time=234ms, Records=146)
>>>>>>> q_all_schema (Datasource=gis, Time=1531ms, Records=146)
>>>>>>>
>>>>>>> z
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Zac Spitzer -
>>>>>>> http://zacster.blogspot.com (My Blog)
>>>>>>> +61 405 847 168
>>>>>>> _______________________________________________
>>>>>>> fdo-users mailing list
>>>>>>> fdo-users at lists.osgeo.org
>>>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>>>> _______________________________________________
>>>>>>> fdo-users mailing list
>>>>>>> fdo-users at lists.osgeo.org
>>>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Zac Spitzer -
>>>>>> http://zacster.blogspot.com (My Blog)
>>>>>> +61 405 847 168
>>>>>> _______________________________________________
>>>>>> fdo-users mailing list
>>>>>> fdo-users at lists.osgeo.org
>>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>>> _______________________________________________
>>>>>> fdo-users mailing list
>>>>>> fdo-users at lists.osgeo.org
>>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Zac Spitzer -
>>>>> http://zacster.blogspot.com (My Blog)
>>>>> +61 405 847 168
>>>>> _______________________________________________
>>>>> fdo-users mailing list
>>>>> fdo-users at lists.osgeo.org
>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>> _______________________________________________
>>>>> fdo-users mailing list
>>>>> fdo-users at lists.osgeo.org
>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Zac Spitzer -
>>>> http://zacster.blogspot.com (My Blog)
>>>> +61 405 847 168
>>>> _______________________________________________
>>>> fdo-users mailing list
>>>> fdo-users at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>> _______________________________________________
>>>> fdo-users mailing list
>>>> fdo-users at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>
>>>
>>>
>>>
>>> --
>>> Zac Spitzer -
>>> http://zacster.blogspot.com (My Blog)
>>> +61 405 847 168
>>> _______________________________________________
>>> fdo-users mailing list
>>> fdo-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>> _______________________________________________
>>> fdo-users mailing list
>>> fdo-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>
>>
>>
>>
>> --
>> Zac Spitzer -
>> http://zacster.blogspot.com (My Blog)
>> +61 405 847 168
>> _______________________________________________
>> fdo-users mailing list
>> fdo-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>> _______________________________________________
>> fdo-users mailing list
>> fdo-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>
>
>
>
> --
> Zac Spitzer -
> http://zacster.blogspot.com (My Blog)
> +61 405 847 168
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
>
--
Zac Spitzer -
http://zacster.blogspot.com (My Blog)
+61 405 847 168
_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users
More information about the fdo-users
mailing list