Re: Help with sql query speed. Explain plan included

On Mar 19, 3:48 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com> wrote:
> On Mar 19, 2:45 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > Plan still using the original index.
>
> > DROP INDEX T1_IDXE;
>
> > select tx.residentid /*+ INDEX(t0 T1_INDEX1) INDEX(tx T1_INDEX2) */
> > from t1 t0,t1 tx
> > where t0.residentid!=tx.residentid
> > and t0.questionnumber=tx.questionnumber
> > and t0.answer=tx.answer
> > and t0.residentid=1486674
> > group by tx.residentid
> > having count(*)>10;
>
> > Plan Table
> > ============
> > ---------------------------------------------------
> > +-----------------------------------+
> > | Id | Operation | Name | Rows | Bytes |
> > Cost | Time |
> > ---------------------------------------------------
> > +-----------------------------------+
> > | 0 | SELECT STATEMENT | | | |
> > 218K | |
> > | 1 | FILTER | | |
> > | | |
> > | 2 | SORT GROUP BY | | 102K | 2245K |
> > 218K | 00:57:47 |
> > | 3 | HASH JOIN | | 2041K | 44M |
> > 210K | 00:55:45 |
> > | 4 | TABLE ACCESS BY INDEX ROWID | T1 | 76 | 836
> > | 80 | 00:00:02 |
> > | 5 | INDEX RANGE SCAN | T1_INDEX1| 76 |
> > | 4 | 00:00:01 |
> > | 6 | TABLE ACCESS FULL | T1 | 216M | 2376M |
> > 208K | 00:54:13 |
> > ---------------------------------------------------
> > +-----------------------------------+
> > Predicate Information:
> > ----------------------
> > 1 - filter(COUNT(*)>10)
> > 3 - access("T0"."QUESTIONNUMBER"="TX"."QUESTIONNUMBER" AND
> > "T0"."ANSWER"="TX"."ANSWER")
> > 3 - filter("T0"."RESIDENTID"<>"TX"."RESIDENTID")
> > 5 - access("T0"."RESIDENTID"=1486674)
> > 6 - filter("TX"."RESIDENTID"<>1486674)
>
> > Try the other query that was suggested:
> > ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST101';
> > SELECT residentId, count(1)
> > FROM t1
> > WHERE ( questionNumber, answer ) IN ( SELECT questionNumber, answer
> > FROM t1
> > WHERE residentId = 1486674 )
> > GROUP BY residentId
> > HAVING COUNT(1) > 10;
>
> > SELECT
> > *
> > FROM
> > TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
>
> > Plan Table
> > ============
> > ---------------------------------------------------
> > +-----------------------------------+
> > | Id | Operation | Name | Rows | Bytes |
> > Cost | Time |
> > ---------------------------------------------------
> > +-----------------------------------+
> > | 0 | SELECT STATEMENT | | | |
> > 208K | |
> > | 1 | FILTER | | |
> > | | |
> > | 2 | SORT GROUP BY | | 1384 | 30K |
> > 208K | 00:54:12 |
> > | 3 | HASH JOIN RIGHT SEMI | | 27K | 595K |
> > 208K | 00:54:12 |
> > | 4 | TABLE ACCESS BY INDEX ROWID | T1 | 76 | 836
> > | 80 | 00:00:02 |
> > | 5 | INDEX RANGE SCAN | T1_INDEX1| 76 |
> > | 4 | 00:00:01 |
> > | 6 | TABLE ACCESS FULL | T1 | 216M | 2376M |
> > 206K | 00:54:40 |
> > ---------------------------------------------------
> > +-----------------------------------+
> > Predicate Information:
> > ----------------------
> > 1 - filter(COUNT(*)>10)
> > 3 - access("QUESTIONNUMBER"="QUESTIONNUMBER" AND "ANSWER"="ANSWER")
> > 5 - access("RESIDENTID"=1486674)
>
> > For some reason, this query returned one row where none were returned
> > before.
>
> > At this point, I am not sure what the solution may be. Your results
> > may vary on other platforms.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> Charles,
>
> I think that "residentId" should be included in the index, as the last
> column: "questionNumber" + "answer" + "residentId" so that the table
> read is no longer required. Another index should be created if it's
> not already in place on "residentId" + "questionNumber" + "answer".
> Can you try this on your sandbox and let us know what the result is?

It takes a couple hours to add an index to a table with ~ 226,407,657 rows, and then to analyze that index. I will take a look at that possibility in a couple hours.

SELECT
  BLOCKS
FROM
  DBA_TABLES
WHERE
  TABLE_NAME='T1'; 551,931

SELECT
  INDEX_NAME,
  LEAF_BLOCKS
FROM
  DBA_INDEXES
WHERE
  INDEX_NAME LIKE 'T1'; T1_INDEX1 516,467 (RESIDENTID only)
T2_INDEX2 347,797 (QUESTIONNUMBER,ANSWER composite, now using key compression)

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Mar 19 2007 - 15:50:41 CDT