OraFAQ Forum: Performance Tuning » STOPKEY & Index Use

Home » RDBMS Server » Performance Tuning » STOPKEY & Index Use (10.2.0.4.0 - 64bi, Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

STOPKEY & Index Use [message #481807]

Sat, 06 November 2010 23:47

annagel
Messages: 220
Registered: April 2006

Senior Member

I am working on a query for a feedback response system which is going to be targeted at the common case when the user only want the most recent 10-20 rows in the feedback table. My though is to create an index on the date column, do a sort in an inner query and rownum <= in an outer query. This works as I expect when I am only querying the main table (lookup by index with a stop key), but when I start joining the main table to attribute tables I end up with a full table scan of the main table with the stop key applied after all the joins are completed, the index is nowhere to be found.

CREATE TABLE attr1_tbl(attr1_id NUMBER NOT NULL, attr1 VARCHAR2(10) NOT NULL,
    CONSTRAINT attr1_pk PRIMARY KEY (attr1_id));
CREATE TABLE attr2_tbl(attr2_id NUMBER NOT NULL, attr2 VARCHAR2(10) NOT NULL,
    CONSTRAINT attr2_pk PRIMARY KEY (attr2_id));
CREATE TABLE attr3_tbl(attr3_id NUMBER NOT NULL, attr3 VARCHAR2(10) NOT NULL,
    CONSTRAINT attr3_pk PRIMARY KEY (attr3_id));

CREATE TABLE main_rec
(rec_id NUMBER NOT NULL,
rec_date DATE NOT NULL,
attr1_id NUMBER NOT NULL,
attr2_id NUMBER NOT NULL,
attr3_id NUMBER NOT NULL,
CONSTRAINT main_rec_pk PRIMARY KEY (rec_id),
CONSTRAINT attr1_fk FOREIGN KEY (attr1_id) REFERENCES attr1_tbl(attr1_id),
CONSTRAINT attr2_fk FOREIGN KEY (attr2_id) REFERENCES attr2_tbl(attr2_id),
CONSTRAINT attr3_fk FOREIGN KEY (attr3_id) REFERENCES attr3_tbl(attr3_id)
);

CREATE INDEX main_rec_dte_idx ON main_rec(rec_date);

INSERT INTO attr1_tbl
   SELECT     ROWNUM, DBMS_RANDOM.STRING ('u', 10)
         FROM DUAL
   CONNECT BY LEVEL <= 10;

INSERT INTO attr2_tbl
   SELECT     ROWNUM, DBMS_RANDOM.STRING ('u', 10)
         FROM DUAL
   CONNECT BY LEVEL <= 10;

INSERT INTO attr3_tbl
   SELECT     ROWNUM, DBMS_RANDOM.STRING ('u', 10)
         FROM DUAL
   CONNECT BY LEVEL <= 10;


INSERT INTO main_rec
   SELECT     ROWNUM, SYSDATE - DBMS_RANDOM.VALUE (1, 1000),
              ROUND (DBMS_RANDOM.VALUE (1, 10)),
              ROUND (DBMS_RANDOM.VALUE (1, 10)),
              ROUND (DBMS_RANDOM.VALUE (1, 10))
         FROM DUAL
   CONNECT BY LEVEL <= 4000;

BEGIN
   DBMS_STATS.gather_table_stats ('<schema>', 'attr1_tbl');
   DBMS_STATS.gather_table_stats ('<schema>', 'attr2_tbl');
   DBMS_STATS.gather_table_stats ('<schema>', 'attr3_tbl');
   DBMS_STATS.gather_table_stats ('<schema>', 'main_rec');
END;

--index is not used
SELECT *
  FROM (SELECT   /*+cardinality(mr 1000000000)*/
                 mr.rec_id, mr.rec_date, a1.attr1, a2.attr2, a3.attr3
            FROM main_rec mr, attr1_tbl a1, attr2_tbl a2, attr3_tbl a3
           WHERE mr.attr1_id = a1.attr1_id
             AND mr.attr2_id = a2.attr2_id
             AND mr.attr3_id = a3.attr3_id
        ORDER BY mr.rec_date)
 WHERE ROWNUM <= 5;

--even with just one attribute table no index
SELECT *
  FROM (SELECT   /*+cardinality(mr 1000000000)*/
                 mr.rec_id, mr.rec_date, a1.attr1
            FROM main_rec mr, attr1_tbl a1
           WHERE mr.attr1_id = a1.attr1_id
        ORDER BY mr.rec_date)
 WHERE ROWNUM <= 5;

--when only the main table is accessed the index is used.
SELECT *
  FROM (SELECT   /*+cardinality(mr 1000000000)*/
                 mr.*
            FROM main_rec mr
        ORDER BY mr.rec_date)
 WHERE ROWNUM <= 5;

One thing I noticed was that when no data is selected from the attribute tables, even if they are joined in the query, the CBO throws them out of the plan and only accesses the main table. With the foreign keys this makes sense and really just disqualified my first thought that maybe I was missing a foreign key or not null constraint somewhere.

I also added the cardinality hint to overcome the chance that in my test case there was so little data that index access is not worth it.