Slow query need suggestion on rewrite or index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Slow query need suggestion on rewrite or index

  1. #1
    Join Date
    Apr 2001
    Posts
    125

    Slow query need suggestion on rewrite or index

    Hi, I have a query only join two tables, one table has about 10 million rows, the other has 4 thousand rows, the query took about 200 seconds to return 700 thousand rows

    This is the query:

    Code:
    SELECT count(1)
      FROM table1 INNER JOIN table2 ON ( table1.col1 = table2.cola 
                                         AND
                                         table1.col2 = table2.colb)
     WHERE table1.col3<=SYSDATE
       AND (
            (
             table1.col4='a' 
             AND 
             table1.col5<100
            )
            OR
            (
             table1.col4='b' 
             AND 
             (
              table1.col5=10 
              OR
              table1.col5=20
             )
            )
           ) 
       AND table1.col6<>'abc' 
       AND table1.col6<>'def' 
       AND table1.col6<>'ghi'
       AND table2.colc='xy' 
       AND table2.cold='z'
    This is the output of ultxpls.sql

    Code:
    SELECT STATEMENT, GOAL = FIRST_ROWS			Cost=87796	Cardinality=1	Bytes=39
     SORT AGGREGATE				Cardinality=1	Bytes=39
      HASH JOIN			Cost=87796	Cardinality=34711	Bytes=1353729
       TABLE ACCESS FULL	Object owner=joe	Object name=table2	Cost=25	Cardinality=91	Bytes=1274
       TABLE ACCESS FULL	Object owner=joe	Object name=table1	Cost=87756	Cardinality=1461194	Bytes=36529850
                                                           
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("table1"."col1"="table2"."colb")
       3 - filter("table2"."colc"='xy' AND "table2"."cold"='z'
                  AND "table2"."colb"='001')
       4 - filter("table1"."col3"<=SYSDATE@! AND
                  ("table1"."col4"='a' AND "table1"."col5"<100 OR
                  "table1"."col4"='b' AND ("table1"."col5"=10 OR
                  "table1"."col5"=20)) AND "table1"."col6"<>'abc' AND
                  "table1"."col6"<>'def' AND "table1"."col6"<>'ghi'
                  AND "table1"."WHSE"='001')
    I want to know why it is doing full table scan, I have index on:

    table1 (col1, col2)
    table2(col2, col1) primary key

    Thanks

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Because the optimizer thinks "FULL TABLE SCAN" cost is cheaper than using indexes.

    See below:
    TABLE ACCESS FULL Object owner=joe Object name=table1 Cost=87756 Cardinality=1461194 Bytes=36529850

    First run a query on the big table with out joining the small table but with all other predicates on table1.
    And see the statistics matches with your output. If they are not, then create new indexes on high cardinality columns. And check one more time.

    Also, histogram may help in this case.

    Tamil

  3. #3
    Join Date
    Apr 2001
    Posts
    125
    First run a query on the big table with out joining the small table but with all other predicates on table1.
    tamilselvan:

    I run the query on the big table without join with the small table, here is the explain plan:

    Code:
    SELECT STATEMENT, GOAL = FIRST_ROWS			Cost=87763	Cardinality=1	Bytes=20
     SORT AGGREGATE				Cardinality=1	Bytes=20
      TABLE ACCESS FULL	Object owner=joe	Object name=table1	Cost=87763	Cardinality=1462134	Bytes=29242680
    The Cardinality and Cost are pretty much the same as the original query.

    Do you think it will help if I add an index on table1.col3?

    Please advice.

    Thanks

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    how many rows do your 2 tables have?

    how many rows do are returned if execute
    SELECT count(*)
    FROM table1
    WHERE table1.col3<=SYSDATE
    AND (
    (
    table1.col4='a'
    AND
    table1.col5<100
    )
    OR
    (
    table1.col4='b'
    AND
    (
    table1.col5=10
    OR
    table1.col5=20
    )
    )
    )
    AND table1.col6<>'abc'
    AND table1.col6<>'def'
    AND table1.col6<>'ghi'

    How many rows are retuned if you execute
    SELECT count(1)
    FROM table2 WHERE table2.colc='xy'
    AND table2.cold='z'


    How long does the execution of these 2 queries take?

    How long does your query take?

  5. #5
    Join Date
    Apr 2001
    Posts
    125
    See the following results:

    Code:
    select count(*) from table1
    --9746801
    
    select count(*) from table2
    --3845
    
    SELECT count(*)
    FROM table1 
    WHERE table1.col3<=SYSDATE
    AND (
    (
    table1.col4='a' 
    AND 
    table1.col5<100
    )
    OR
    (
    table1.col4='b' 
    AND 
    (
    table1.col5=10 
    OR
    table1.col5=20
    )
    )
    ) 
    AND table1.col6<>'abc' 
    AND table1.col6<>'def' 
    AND table1.col6<>'ghi'
    --857747 in 138.389 seconds
    
    SELECT count(1)
    FROM table2 WHERE table2.colc='xy' 
    AND table2.cold='z'
    --77 in 0.441 seconds

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    Because your query against table1 returns about 10% of the rows it probably doesn't make sense to use an index.

    Have you tried to execute the query in parallel using the hint
    /*+ parallel(table1) */
    how fast is it?

    Which Oracle version do you use?

    Which are the values of the following parameters:
    WORKAREA_SIZE_POLICY
    HASH_AREA_SIZE
    PGA_AGGREGATE_TARGET

  7. #7
    Join Date
    Apr 2001
    Posts
    125
    Since there is a index on table1 (col1, col2), and

    Code:
    select count(1) from table1;
    
    --9746801
    
    select COUNT(distinct col1)
    from table1
    
    --390
    
    select COUNT(distinct col2)
    from table1
    
    --1
    
    select COUNT(distinct col3)
    from table1
    
    --857
    
    select COUNT(1)
    from table1
    where col3<=sysdate;
    
    --971876
    Do you know it will help if I modify the current index to
    table1 (col3, col1, col2) or add a new index just on col3?

    Thanks

  8. #8
    Join Date
    Apr 2001
    Posts
    125
    BTW, the oracle version is Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit

    WORKAREA_SIZE_POLICY = AUTO
    HASH_AREA_SIZE = 131072
    PGA_AGGREGATE_TARGET = 604M

  9. #9
    Join Date
    Mar 2002
    Posts
    534
    With your conditions set against TABLE1 you will get about 9% (857747/9746801) of te rows returned. This means that an index would probably not be of any help.

    Have you tried to execute your query in parallel?
    Also please show explain plan of teh parallel query using DBMS_XPLAN.DISPLAY
    http://download-west.oracle.com/docs...12/d_xplan.htm

  10. #10
    Join Date
    Apr 2001
    Posts
    125
    After adding hint /*+ parallel(table1) */, instead runs 150 seconds, now it runs 120 seconds.

    Here is the result of DBMS_XPLAN.DISPLAY:

    Code:
    ------------------------------------------------------------------------------------
    | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |              |     1 |    39 | 87767   (3)| 00:17:34 |
    |   1 |  SORT AGGREGATE     |              |     1 |    39 |            |          |
    |*  2 |   HASH JOIN         |              | 17262 |   657K| 87767   (3)| 00:17:34 |
    |*  3 |    TABLE ACCESS FULL| table2       |    91 |  1274 |    25   (0)| 00:00:01 |
    |*  4 |    TABLE ACCESS FULL| table1       |   726K|    17M| 87734   (3)| 00:17:33 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("table1"."col1"="table2"."cola" AND
                  "table1"."col2"="table2"."colb")
       3 - filter("table2"."colc"='xy' AND "table2"."cold"='z')
       4 - filter("table1"."col3"<=SYSDATE@! AND
                  ("table1"."col4"='a' AND "table1"."col5"<100 OR
                  "table1"."col4"='b' AND ("table1"."col5"=10 OR
                  "table1"."col5"=20)) AND "table1"."col6"<>'abc' AND
                  "table1"."col6"<>'def' AND "table1"."col6"<>'ghi')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width