sql tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sql tuning

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    94

    Question

    I'm trying to tune the following query which is doing a full table scan.(generated through java)
    Issuepricedate has 967 distinct values
    validflag has 2 distinct values
    pricefeedsource has 1 distinct value
    symbol has 424 distinct values.
    any ideas, I've thought about using partitioning but I'd rather use any index at the mo....

    select COUNT(*)
    from (SELECT A.PRICEHISTID
    , A.ISSUEID
    , A.SYMBOL
    , A.BIDPRICE
    , A.ASKPRICE
    , A.CREATIONPRICE
    , A.CANCELLATIONPRICE
    , A.ISSUEPRICEDATE
    , A.DIVIDENDSTATUS
    , A.PRICEFEEDSOURCE
    , A.SOURCEVENDOR
    , A.VALIDFLAG
    , A.AUTHORITATIVEFLAG
    , A.CREATEID
    , A.CREATEDATE
    , A.MODIFIEDID
    , A.MODIFIEDDATE from PRICEHIST A
    where (UPPER(A.SYMBOL) like UPPER('GEUKT')
    and (A.ISSUEPRICEDATE >= TO_DATE('May 09 2000 00 00','MON DD YYYY HH24 MI')
    and A.ISSUEPRICEDATE <= TO_DATE('May 09 2001 23 59','MON DD YYYY HH24 MI'))
    and UPPER(A.PRICEFEEDSOURCE) like UPPER('IS_QUOTED'))
    and VALIDFLAG = '1' )

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 2 0.03 0.03 0 0 0 0
    Execute 2 0.00 0.00 0 0 0 0
    Fetch 4 0.89 0.92 859 2378 8 2
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 8 0.92 0.95 859 2378 8 2

    Misses in library cache during parse: 2
    Optimizer goal: CHOOSE
    Parsing user id: 49 (JAVELIN)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 SORT AGGREGATE
    246 TABLE ACCESS FULL PRICEHIST


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    1 SORT (AGGREGATE)
    246 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PRICEHIST'

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Create an index on ISSUEPRICEDATE
    Analysis table and Index.

  3. #3
    Join Date
    Feb 2001
    Posts
    123
    One thing about the SQL puzzles me - why use the 'like' operator if you are comparing the column with a literal?

    Surely you could use
    where (UPPER(A.SYMBOL) = UPPER('GEUKT') instead?

    Likewise for the PRICEFEEDSOURCE column.

    HTH

    David.

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