1 2 Previous Next 22 Replies Latest reply: Apr 4, 2013 2:53 PM by B Hall Go to original post RSS
      • 15. Re: SDO_AGGR_MBR
        NoelKhan
        Bryan,

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

        Regards,
        Noel
        • 16. Re: SDO_AGGR_MBR
          B Hall
          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
            Ying Hu-Oracle
            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
              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
                Ying Hu-Oracle
                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
                  Ying Hu-Oracle
                  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
                    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
                      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