This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Apr 4, 2013 12:53 PM by B Hall Go to original post RSS
  • 15. Re: SDO_AGGR_MBR
    NoelKhan Journeyer
    Currently Being Moderated
    Bryan,

    If the table is partitioned, you might be able to prune irrelevant data.

    Regards,
    Noel
  • 16. Re: SDO_AGGR_MBR
    B Hall Explorer
    Currently Being Moderated
    Thanks Noel,

    But in this case that would do no good (not that I could recommend buying 5 partitioning licenses for this problem anyhow). ArcCatalog is trying to determine the BBOX for all the data. The sdo_aggr_mgr function ignores the indexes (that have the data it needs) and instead does a FTS and re-compute of all the data. I still can't believe nothing has happened in all this time. What exactly are those spatial license fees doing for us again?

    If I could only spend a week at Oracle with the source code, I could easily fix this problem. Very frustrating!

    Bryan
  • 17. Re: SDO_AGGR_MBR
    yhu Journeyer
    Currently Being Moderated
    Hi Bryan,

    I just try to understand your question.

    For SDO_TUNE.EXTENT_OF(), spatial indexes on geodetic geometries are already used in 11.2.

    For SDO_AGGR_MBR, I don't know how a spatial index can be safely used. For example,

    SELECT SDO_AGGR_MBR(shape)
    FROM cola_markets
    WHERE id between 1 and 100;

    Thanks,
    Ying
  • 18. Re: SDO_AGGR_MBR
    B Hall Explorer
    Currently Being Moderated
    Ying,

    SDO_AGGR_MBR - I don't understand why this is a problem, as SDO_TUNE.EXTENT_OF uses the same indexes. Is it not a simple matter to enhance SDO_AGGR_MBR such that given the rowid's from other predicates (where id between 1 and 100 in your example), select the matching rows from the index tables, do the same min/max that extent_of uses, and return the results? How is that not "safe"?

    BTW, the 11.2 documentation for SDO_AGGR_MBR states:

    "The SDO_TUNE.EXTENT_OF function, documented in Chapter 31, also returns the MBR of geometries. The SDO_TUNE.EXTENT_OF function has better performance than the SDO_AGGR_MBR function *if the data is non-geodetic* and if a spatial index is defined on the geometry column;"

    So - I thought SDO_TUNE.EXTENT_OF didn't work with geodetic data (and honestly had not used it since about 2006) - and the docs here seem to infer that it won't. But I tried the extend_of function on the table, and it does seem to work (although that will not immediately fix my problem since that is coded in their software). So... is SDO_TUNE.EXTENT_OF safe for use with geodetic SRIDs? Anything to watch out for?

    Bryan
  • 19. Re: SDO_AGGR_MBR
    yhu Journeyer
    Currently Being Moderated
    Hi Bryan,

    The 11.2 documentation for SDO_AGGR_MBR seems a doc bug.

    For SDO_AGGR_MBR using a spatial index, it could be very difficult if not impossible.

    Assume you have a query without any spatial data types and spatial indexes

    select sum(c1) from t1 where c2 between 1 and 100;

    Do you think the above SQL will use a index on c1?

    Anyway, I will contact doc writer for the above doc bug.

    Thanks,
    Ying
  • 20. Re: SDO_AGGR_MBR
    yhu Journeyer
    Currently Being Moderated
    Hi Bryan,

    And another example when I mean by "safely":

    SELECT sdo_aggr_mbr(SDO_GEOM.SDO_CENTROID(shape, 0.05))
    FROM cola_markets
    WHERE id between 1 and 100;

    i.e. sdo_aggr_mbr() can accept any geometry (temporary or persistent).

    Thanks,
    Ying
  • 21. Re: SDO_AGGR_MBR
    B Hall Explorer
    Currently Being Moderated
    Ying,
    yhu wrote:
    Assume you have a query without any spatial data types and spatial indexes

    select sum(c1) from t1 where c2 between 1 and 100;

    Do you think the above SQL will use a index on c1?
    Yes of course it will:
    SQL> CREATE TABLE t1
      2  (
      3     c1   NUMBER,
      4     c2   CHAR (1000),
      5     c3   CHAR (1000),
      6     c4   NUMBER
      7  );
    
    Table created.
    
    SQL>
    SQL> BEGIN
      2     FOR i IN 1 .. 10000
      3     LOOP
      4        INSERT /*+ append */
      5              INTO  t1
      6             VALUES ( (1 + ABS (MOD (DBMS_RANDOM.random, 1000))), 'A', 'B', 42);
      7     END LOOP;
      8  END;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> CREATE TABLE t2
      2  AS SELECT * FROM t1;
    
    Table created.
    
    SQL>
    SQL> CREATE INDEX t2i1
      2     ON t2 (c1);
    
    Index created.
    
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats('BHALL2','T1');
    
    PL/SQL procedure successfully completed.
    
    SQL> EXEC dbms_stats.gather_table_stats('BHALL2','T2');
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> SET AUTOTRACE ON
    SQL>
    SQL> SELECT SUM (c1) FROM t1;
    
       SUM(C1)
    ----------
       4991084
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3693069535
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |     4 |   991   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
    |   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   991   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              1  db block gets
           3408  consistent gets
              0  physical reads
              0  redo size
            212  bytes sent via SQL*Net to client
            247  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> SELECT SUM (c1) FROM t2;
    
       SUM(C1)
    ----------
       4991084
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3664488178
    
    ------------------------------------------------------------------------------
    | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |     1 |     4 |     8   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |     4 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T2I1 | 10000 | 40000 |     8   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             26  consistent gets
              0  physical reads
              0  redo size
            229  bytes sent via SQL*Net to client
            247  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    Here I created two tables, T1 and T2. T2 has an index, T1 does not. The optimizer noticed there was an index on the column we were using on T2 and chose a index scan instead of a table scan. I would expect the same with the SDO_AGGR_GEOM function (sum up the mins and maxes).
    yhu wrote:
    And another example when I mean by "safely":
    SELECT sdo_aggr_mbr(SDO_GEOM.SDO_CENTROID(shape, 0.05))
    FROM cola_markets
    WHERE id between 1 and 100;
    i.e. sdo_aggr_mbr() can accept any geometry (temporary or persistent).
    Yes, I get that it is not trivial. The optimizer has to recogize what is being passsed into the function (raw column or temporary geom). Then it has to ensure there is a spatial index exists. When those conditions are met, it can then scan the index with the pre-computed MBRs instead of re-computing all of them AFTER a FTS.

    Makes sense don't you think?

    Bryan
  • 22. Re: SDO_AGGR_MBR
    B Hall Explorer
    Currently Being Moderated
    To possibly resolve this problem - I had an enhancement put in with ESRI to use SDO_TUNE.EXTENT_OF in place of SDO_AGGR_MBR. Incident #1138117 if you are interested.

    Hopefully this will find it's way in to some future patch or version.

    Bryan
1 2 Previous Next