Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: Oracle 10G queries dirty data and never returns

    I am trying to do a report off three tables where data gets loaded on the daily basis. Catch is that data is "dirty" (see WHERE clause). Even though I have an index on each of the three tables for the column I do my equijoin against , the query never returns.

    Code:
    SELECT 
        A.SITE || ',' ||
        A.SCRIPT || ',' ||
        A.TESTRESULT || ',' ||
        A.BISTERROR || ',' ||
        A.HDDMANUFACTURER || ',' ||
        A.TESTDATE || ',' ||
        A.STB_MODEL || ',' ||
        A.RECEIVERID || ',' ||
        B.STB_MODEL || ',' ||
        B.STB_MANUFACTURER || ',' ||
        B.STB_MFRDATE || ',' ||
        B.SW_VERSION || ',' ||
        B.SW_NAME || ',' ||
        B.HW_VERSION || ',' ||
        B.RECEIVERID || ',' ||
        C.HDDMODELNUMBER || ',' ||
        C.HDDSERIALNUMBER || ',' ||
        C.DISKPORT || ',' ||
        C.DISKSIZE || ',' ||
        C.SECTORSIZE || ',' ||
        C.POWERONHOURS || ',' ||
        C.CURRENTTEMP || ',' ||
        MAX(TRUNC(A.LOAD_DATE)) 
        FROM 
        HDD_SMARTLOG_FILEINFO A, 
        HDD_SMARTLOG_BOXINFO B, 
        HDD_SMARTLOG_DISKINFO C 
        WHERE 
        A.FILENAME=B.FILENAME 
        AND 
        B.FILENAME=C.FILENAME 
        AND 
        INSTR(TRANSLATE(A.RECEIVERID,'0123456789','XXXXXXXXXX'),'X') != LENGTH(A.RECEIVERID) 
        AND 
        INSTR(TRANSLATE(B.RECEIVERID,'0123456789','XXXXXXXXXX'),'X') != LENGTH(B.RECEIVERID) 
        AND 
        LOWER(C.DISKPORT)='internal' 
        AND 
        A.SITE IS NOT NULL OR A.SITE <> '' 
        AND 
        A.BISTERROR IS NOT NULL OR A.BISTERROR <> '' 
        AND 
        B.STB_MODEL IS NOT NULL OR B.STB_MODEL <> '' 
        AND 
        B.SW_VERSION IS NOT NULL OR B.SW_VERSION <> '' 
        AND 
        C.POWERONHOURS IS NOT NULL OR C.POWERONHOURS <> '' 
        AND 
        C.CURRENTTEMP IS NOT NULL OR C.CURRENTTEMP <> '' 
        AND 
        INSTR(TRANSLATE(C.POWERONHOURS,'0123456789','XXXXXXXXXX'),'X') != LENGTH(C.POWERONHOURS) 
        AND 
        INSTR(TRANSLATE(C.CURRENTTEMP,'0123456789','XXXXXXXXXX'),'X') != LENGTH(C.CURRENTTEMP) 
        GROUP BY 
        A.SITE, 
        A.SCRIPT, 
        A.TESTRESULT, 
        A.BISTERROR, 
        A.HDDMANUFACTURER, 
        A.TESTDATE, 
        A.STB_MODEL, 
        A.RECEIVERID, 
        B.STB_MODEL, 
        B.STB_MANUFACTURER, 
        B.STB_MFRDATE, 
        B.SW_VERSION, 
        B.SW_NAME, 
        B.HW_VERSION, 
        B.RECEIVERID, 
        C.HDDMODELNUMBER, 
        C.HDDSERIALNUMBER, 
        C.DISKPORT, 
        C.DISKSIZE, 
        C.SECTORSIZE, 
        C.POWERONHOURS, 
        C.CURRENTTEMP;
    It used to work about a month ago when we had less than 100K records on a daily basis. Now, we are processing +/- 300K daily.

    How can I optimize this query. My boss wants it to be run daily. Can you please provide a few pointers?

    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    all the NOT NULL forces Full Table Scan
    Code:
    SELECT a.site
           || ','
           || a.script
           || ','
           || a.testresult
           || ','
           || a.bisterror
           || ','
           || a.hddmanufacturer
           || ','
           || a.testdate
           || ','
           || a.stb_model
           || ','
           || a.receiverid
           || ','
           || b.stb_model
           || ','
           || b.stb_manufacturer
           || ','
           || b.stb_mfrdate
           || ','
           || b.sw_version
           || ','
           || b.sw_name
           || ','
           || b.hw_version
           || ','
           || b.receiverid
           || ','
           || c.hddmodelnumber
           || ','
           || c.hddserialnumber
           || ','
           || c.diskport
           || ','
           || c.disksize
           || ','
           || c.sectorsize
           || ','
           || c.poweronhours
           || ','
           || c.currenttemp
           || ','
           || MAX(Trunc(a.load_date))
    FROM   hdd_smartlog_fileinfo a,
           hdd_smartlog_boxinfo b,
           hdd_smartlog_diskinfo c
    WHERE  a.filename = b.filename
           AND b.filename = c.filename
           AND Instr(Translate(a.receiverid, '0123456789', 'XXXXXXXXXX'), 'X') !=
               Length(a.receiverid)
           AND Instr(Translate(b.receiverid, '0123456789', 'XXXXXXXXXX'), 'X') !=
               Length(b.receiverid)
           AND Lower(c.diskport) = 'internal'
           AND a.site IS NOT NULL
            OR a.site <> ''
               AND a.bisterror IS NOT NULL
            OR a.bisterror <> ''
               AND b.stb_model IS NOT NULL
            OR b.stb_model <> ''
               AND b.sw_version IS NOT NULL
            OR b.sw_version <> ''
               AND c.poweronhours IS NOT NULL
            OR c.poweronhours <> ''
               AND c.currenttemp IS NOT NULL
            OR c.currenttemp <> ''
               AND Instr(Translate(c.poweronhours, '0123456789', 'XXXXXXXXXX'), 'X')
                   !=
                   Length(c.poweronhours)
               AND Instr(Translate(c.currenttemp, '0123456789', 'XXXXXXXXXX'), 'X')
                   !=
                   Length(c.currenttemp)
    GROUP  BY a.site,
              a.script,
              a.testresult,
              a.bisterror,
              a.hddmanufacturer,
              a.testdate,
              a.stb_model,
              a.receiverid,
              b.stb_model,
              b.stb_manufacturer,
              b.stb_mfrdate,
              b.sw_version,
              b.sw_name,
              b.hw_version,
              b.receiverid,
              c.hddmodelnumber,
              c.hddserialnumber,
              c.diskport,
              c.disksize,
              c.sectorsize,
              c.poweronhours,
              c.currenttemp;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18
    Removed NOT NULL check from WHERE clause. Queried completed under 15 minutes.

    Thanks!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXPLAIN PLAN
    and
    ALTER SESSION SET SQL_TRACE=TRUE

    each will provide visibility into where wall clock time is actually being spent.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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