This discussion is archived
9 Replies Latest reply: Dec 30, 2010 5:35 PM by Yasu RSS

Rewrite sql to avoid filter operation

Yasu Newbie
Currently Being Moderated
Hi All,

I found below sql and some more sql's causing high CPU usage.
SELECT :B1 AS ID , 
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL 
                             FROM ONS 
                             WHERE PARENT_ID = :B1 )), 1, 1, 0) AS IP_RELATION ,
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL 
                             FROM ONS 
                             WHERE ULTIMATE_PARENT_GID = :B1 )), 1, 1, 0) AS UP_RELATION , 
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL FROM AFFILIATIONS WHERE AFFILIATED_ID= :B1 )), 1, 1, 0) AS AFF_RELATION , 
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL FROM JOINT_VENTURES WHERE JOINT_VENTURE_ID= :B1 )), 1, 1, 0) AS JV_RELATION ,
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL FROM SUCCESSORS WHERE SUCCESSOR_ID= :B1 )), 1, 1, 0) AS SUC_RELATION ,
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL FROM COUNTERPARTY WHERE CP_TAX_AUTHORITY_ID = :B1 )), 1, 1, 0) AS TAX_AUTH_RELATION , 
       DECODE((SELECT 1
               FROM DUAL 
               WHERE EXISTS (SELECT NULL FROM COUNTERPARTY WHERE CP_PRIM_REGULATOR_ID = :B1 )), 1, 1, 0) AS PRIM_REG_RELATION , 
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL FROM ONS WHERE DUPLICATE_OF_ID = :B1 )), 1, 1, 0) AS DUP_RELATION , 
       DECODE((SELECT 1 
               FROM DUAL 
               WHERE EXISTS (SELECT NULL FROM ONS WHERE REG_AUTHORITY_ID = :B1 )), 1, 1, 0) AS REG_AUTH_RELATION 
FROM DUAL
/

--------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                                |       |       |     2 (100)|          |
|*  1 |  FILTER               |                                |       |       |            |          |
|   2 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN    | IDX_IMMEDIATE_PARENT_ID        |     1 |     3 |     2   (0)| 00:00:01 |
|*  4 |  FILTER               |                                |       |       |            |          |
|   5 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN    | IDX_ULTIMATE_PARENT_ID         |     2 |     4 |     2   (0)| 00:00:01 |
|*  7 |  FILTER               |                                |       |       |            |          |
|   8 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|*  9 |   INDEX FAST FULL SCAN| PK_ORG_AFFILIATED_WITH         |     1 |     7 |   294   (7)| 00:00:04 |
|* 10 |  FILTER               |                                |       |       |            |          |
|  11 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|* 12 |   INDEX FULL SCAN     | PK_ORG_JOINT_VENTURE_OF        |     1 |     7 |     3   (0)| 00:00:01 |
|* 13 |  FILTER               |                                |       |       |            |          |
|  14 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|* 15 |   INDEX FAST FULL SCAN| PK_ONS_SUCCEEDED_BY            |     1 |     7 |    79   (7)| 00:00:01 |
|* 16 |  FILTER               |                                |       |       |            |          |
|  17 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|* 18 |   INDEX RANGE SCAN    | IDX_ORG_CP_TAX_AUTHORITY_ID    |     2 |    14 |     2   (0)| 00:00:01 |
|* 19 |  FILTER               |                                |       |       |            |          |
|  20 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|* 21 |   INDEX RANGE SCAN    | IDX_ORGCP_PRIM_REGULATOR_ID    |     1 |     4 |     2   (0)| 00:00:01 |
|* 22 |  FILTER               |                                |       |       |            |          |
|  23 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|* 24 |   TABLE ACCESS FULL   | ONS                            |     1 |     2 | 27013   (4)| 00:05:25 |
|* 25 |  FILTER               |                                |       |       |            |          |
|  26 |   FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
|* 27 |   TABLE ACCESS FULL   | ONS                            |     1 |     2 |   475   (3)| 00:00:06 |
|  28 |  FAST DUAL            |                                |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   2 - :B1 (NUMBER, Primary=1)
   3 - :B1 (NUMBER, Primary=1)
   4 - :B1 (NUMBER, Primary=1)
   5 - :B1 (NUMBER, Primary=1)
   6 - :B1 (NUMBER, Primary=1)
   7 - :B1 (NUMBER, Primary=1)
   8 - :B1 (NUMBER, Primary=1)
   9 - :B1 (NUMBER, Primary=1)
  10 - :B1 (NUMBER, Primary=1)

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( IS NOT NULL)
   3 - access("IMMEDIATE_PARENT_ID"=:B1)
   4 - filter( IS NOT NULL)
   6 - access("ULTIMATE_PARENT_ID"=:B1)
   7 - filter( IS NOT NULL)
   9 - filter("AFFILIATED_ID"=:B1)
  10 - filter( IS NOT NULL)
  12 - access("JOINT_VENTURE_ID"=:B1)
       filter("JOINT_VENTURE_ID"=:B1)
  13 - filter( IS NOT NULL)
  15 - filter("SUCCESSOR_ID"=:B1)
  16 - filter( IS NOT NULL)
  18 - access("CP_TAX_AUTHORITY_ID"=:B1)
  19 - filter( IS NOT NULL)
  21 - access("CP_PRIM_REGULATOR_ID"=:B1)
  22 - filter( IS NOT NULL)
  24 - filter("DUPLICATE_OF_ID"=:B1)
  25 - filter( IS NOT NULL)
  27 - filter("REG_AUTHORITY_ID"=:B1)
Oracle Version : 10.2.0.4 RAC 2 nodes

Is there any possibility to rewrite this sql to avoid filter operation.

Please let me know if you need any more details....
  • 1. Re: Rewrite sql to avoid filter operation
    729338 Expert
    Currently Being Moderated
    Hi,

    It is doing Full Table Scan on ONS table. Try to create index on the filter columns. Cost is also high there. You can not avaoid filer as you have where clauses on the tables.

    Regards
  • 2. Re: Rewrite sql to avoid filter operation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    skvaish1 wrote:
    Hi,

    It is doing Full Table Scan on ONS table. Try to create index on the filter columns. Cost is also high there. You can not avaoid filer as you have where clauses on the tables.
    And a couple of index fast full scans which have been given a relatively high cost.

    At first sight there is, in principle, no reason why this code needs to be changed significantly - but to work efficiently every existence subquery probably needs to be supported by (and use - with hints if necessary) an index starting with the column that appears in the existence predicate.

    Regards
    Jonathan Lewis
  • 3. Re: Rewrite sql to avoid filter operation
    jgarry Guru
    Currently Being Moderated
    I'm wondering if 98% of the problem is not having an index on DUPLICATE_OF_ID.
  • 4. Re: Rewrite sql to avoid filter operation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jgarry wrote:
    I'm wondering if 98% of the problem is not having an index on DUPLICATE_OF_ID.
    That introduces an interesting point. The optimizer "thinks" that that's the line that's going to cause about 98% of the work (or 96.5% if you want to split hairs) - but look carefully at lines 24 and 27 and you'll see that they both employ full tablescans of table ONS, but have dramatically different costs - roughly 27,000 to 500.

    |* 24 |   TABLE ACCESS FULL   | ONS                            |     1 |     2 | 27013   (4)| 00:05:25 |
    |* 27 |   TABLE ACCESS FULL   | ONS                            |     1 |     2 |   475   (3)| 00:00:06 |
     
      24 - filter("DUPLICATE_OF_ID"=:B1)
      27 - filter("REG_AUTHORITY_ID"=:B1)
    Ignoring the arithmetical complications introduced by nulls we can from this that there are about 50 (27,013/475) times as many distinct values for duplicate_of_id as there are of reg_authority_id - because the optimizer thinks that, on average, it will have to scan 50 times as much of the table before it finds the given value of duplicate_of_id compared to the volume it will have to scan to find the given reg_authority_id.

    So, coming back to your comment about where the problem is: on average it ought to be with the duplicate_of_id, in practice it depends on which specific values are of interest and where they are in the table. It may be that, due to the nature of the application, data, and pattern of activity, most of the cost is actually in the scan that has been given the lower cost.

    (Footnote - costing for an existence subquery appears to be effectively first_rows_1 - or first_rows(1) - costing ... hypothesis yet to be confirmed.)

    Regards
    Jonathan Lewis
  • 5. Re: Rewrite sql to avoid filter operation
    jgarry Guru
    Currently Being Moderated
    Jonathan Lewis wrote:
    jgarry wrote:
    I'm wondering if 98% of the problem is not having an index on DUPLICATE_OF_ID.
    That introduces an interesting point. The optimizer "thinks" that that's the line that's going to cause about 98% of the work (or 96.5% if you want to split hairs) - but look carefully at lines 24 and 27 and you'll see that they both employ full tablescans of table ONS, but have dramatically different costs - roughly 27,000 to 500.
    That was exactly what caught my attention, and made me wonder why no one had commented on it.

    >
    >
    |* 24 |   TABLE ACCESS FULL   | ONS                            |     1 |     2 | 27013   (4)| 00:05:25 |
    |* 27 |   TABLE ACCESS FULL   | ONS                            |     1 |     2 |   475   (3)| 00:00:06 |
    
    24 - filter("DUPLICATE_OF_ID"=:B1)
    27 - filter("REG_AUTHORITY_ID"=:B1)
    Ignoring the arithmetical complications introduced by nulls we can from this that there are about 50 (27,013/475) times as many distinct values for duplicate_of_id as there are of reg_authority_id - because the optimizer thinks that, on average, it will have to scan 50 times as much of the table before it finds the given value of duplicate_of_id compared to the volume it will have to scan to find the given reg_authority_id.

    So, coming back to your comment about where the problem is: on average it ought to be with the duplicate_of_id, in practice it depends on which specific values are of interest and where they are in the table. It may be that, due to the nature of the application, data, and pattern of activity, most of the cost is actually in the scan that has been given the lower cost.

    (Footnote - costing for an existence subquery appears to be effectively first_rows_1 - or first_rows(1) - costing ... hypothesis yet to be confirmed.)
    Yes, I was wondering how far to extrapolate the data from this one data point, thinking everything might be way skewed (as in a status field that is only set to the value on recent records versus one much closer to the beginning of the scan). If it is representative of the data, simply working on the very large time of that full table scan would make sense. If it is not... well, I have seen designs like this wind up with way too many indices, when the real problem is denormalization or improperly defined attributes.

    Which leads back to a more general question about what is to be accomplished with this code and what else accesses the data. It kind of looks like it is creating something that once was described by bit-twiddling.

    >
    Regards
    Jonathan Lewis
  • 6. Re: Rewrite sql to avoid filter operation
    Yasu Newbie
    Currently Being Moderated
    Your predictions is correct...

    Most of the time was spent upon below filtering.
    |* 24 |   TABLE ACCESS FULL   | ONS                            |     1 |     2 | 27013   (4)| 00:05:25 |
    
    24 - filter("DUPLICATE_OF_ID"=:B1)
    I regathered the execution plan using advanced option in dbms_xplan,
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
    ---------------------------------------------------------------------------------------------------------
    |*  1 |  FILTER               |                                |        |       |            |          |
    |   2 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |*  3 |   INDEX RANGE SCAN    | IDX_IMMEDIATE_PARENT_ID        |      1 |     3 |     2   (0)| 00:00:01 |
    |*  4 |  FILTER               |                                |        |       |            |          |
    |   5 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |*  6 |   INDEX RANGE SCAN    | IDX_ULTIMATE_PARENT_ID         |      2 |     4 |     2   (0)| 00:00:01 |
    |*  7 |  FILTER               |                                |        |       |            |          |
    |   8 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |*  9 |   INDEX FAST FULL SCAN| PK_ORG_AFFILIATED_WITH         |      1 |     7 |   294   (7)| 00:00:04 |
    |* 10 |  FILTER               |                                |        |       |            |          |
    |  11 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |* 12 |   INDEX FULL SCAN     | PK_ORG_JOINT_VENTURE_OF        |      1 |     7 |     3   (0)| 00:00:01 |
    |* 13 |  FILTER               |                                |        |       |            |          |
    |  14 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |* 15 |   INDEX FAST FULL SCAN| PK_ONS_SUCCEEDED_BY            |      1 |     7 |    79   (7)| 00:00:01 |
    |* 16 |  FILTER               |                                |        |       |            |          |
    |  17 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |* 18 |   INDEX RANGE SCAN    | IDX_ORG_CP_TAX_AUTHORITY_ID    |      2 |    14 |     2   (0)| 00:00:01 |
    |* 19 |  FILTER               |                                |        |       |            |          |
    |  20 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |* 21 |   INDEX RANGE SCAN    | IDX_ORGCP_PRIM_REGULATOR_ID    |      1 |     4 |     2   (0)| 00:00:01 |
    |* 22 |  FILTER               |                                |        |       |            |          |
    |  23 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |* 24 |   TABLE ACCESS FULL   | ONS                            |      1 |     2 | 27080   (4)| 00:05:25 |
    |* 25 |  FILTER               |                                |        |       |            |          |
    |  26 |   FAST DUAL           |                                |      1 |       |     2   (0)| 00:00:01 |
    |* 27 |   TABLE ACCESS FULL   | ONS                            |      2 |     4 |   929   (4)| 00:00:12 |
    |  28 |  FAST DUAL            |                                |      1 |       |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------
    This clearly shows it spent 5:25 seconds.

    But totally confused about your comment
    it will have to scan 50 times as much of the table before it finds the given value of duplicate_of_id compared to the volume it will have to scan to find the given reg_authority_id
    Is it like (line 24) after full table scan of ONS table it has to discard very less rows compared to filtering operation in line 27. Which is why cost is very high.

    I was inclining more to rewrite this query just because there plenty of indexes on this table, say about 15 indexes. Of which most of them are for satisfying case insensitive search queries. This will be taken care in next phase where we are planning to implement Text Index and drop all FBI indexes created for supporting case insensitive searches.

    Index expressions on this table, still there are other indexes which are not displayed here:
    INDEX_NAME                     COLUMN_EXPRESSION
    ------------------------------ --------------------------------------------------------------------------------
    IDX_ON_NAME_LR                 LOWER("OFFICIAL_NAME")
    IDX_OFFICIAL_ENG_NAME_LR       LOWER("OFFICIAL_ENG_NAME")
    IDX_OFFICIAL_NORM_NAME_LR      LOWER("OFFICIAL_NORM_NAME")
    IDX_OFFICIAL_TRANS_NAME_LR     LOWER("OFFICIAL_TRANS_NAME")
    IDX_OFF_ENG_NORM_NAME_LR       LOWER("OFFICIAL_ENG_NORM_NAME")
    UX_ON_NAME                     NLSSORT("OFFICIAL_NAME",'nls_sort=''BINARY_CI''')
    UX_COUNTRY_OF_DOMICILE         NLSSORT("COUNTRY_OF_DOMICILE",'nls_sort=''BINARY_CI''')
    UX_COUNTRY_OF_INC              NLSSORT("COUNTRY_OF_INCORPORATION",'nls_sort=''BINARY_CI''')
    UX_OFFICIAL_ENG_NAME           NLSSORT("OFFICIAL_ENG_NAME",'nls_sort=''BINARY_CI''')
    UX_OFFICIAL_ENG_NORM_NAME      NLSSORT("OFFICIAL_ENG_NORM_NAME",'nls_sort=''BINARY_CI''')
    UX_OFFICIAL_NORM_NAME          NLSSORT("OFFICIAL_NORM_NAME",'nls_sort=''BINARY_CI''')
    UX_OFFICIAL_TRANS_NAME         NLSSORT("OFFICIAL_TRANS_NAME",'nls_sort=''BINARY_CI''')
    So is there any way to avoid filtering operation at line 24 without creating any index ?
  • 7. Re: Rewrite sql to avoid filter operation
    ant7 Newbie
    Currently Being Moderated
    I am not sure that you can avoid additional indexes, but you can also look at the physical structure of the ONS table - in other words look to see if partitioning, or a partitioned IOT might help.
  • 8. Re: Rewrite sql to avoid filter operation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Yasu wrote:
    Your predictions is correct...

    Most of the time was spent upon below filtering.
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
    ---------------------------------------------------------------------------------------------------------
    No, you are still using a mechanism that shows the predicted execution plan (E- is for estimated), so Oracle is predicating that that's where the time will go.
    If you want to know where the time was spent (for a given value of the bind variable) then you have to run the query with the /*+ gather_plan_statistics */ hint and pull the actual results from memory (see, for example: http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ )


    Bear in miind that if you supply a bind variable for which there is NO matching row in ONS for the two columns then this query is going to do two full tablescans of the table. If you want a really efficient way to get the data you are after there is NO good way of doing this other than creating the best set of indexes.


    As a side thought - and one I would have to investigate - since you have declared a number of inddexes with "case insensitive sorting" - is is possible that you could work around this idea to drop a few of the existing indexes on "lower(column)" and use case-insensitive indexes for these comparisons ?

    I've written a short note on my blog about the "exists subquery" and the varying cost of the tablescane lines - I think you're thinking in the right way about what I said, but it may make it easier for you to understand: http://jonathanlewis.wordpress.com/2010/12/29/existence/

    Regards
    Jonathan Lewis
  • 9. Re: Rewrite sql to avoid filter operation
    Yasu Newbie
    Currently Being Moderated
    My bad..i overlooked the execution plan.

    Below execution plan has been extracted from devlopment database which is exact replica of production database.
    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    -------------------------------------------------------------------------------------------------------------------------------
    |*  1 |  FILTER                   |                                |      1 |        |      1 |00:00:00.72 |    8028 |   5986 |
    |   2 |   FAST DUAL               |                                |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
    |   3 |   PARTITION RANGE ALL     |                                |      1 |      1 |      1 |00:00:00.72 |    8028 |   5986 |
    |*  4 |    TABLE ACCESS FULL      | ONS                            |      1 |      1 |      1 |00:00:00.72 |    8028 |   5986 |
    |*  5 |  FILTER                   |                                |      1 |        |      1 |00:00:00.19 |       7 |      0 |
    |   6 |   FAST DUAL               |                                |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
    |   7 |   PX COORDINATOR          |                                |      1 |        |      1 |00:00:00.19 |       7 |      0 |
    |   8 |    PX SEND QC (RANDOM)    | :TQ10000                       |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |   9 |     PX PARTITION RANGE ALL|                                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 10 |      INDEX RANGE SCAN     | IDX_ULTIMATE_PARENT_ID         |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 11 |  FILTER                   |                                |      1 |        |      0 |00:00:00.11 |    1231 |      0 |
    |  12 |   FAST DUAL               |                                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 13 |   INDEX FAST FULL SCAN    | PK_ORG_AFFILIATED_WITH         |      1 |      1 |      0 |00:00:00.11 |    1231 |      0 |
    |* 14 |  FILTER                   |                                |      1 |        |      0 |00:00:00.01 |       7 |      0 |
    |  15 |   FAST DUAL               |                                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 16 |   INDEX FAST FULL SCAN    | PK_ORG_JOINT_VENTURE_OF        |      1 |      1 |      0 |00:00:00.01 |       7 |      0 |
    |* 17 |  FILTER                   |                                |      1 |        |      0 |00:00:00.02 |     229 |      0 |
    |  18 |   FAST DUAL               |                                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
    |* 19 |   INDEX FAST FULL SCAN    | PK_ONS_SUCCEEDED_BY            |      1 |      1 |      0 |00:00:00.02 |     229 |      0 |
    |* 20 |  FILTER                   |                                |      1 |        |      1 |00:00:00.01 |       3 |      0 |
    |  21 |   FAST DUAL               |                                |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
    |* 22 |   INDEX RANGE SCAN        | IDX_CP_TAX_AUTHORITY_ID        |      1 |      2 |      1 |00:00:00.01 |       3 |      0 |
    |* 23 |  FILTER                   |                                |      1 |        |      1 |00:00:00.01 |       3 |      0 |
    |  24 |   FAST DUAL               |                                |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
    |* 25 |   INDEX RANGE SCAN        | IDX_CP_PRIM_REGULATOR_ID       |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |
    |* 26 |  FILTER                   |                                |      1 |        |      1 |00:00:02.20 |   28923 |  21562 |
    |  27 |   FAST DUAL               |                                |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
    |  28 |   PARTITION RANGE ALL     |                                |      1 |      1 |      1 |00:00:02.20 |   28923 |  21562 |
    |* 29 |    TABLE ACCESS FULL      | ONS                            |      1 |      1 |      1 |00:00:02.20 |   28923 |  21562 |
    |* 30 |  FILTER                   |                                |      1 |        |      1 |00:00:00.01 |       4 |      5 |
    |  31 |   FAST DUAL               |                                |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
    |  32 |   PARTITION RANGE ALL     |                                |      1 |      1 |      1 |00:00:00.01 |       4 |      5 |
    |* 33 |    TABLE ACCESS FULL      | ONS                            |      1 |      1 |      1 |00:00:00.01 |       4 |      5 |
    |  34 |  FAST DUAL                |                                |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
    -------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter( IS NOT NULL)
       4 - filter("IMMEDIATE_PARENT_ID"=:B1)
       5 - filter( IS NOT NULL)
      10 - access("ULTIMATE_PARENT_ID"=:B1)
      11 - filter( IS NOT NULL)
      13 - filter("AFFILIATED_ID"=:B1)
      14 - filter( IS NOT NULL)
      16 - filter("JOINT_VENTURE_ID"=:B1)
      17 - filter( IS NOT NULL)
      19 - filter("SUCCESSOR_ID"=:B1)
      20 - filter( IS NOT NULL)
      22 - access("CP_TAX_AUTHORITY_ID"=:B1)
      23 - filter( IS NOT NULL)
      25 - access("CP_PRIM_REGULATOR_ID"=:B1)
      26 - filter( IS NOT NULL)
      29 - filter("DUPLICATE_OF_ID"=:B1)
      30 - filter( IS NOT NULL)
      33 - filter("REG_AUTHORITY_ID"=:B1)
    It took just 2.20 seconds, but why does it causes more CPU resource ?

    We are about to plugin new module in this database, hence ONS table is partitioned, its partitioned on column PROVIDER which seperates existing and new module in to different partitions which makes easier for loading wihout affecting existing module data(We also make about to load partition local indexes to unusable state). Also this table is the parent table for about 6 child tables. So we decided to partition even child tables by adding PROVIDER column to all child tables and partition on this column. Parent-Child relationship is built upon ID column in all the tables.

    All the sql's will be altered to use PROVIDER column for filtering old and new module data.

    Do you think we are in right approach, I would be thankful if you can help me here for precise designing of this table.
    As a side thought - and one I would have to investigate - since you have declared a number of inddexes with "case insensitive sorting" - is is possible that you could work around this idea to drop a few of the existing indexes on "lower(column)" and use case-insensitive indexes for these comparisons ?
    Will test it in development database, but what is the performance improvement prediction? And please let me know your suspects which claims "lower(column)" should be avoided and use case-insensitive indexes.
    Anyway we are implementing Text-Index on this table and drop all the unwanted indexes.
    I've written a short note on my blog about the "exists subquery" and the varying cost of the tablescane lines
    I am regular reader of your blog, after seeing your test case i understood the concept crystal clear. Thanks a lot....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points