1 / 506

Oracle Spatial 10 g : Advanced

Oracle Spatial 10 g : Advanced. Introduction. Overview. This seminar introduces you to the advanced concepts of Oracle Spatial 10 g . Prior experience with Oracle Spatial 9 i or Oracle Spatial 10g: Fundamentals is assumed. Course Objectives.

manuels
Download Presentation

Oracle Spatial 10 g : Advanced

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Spatial 10g: Advanced Introduction

  2. Overview • This seminar introduces you to the advanced concepts of Oracle Spatial 10g. • Prior experience with Oracle Spatial 9ior Oracle Spatial 10g: Fundamentals is assumed.

  3. Course Objectives • After completing this lesson, you should be able to understand the following advanced Oracle Spatial 10g enhancements: • Identify the basic changes between Oracle9i Spatial and Oracle Spatial 10g • Export and import transportable tablespaces that include spatial indexes • Describe the Oracle Spatial GeoRaster storage model • Use MapViewer to publish raster (and vector) data over the web

  4. Course Objectives • Describe Oracle Spatial Geocoder in the server and the Geocoding Architecture • Identify how to use the Oracle Spatial Geocoding Functions • Describe Oracle Spatial Router and the Routing Architecture • Discuss Route Requests and Route Responses • Describe the Oracle Spatial Topology Data Model • Discuss how to create and load a Topology

  5. Course Objectives • Edit a topology and topology caches • Describe the Oracle Spatial Network Data Model and Network Concepts • Describe the Node, Link, and Path Tables used with the Network Data Model • Use the Network Data Model to perform graph analysis • Discuss Oracle Spatial Analysis and Mining features

  6. Course Agenda – Day 1

  7. Course Agenda – Day 2

  8. Advanced SpatialIndexing

  9. Objectives • After completing this lesson, you should be able to do the following: • Create partitioned spatial indexes • Discuss spatial partitioning of data • Export and import transportable tablespaces that include spatial indexes • Create function-based indexes • Embed and index spatial objects within objects

  10. Spatial Index Partitioning

  11. Oracle Table Partitioning andOracle Spatial • Oracle table partitioning is a key feature of the Oracle database. • One logical table: • Decomposes to multiple physical tables, called partitions • The decomposition is based on the value of a partitioning key: • Single Column • Multicolumn

  12. Oracle Table Partitioning andOracle Spatial • A partitioned index is one logical index: • Decomposes to one physical index per partition • Tables with SDO_GEOMETRY columns can be partitioned: • Partitioned spatial indexes are also supported • Transparent to applications

  13. Benefits of Partitioned Spatial Indexes • Performance: • Reduce response times for long-running queries: local index scan • Reduce response times for concurrent queries: parallel I/O on each partition’s index • Single queries that search multiple index partitions can be parallelized • Maintenance: • Partition-level index rebuilds • Partition-level table and index archives • Partition-level table and index exchanges, splits, merges

  14. Oracle Spatial Partitioning • Addresses the problem of supporting very large tables and indexes: • Decomposes tables and indexes into smaller pieces called partitions • SQL statements can access and manipulate the partitions rather than entire tables • Table partitions and index partitions can be mapped to different tablespaces

  15. Data Set Description for Partitioned Spatial Index Examples

  16. Fabricated Yellow Pages Data(Businesses in New York) (-74.099, 41.000) (-73.501, 41.000) • All business locations stored in longitude/latitude • Businesses on the same latitude are 0.001 decimal degrees apart • Latitudes increase by 0.001 decimal degrees • 360,600 businesses in table (-74.100, 41.000) (-74.100, 40.999) (-74.100, 40.400)

  17. The YELLOW_PAGES Table ID Name Category Location 1 2 3 4 5 6 7 25 ITALIAN RESTAURANT 1 CHASE BANK 1 MOBIL GAS 1 PATH MARK GROCERY 1 SHERATON HOTEL 1 FORD AUTOS 1 THAI RESTAURANT 1 ITALIAN RESTAURANT 2 1 2 3 4 5 6 1 1 (-74.100, 40.400) (-74.100, 40.401) (-74.100, 40.402) (-74.100, 40.403) (-74.100, 40.404) (-74.100, 40.405) (-74.100, 40.406) (-74.100, 40.424) • Six categories are defined that correspond to: • 1 for restaurants • 2 for banks • 3 for gas stations • 4 for grocery stores • 5 for hotels • 6 for auto dealers

  18. Creating a PartitionedSpatial Index

  19. Spatial Index Partitioning Parameters • Storage parameters for spatial index partitions should be treated in the same way as those for other partitioned indexes. • For best performance, storage parameters can (and should) be different for each partition: • TABLESPACE for spatial index placement • These parameters can help reduce the I/O contention between different spatial index partitions, thus ensuring: • Better performance with parallelism • Better performance with concurrency

  20. Spatial Index Partitioning Parameters • Some spatial index parameters must be the same for all partitions • SDO_INDX_DIMS

  21. Oracle Spatial Index Partitioning • Only range partitioning of base table is supported: • Not hash, composite, or list • Extensible indexing limitation • Partition key cannot be the SDO_GEOMETRY column • Choose a partition key that: • Is likely to be in the WHERE clause • Creates meaningful partitions when geometries are separated based on the partition key value

  22. Oracle Spatial Index Partitioning • With an effective partition key, performance gains attributed to partitioning are greater on larger tables. CREATE TABLE yellow_pages_part (id NUMBER, name VARCHAR2(50), category NUMBER, location SDO_GEOMETRY) PARTITION BY RANGE (category) (PARTITION p1 VALUES LESS THAN (2), PARTITION p2 VALUES LESS THAN (3), PARTITION p3 VALUES LESS THAN (4), PARTITION p4 VALUES LESS THAN (5), PARTITION p5 VALUES LESS THAN (6), PARTITION p6 VALUES LESS THAN (MAXVALUE));

  23. Spatial Index Partitioning Syntax CREATE INDEX yp_part_sidx ON yellow_pages_part (location) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('LAYER_GTYPE=POINT') LOCAL (PARTITION ip1PARAMETERS('TABLESPACE=TBS_1'), PARTITION ip2PARAMETERS('TABLESPACE=TBS_2'), PARTITION ip3, PARTITION ip4, PARTITION ip5, PARTITION ip6); • The LOCAL keyword is required to create a partitioned index. • If the LOCAL keyword is not specified, a global index is created.

  24. Spatial Index Partitioning Best Practices • Some customers who use Oracle Spatial have several hundreds of partitions. • In the previous CREATE INDEX statement, if any partition’s index fails to build, the whole index build fails, and the whole index needs to be rebuilt • A better way would be: • Create the spatial index with the UNUSABLE keyword • Creates all of the index metadata without actually • creating the index • Next, do an ALTER INDEX … REBUILD PARTITION • Can do this in parallel from different sessions • If any partition’s index fails to build, you can • rebuild only that partition’s index

  25. Spatial Index Partitioning Best Practices CREATE INDEX yp_part_sidx ON yellow_pages_part (location) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('LAYER_GTYPE=POINT') LOCAL (PARTITION ip1PARAMETERS ('TABLESPACE=TBS_1'), PARTITION ip2PARAMETERS ('TABLESPACE=TBS_2'), PARTITION ip3, PARTITION ip4, PARTITION ip5, PARTITION ip6) UNUSABLE; ALTER INDEX yp_part_sidx REBUILD PARTITION ip1; ALTER INDEX yp_part_sidx REBUILD PARTITION ip2; ALTER INDEX yp_part_sidx REBUILD PARTITION ip3; ALTER INDEX yp_part_sidx REBUILD PARTITION ip4; ALTER INDEX yp_part_sidx REBUILD PARTITION ip5; ALTER INDEX yp_part_sidx REBUILD PARTITION ip6;

  26. Spatial Index Partitioning Restrictions • The partition key must be a scalar value. • Hash, composite, and list partitioning are not supported. • The SDO_INDX_DIMS and SDO_RTR_PCTFREE index parameters must be the same across all partitions. • Other parameters, such as storage parameters, can and should be different for each partition. • The ALTER TABLE partitioning functionality works: • Can exchange a partition with another table and its index • Can split and merge table partitions • Split/merge requires a rebuild of associated spatial • indexes

  27. Query Examples UsingPartitioned and Nonpartitioned Tables

  28. Nonpartitioned Example -- nonpartitioned case SELECT count(*) FROM yellow_pages a WHERE sdo_within_distance (a.location, sdo_geometry (2001, 8307, sdo_point_type (-73.8, 40.7, null), null, null), 'distance=8 unit=mile') = 'TRUE' AND category = 1; -- runs in 1.70 seconds • How many restaurants are within eight miles of a given location? Note: Sometimes, no_index hints can help performance, irrespective of partitioning. -- nonpartitioned case SELECT /*+ no_index (a yp_category_idx) */ count(*) FROM yellow_pages a WHERE sdo_within_distance (a.location, sdo_geometry (2001, 8307, sdo_point_type (-73.8, 40.7, null), null, null), 'distance=8 unit=mile') = 'TRUE' AND category = 1; -- runs in 0.44 seconds

  29. Partitioned Example • How many restaurants are within eight miles of a given location? -- partitioned case SELECT count(*) FROM yellow_pages_part a WHERE sdo_within_distance (a.location, sdo_geometry (2001, 8307, sdo_point_type (-73.8, 40.7, null), null, null), 'distance=8 unit=mile') = 'TRUE' AND category = 1; -- runs in 0.08 seconds

  30. Partitioned Example: SDO_NN • Find the three closest businesses (any kind) to a given location: • SDO_NN executes once per partition. • This example does not include a partition key, so all the six partitions are searched. • This example sets ‘SDO_NUM_RES=3’, but returns 18 rows (three rows per partition). • The next slide shows how to answer the question above (that is, return only three rows). -- This query does not answer the question above -- (returns more than three rows) SELECT name, sdo_nn_distance(1) distance FROM yellow_pages_part a WHERE sdo_nn (a.location, sdo_geometry (2001, 8307, sdo_point_type (-73.8042, 40.7613, null), null, null), 'sdo_num_res=3 unit=mile', 1) = 'TRUE' ORDER BY distance;

  31. Partitioned Example: SDO_NN • Find the three closest businesses (any kind) to a given location. • The example below ensures only three rows are returned. • When more than one partition is searched, and a specific number of results are desired, SDO_NN must use an inline view with rownum<some_value) outside of the inline view. • In the example below, the inline view returns 18 rows (three rows per partition) and rownum < 4 reduces the result set to three rows: -- partitioned table - query does not include partition key SELECT name, distance FROM (SELECT name, sdo_nn_distance(1) distance FROM yellow_pages_part a WHERE sdo_nn (a.location, sdo_geometry (2001, 8307, sdo_point_type(-73.8042, 40.7613,null), null, null), 'sdo_num_res=3 unit=mile', 1) = 'TRUE' ORDER BY distance) WHERE rownum < 4;

  32. Partitioned Example: SDO_NN • Find the three closest restaurants to a given location: • If looking only for restaurants, include the restaurant category partition key • Restaurant category partition key ensures only one partition is searched; inline view is not necessary • This query returns the three closest restaurants: -- partitioned table - query includes partition key and -- use of the inline view is not necessary SELECT name, sdo_nn_distance(1) distance FROM yellow_pages_part a WHERE a.category = 1 AND sdo_nn (a.location, sdo_geometry (2001, 8307, sdo_point_type (-73.8042, 40.7613, null), null, null), 'sdo_num_res=3 unit=mile', 1) = 'TRUE' ORDER BY distance;

  33. Partitioned Example: SDO_NN • Find the three closest Italian restaurants to a given location • If looking only for restaurants, include the restaurant category partition key • Restaurant category partition key ensures only one partition is searched • This query returns the three closest Italian restaurants: -- partitioned table - query includes the partition key -- and uses sdo_batch_size instead of sdo_num_res SELECT name, sdo_nn_distance(1) distance FROM yellow_pages_part a WHERE a.category = 1 AND sdo_nn (a.location, sdo_geometry (2001, 8307, sdo_point_type (-73.8042, 40.7613, null), null, null), 'sdo_batch_size=0 unit=mile', 1) = 'TRUE' AND a.name like '%ITALIAN%' AND rownum < 4 ORDER BY distance;

  34. Parallel Partitioned Spatial Index Creation • When the PARALLEL keyword is specified: • Multiple partitions are indexed in parallel. The number of parallel processes is determined by: • Degree in PARALLEL [<DEGREE>] syntax • Oracle algorithms when PARALLEL is specified without <DEGREE> • Intra-partition parallelism is not supported: • Within each partition there is no parallelism, unlike nonpartitioned tables • Instead, PARALLEL [<DEGREE>] partition’s indexes are built in parallel

  35. Parallel Partitioned Spatial Index: Query • After the spatial index is created: • Parallel query (across partitions) is supported • Only useful if more than one partition is searched • If spatial index is created without the parallel keyword, you can add parallelism: ALTER INDEX <index_name> PARALLEL [<degree>];

  36. Spatial Partitioning of Data

  37. Parallel Partitioned Spatial Index Creation and Query • Spatial data can be partitioned based on its location • Some spatial customers have a regional partition key: • All of the benefits of spatial partitioning that are discussed in this section are automatically included. • Can partition on X (Longitude) or Y (Latitude) • Can partition using both X and Y • Can partition using a traditional partition key such as date and location partitioning

  38. Spatial Partition Pruning • The Oracle optimizer does partition elimination on the basis of a partition key. In a similar fashion, Oracle Spatial can automatically eliminate partitions by using spatial partition pruning. • Included in spatial index metadata is SDO_ROOT_MBR • The minimum-bounding rectangle encompassing the spatial data in that indexed partition. • At query time, Oracle Spatial compares the query window minimum bounding rectangle (MBR) with the partition’s SDO_ROOT_MBR. • If there is no overlap, then no further processing is done on that partition.

  39. Spatial Partition Pruning • Used with these Spatial operators • SDO_WITHIN_DISTANCE • SDO_FILTER • SDO_RELATE and relationship operators such as SDO_ANYINTERACT • Completely transparent and automatic • Requires no partition key in the WHERE clause • Including a spatial partition key can be incorrect • Occurs at run time

  40. Spatially Partitioned Table and Index: Multicolumn Key spatial data partitioned index root MBR (not all shown) query window MBR

  41. Performance Considerations of Spatial Partitioning • Large geometries (in area or length) that cross multiple partitions cause the SDO_ROOT_MBR to expand: • Reduces the effectiveness of spatial partitioning • If only a few, can create a separate partition: • You can use geometry length or area or MBR area as a criteria to determine if data should be pushed off to a special partition.

  42. Performance Considerations of Spatial Partitioning • Overhead associated with checking each partition: • A little bit over one millisecond/partition on 1.5 Ghz Itanium box • If a very large number of partitions, overhead can be substantial

  43. Transportable Tablespaces and Spatial Indexes

  44. Transportable Tablespaces • Transportable tablespaces allow you to copy database files from one system to another system. • Use Oracle Export and Import utilities • Specific setup must be done before and after transporting spatial data with spatial indexes • Oracle database allows you to copy database files from one endian platform to a different endian platform. • Spatial data can be moved • Spatial indexes cannot be transported across endian platforms

  45. Transportable Tablespace Support for Spatial Indexes • Oracle10g Spatial includes transportable tablespace support for spatial indexes: • Each user with a spatial index in the tablespace to be transported executes SDO_UTIL.PREPARE_FOR_TTS (TABLESPACE_NAME) before the export. • After import, each user with transported spatial indexes must run SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS. EXECUTE SDO_UTIL.PREPARE_FOR_TTS('TABLESPACE_NAME');

  46. Transportable Tablespace: Example -- Each user with spatial indexes associated with tables -- in transportable tablespace TTBS_DAT that will also be -- moved as part of the transport set will need to do the -- following. This example is for user SCOTT. SCOTT has -- tables in TTBS_DAT and indexes in TTBS_IDX. CONNECT SYSTEM/PW4SYSTEM -- First, make sure SCOTT’s default tablespace is in the -- transport set, because SDO_UTIL.PREPARE_FOR_TTS will -- create a table to be moved in the tablespace. ALTER USER SCOTT DEFAULT TABLESPACE TTBS_DAT; -- CONNECT SCOTT/TIGER EXECUTE SDO_UTIL.PREPARE_FOR_TTS('TTBS_DAT'); -- Connect as sysdba to check if tablespace is OK to move CONNECT SYSTEM/PW4SYSTEM AS SYSDBA -- Execute procedure which checks transportability EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTBS_DAT,TTBS_IDX',TRUE);

  47. Transportable Tablespace: Example -- Valid transport set if the following returns no rows SELECT * FROM TRANSPORT_SET_VIOLATIONS; -- Set tablespace to read only ALTER TABLESPACE ttbs_dat READ ONLY; ALTER TABLESPACE ttbs_idx READ ONLY; -- Using data pump export, the directory to write the dump -- file (which will contain metadata only) has be be created -- and write privileges have to be granted to the user. -- Additionally, any user who will export transportable -- tablespaces needs to be granted EXP_FULL_DATABASE privilege CREATE DIRECTORY dumpdir AS '/usr/dir/dumpfiles'; GRANT READ,WRITE ON DIRECTORY dumpdir TO scott; GRANT EXP_FULL_DATABASE TO scott; -- When transportable tablespace export is done, data gets -- written in a tablespace associated with the user exporting -- the data, so set SCOTT’s default tablespace to one that -- can be written to.

  48. Transportable Tablespace: Example ALTER USER SCOTT DEFAULT TABLESPACE USERS; exit; -- export (note all params must be on the same line) EXPDP SCOTT/TIGER DIRECTORY=DUMPDIR DUMPFILE=ttbs_md.dmp TRANSPORT_TABLESPACES=TTBS_DAT,TTBS_IDX -- Copy dump and tablespace files to other Oracle 10g instance -- The tablespace files can go to their destination directory -- The user who does the import needs to be granted READ and -- WRITE privileges to the directory containing the export -- file, and also requires the IMP_FULL_DATABASE privilege -- Cross-endian transportable tablespaces are supported with -- spatial data and not supported with spatial indexes CONNECT SYSTEM/PW4OTHERSYSTEM CREATE DIRECTORY otherdumpdir AS '/usr/otherdir/dumpfiles'; GRANT READ,WRITE ON DIRECTORY otherdumpdir TO scott; GRANT IMP_FULL_DATABASE TO scott; EXIT;

  49. Transportable Tablespace: Example -- Import using data pump. All parameters below should be -- on the same line, or in a parameter file impdp scott/tiger DIRECTORY=otherdumpdir DUMPFILE=ttbs_md.dmp TRANSPORT_DATAFILES='/usr/orcl10g/ttbs/course_dat.dbf', '/usr/orcl10g/ttbs/course_idx.dbf' -- Allow reads and writes on new tablespaces (just imported) SQLPLUS system/pw4othersystem ALTER TABLESPACE ttbs_dat READ WRITE; ALTER TABLESPACE ttbs_idx READ WRITE; -- For each user who has spatial indexes in transportable -- tablespace: CONNECT scott/tiger; EXEC sdo_util.initialize_indexes_for_tts;

More Related