On Mar 19, 4:50 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
It would be great if you could do it (no matter when) - I do not have
the physical resources for that kind of testing right now,
unfortunately, but I am pretty sure it will work.
Received on Mon Mar 19 2007 - 15:58:47 CDT
