Home » RDBMS Server » Performance Tuning » STOPKEY & Index Use (10.2.0.4.0 - 64bi, Windows 2003)
STOPKEY & Index Use [message #481807] |
Sat, 06 November 2010 23:47 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
|
Re: STOPKEY & Index Use [message #481842 is a reply to message #481840] |
Sun, 07 November 2010 10:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
BlackSwan,
The original poster provided a complete reproducible test case, including enough rows to show the problem. I have provided a run of the code below, plus what I suggested, plus what you asked for.
-- test environment provided by original poster:
SCOTT@orcl_11gR2> CREATE TABLE attr1_tbl(attr1_id NUMBER NOT NULL, attr1 VARCHAR2(10) NOT NULL,
2 CONSTRAINT attr1_pk PRIMARY KEY (attr1_id));
Table created.
SCOTT@orcl_11gR2> CREATE TABLE attr2_tbl(attr2_id NUMBER NOT NULL, attr2 VARCHAR2(10) NOT NULL,
2 CONSTRAINT attr2_pk PRIMARY KEY (attr2_id));
Table created.
SCOTT@orcl_11gR2> CREATE TABLE attr3_tbl(attr3_id NUMBER NOT NULL, attr3 VARCHAR2(10) NOT NULL,
2 CONSTRAINT attr3_pk PRIMARY KEY (attr3_id));
Table created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> CREATE TABLE main_rec
2 (rec_id NUMBER NOT NULL,
3 rec_date DATE NOT NULL,
4 attr1_id NUMBER NOT NULL,
5 attr2_id NUMBER NOT NULL,
6 attr3_id NUMBER NOT NULL,
7 CONSTRAINT main_rec_pk PRIMARY KEY (rec_id),
8 CONSTRAINT attr1_fk FOREIGN KEY (attr1_id) REFERENCES attr1_tbl(attr1_id),
9 CONSTRAINT attr2_fk FOREIGN KEY (attr2_id) REFERENCES attr2_tbl(attr2_id),
10 CONSTRAINT attr3_fk FOREIGN KEY (attr3_id) REFERENCES attr3_tbl(attr3_id)
11 );
Table created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> CREATE INDEX main_rec_dte_idx ON main_rec(rec_date);
Index created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO attr1_tbl
2 SELECT ROWNUM, DBMS_RANDOM.STRING ('u', 10)
3 FROM DUAL
4 CONNECT BY LEVEL <= 10;
10 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO attr2_tbl
2 SELECT ROWNUM, DBMS_RANDOM.STRING ('u', 10)
3 FROM DUAL
4 CONNECT BY LEVEL <= 10;
10 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO attr3_tbl
2 SELECT ROWNUM, DBMS_RANDOM.STRING ('u', 10)
3 FROM DUAL
4 CONNECT BY LEVEL <= 10;
10 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO main_rec
2 SELECT ROWNUM, SYSDATE - DBMS_RANDOM.VALUE (1, 1000),
3 ROUND (DBMS_RANDOM.VALUE (1, 10)),
4 ROUND (DBMS_RANDOM.VALUE (1, 10)),
5 ROUND (DBMS_RANDOM.VALUE (1, 10))
6 FROM DUAL
7 CONNECT BY LEVEL <= 4000;
4000 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> BEGIN
2 DBMS_STATS.gather_table_stats (USER, 'attr1_tbl');
3 DBMS_STATS.gather_table_stats (USER, 'attr2_tbl');
4 DBMS_STATS.gather_table_stats (USER, 'attr3_tbl');
5 DBMS_STATS.gather_table_stats (USER, 'main_rec');
6 END;
7 /
PL/SQL procedure successfully completed.
-- queries showing index usage or lack thereof provided by
originial poster:
SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> --index is not used
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT /*+cardinality(mr 1000000000)*/
3 mr.rec_id, mr.rec_date, a1.attr1, a2.attr2, a3.attr3
4 FROM main_rec mr, attr1_tbl a1, attr2_tbl a2, attr3_tbl a3
5 WHERE mr.attr1_id = a1.attr1_id
6 AND mr.attr2_id = a2.attr2_id
7 AND mr.attr3_id = a3.attr3_id
8 ORDER BY mr.rec_date)
9 WHERE ROWNUM <= 5;
REC_ID REC_DATE ATTR1 ATTR2 ATTR3
---------- --------- ---------- ---------- ----------
2870 12-FEB-08 TTWJUNJOYG NKKIQDEXKF XEAGDLZCUF
2254 12-FEB-08 TZXHTTFJNR WWZIOPSEZY VMTHQFRITK
1857 12-FEB-08 TZXHTTFJNR WWZIOPSEZY TEQNDFKKDB
2603 12-FEB-08 YJHLTJRQMG KEWEJCFRTE DDUWAKNOOE
2939 12-FEB-08 JROWHJDOMH NKKIQDEXKF IPWXCAQXZH
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 744565500
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 215 | | 20M (1)| 68:27:53 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1000M| 40G| | 20M (1)| 68:27:53 |
|* 3 | SORT ORDER BY STOPKEY| | 1000M| 58G| 74G| 20M (1)| 68:27:53 |
|* 4 | HASH JOIN | | 1000M| 58G| | 14241 (99)| 00:02:51 |
| 5 | NESTED LOOPS | | 1000 | 42000 | | 155 (0)| 00:00:02 |
| 6 | NESTED LOOPS | | 100 | 2800 | | 18 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| ATTR1_TBL | 10 | 140 | | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| ATTR2_TBL | 10 | 140 | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | ATTR3_TBL | 10 | 140 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | MAIN_REC | 1000M| 19G| | 7046 (100)| 00:01:25 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
4 - access("MR"."ATTR1_ID"="A1"."ATTR1_ID" AND "MR"."ATTR2_ID"="A2"."ATTR2_ID"
AND "MR"."ATTR3_ID"="A3"."ATTR3_ID")
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> --even with just one attribute table no index
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT /*+cardinality(mr 1000000000)*/
3 mr.rec_id, mr.rec_date, a1.attr1
4 FROM main_rec mr, attr1_tbl a1
5 WHERE mr.attr1_id = a1.attr1_id
6 ORDER BY mr.rec_date)
7 WHERE ROWNUM <= 5;
REC_ID REC_DATE ATTR1
---------- --------- ----------
2870 12-FEB-08 TTWJUNJOYG
2254 12-FEB-08 TZXHTTFJNR
1857 12-FEB-08 TZXHTTFJNR
2603 12-FEB-08 YJHLTJRQMG
2939 12-FEB-08 JROWHJDOMH
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1725215521
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 145 | | 8042K (2)| 26:48:28 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1000M| 27G| | 8042K (2)| 26:48:28 |
|* 3 | SORT ORDER BY STOPKEY| | 1000M| 27G| 37G| 8042K (2)| 26:48:28 |
|* 4 | HASH JOIN | | 1000M| 27G| | 11273 (100)| 00:02:16 |
| 5 | TABLE ACCESS FULL | ATTR1_TBL | 10 | 140 | | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | MAIN_REC | 1000M| 13G| | 4230 (100)| 00:00:51 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
4 - access("MR"."ATTR1_ID"="A1"."ATTR1_ID")
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> --when only the main table is accessed the index is used.
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT /*+cardinality(mr 1000000000)*/
3 mr.*
4 FROM main_rec mr
5 ORDER BY mr.rec_date)
6 WHERE ROWNUM <= 5;
REC_ID REC_DATE ATTR1_ID ATTR2_ID ATTR3_ID
---------- --------- ---------- ---------- ----------
2870 12-FEB-08 8 1 4
2254 12-FEB-08 3 4 5
1857 12-FEB-08 3 4 6
2603 12-FEB-08 5 9 7
2939 12-FEB-08 6 1 3
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3979030507
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 305 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1000M| 56G| 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| MAIN_REC | 1000M| 19G| 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | MAIN_REC_DTE_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
SCOTT@orcl_11gR2> SET AUTOTRACE OFF
-- my suggestion using different index (requires new stats) with original query:
SCOTT@orcl_11gR2> DROP INDEX main_rec_dte_idx
2 /
Index dropped.
SCOTT@orcl_11gR2> CREATE INDEX main_rec_dte_idx ON main_rec(attr1_id, attr2_id, attr3_id, rec_date)
2 /
Index created.
SCOTT@orcl_11gR2> BEGIN
2 DBMS_STATS.gather_table_stats (USER, 'attr1_tbl');
3 DBMS_STATS.gather_table_stats (USER, 'attr2_tbl');
4 DBMS_STATS.gather_table_stats (USER, 'attr3_tbl');
5 DBMS_STATS.gather_table_stats (USER, 'main_rec');
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT /*+cardinality(mr 1000000000)*/
3 mr.rec_id, mr.rec_date, a1.attr1, a2.attr2, a3.attr3
4 FROM main_rec mr, attr1_tbl a1, attr2_tbl a2, attr3_tbl a3
5 WHERE mr.attr1_id = a1.attr1_id
6 AND mr.attr2_id = a2.attr2_id
7 AND mr.attr3_id = a3.attr3_id
8 ORDER BY mr.rec_date)
9 WHERE ROWNUM <= 5
10 /
REC_ID REC_DATE ATTR1 ATTR2 ATTR3
---------- --------- ---------- ---------- ----------
2870 12-FEB-08 TTWJUNJOYG NKKIQDEXKF XEAGDLZCUF
2254 12-FEB-08 TZXHTTFJNR WWZIOPSEZY VMTHQFRITK
1857 12-FEB-08 TZXHTTFJNR WWZIOPSEZY TEQNDFKKDB
2603 12-FEB-08 YJHLTJRQMG KEWEJCFRTE DDUWAKNOOE
2939 12-FEB-08 JROWHJDOMH NKKIQDEXKF IPWXCAQXZH
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 884254505
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 215 | | 20M (1)| 68:26:04 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1000M| 40G| | 20M (1)| 68:26:04 |
|* 3 | SORT ORDER BY STOPKEY | | 1000M| 58G| 74G| 20M (1)| 68:26:04 |
| 4 | NESTED LOOPS | | | | | | |
| 5 | NESTED LOOPS | | 1000M| 58G| | 5158 (1)| 00:01:02 |
| 6 | NESTED LOOPS | | 1000 | 42000 | | 155 (0)| 00:00:02 |
| 7 | NESTED LOOPS | | 100 | 2800 | | 18 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | ATTR1_TBL | 10 | 140 | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | ATTR2_TBL | 10 | 140 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | ATTR3_TBL | 10 | 140 | | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | MAIN_REC_DTE_IDX | 4 | | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| MAIN_REC | 1000K| 20M| | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
11 - access("MR"."ATTR1_ID"="A1"."ATTR1_ID" AND "MR"."ATTR2_ID"="A2"."ATTR2_ID" AND
"MR"."ATTR3_ID"="A3"."ATTR3_ID")
SCOTT@orcl_11gR2> SET AUTOTRACE OFF
-- queries requested by BlackSwan:
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM attr1_tbl a1;
COUNT(*)
----------
10
1 row selected.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM main_rec mr;
COUNT(*)
----------
4000
1 row selected.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM main_rec mr
2 WHERE mr.attr1_id in (select a1.attr1_id from attr1_tbl a1);
COUNT(*)
----------
4000
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
Re: STOPKEY & Index Use [message #481844 is a reply to message #481807] |
Sun, 07 November 2010 14:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Barbara,
I tried adding the index you suggested and after re-analyzing the tables I am still not seeing the results you get, I also tried creating an index with the date leading and the three attribute columns in positions 2, 3, 4 again with no change. Anything else you might have done to get that explain plan?
BlackSwan,
Yes when every row is returned certainly the best option if all the columns are not in the index is a FTS, but if only the most resent (by some indexed column) 5 rows are being returned a FTS is decidedly not the most efficient.
One thing I did try is re-writing the query as follows:
SELECT mr.rec_id, mr.rec_date, a1.attr1, a2.attr2, a3.attr3
FROM (SELECT *
FROM (SELECT /*+cardinality(mr 1000000000)*/
mr.*
FROM main_rec mr
ORDER BY mr.rec_date)
WHERE ROWNUM <= 5) 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;
This produced the execution plan I want. From this I am guessing that the CBO-at least the 10.2 version of the CBO-can't definitively say that each row in the main table will be joined to exactly one row in each of the attribute tables, the NOT NULL, FOREIGN KEY and PRIMARY KEY contraints on the attribute tables and columns should be enough to make this determination unless I am missing something.
I can rewrite my query to execute this way for the "normal" case and use an alternate query for a sort by some other column, it is a bit more code but it will work, I am still curious about what Barbara is doing that I am not, and also why the basic set-up doesn't accomplish it as it still seems to me that it should.
[Updated on: Sun, 07 November 2010 14:20] Report message to a moderator
|
|
|
|
|
Re: STOPKEY & Index Use [message #481847 is a reply to message #481845] |
Sun, 07 November 2010 15:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You might see what you get with row_number.
SCOTT@orcl_11gR2> CREATE INDEX main_rec_dte_idx2 ON main_rec(attr1_id, attr2_id, attr3_id, rec_date, rec_id)
2 /
Index created.
SCOTT@orcl_11gR2> BEGIN
2 DBMS_STATS.gather_table_stats (USER, 'attr1_tbl');
3 DBMS_STATS.gather_table_stats (USER, 'attr2_tbl');
4 DBMS_STATS.gather_table_stats (USER, 'attr3_tbl');
5 DBMS_STATS.gather_table_stats (USER, 'main_rec');
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT /*+cardinality(mr 1000000000)*/
3 mr.rec_id, mr.rec_date, a1.attr1, a2.attr2, a3.attr3,
4 ROW_NUMBER () OVER (ORDER BY mr.rec_date) rn
5 FROM main_rec mr, attr1_tbl a1, attr2_tbl a2, attr3_tbl a3
6 WHERE mr.attr1_id = a1.attr1_id
7 AND mr.attr2_id = a2.attr2_id
8 AND mr.attr3_id = a3.attr3_id)
9 WHERE rn <= 5
10 /
REC_ID REC_DATE ATTR1 ATTR2 ATTR3 RN
---------- --------- ---------- ---------- ---------- ----------
956 11-FEB-08 EZUJEOHKIQ VOGDLBMTXS VWXFRCPAVB 1
500 12-FEB-08 HYPTEEMKBS VJZSUWETOG VWXFRCPAVB 2
3969 12-FEB-08 EZUJEOHKIQ EGXSHIHYUQ VYTFNPWKZG 3
2519 12-FEB-08 JPINHMMXEN VKHATMSWDS EJGEJPACWN 4
119 12-FEB-08 HYPTEEMKBS EMUFGJZOJQ USTFYWZWHO 5
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2113504627
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000M| 52G| | 20M (1)| 68:25:16 |
|* 1 | VIEW | | 1000M| 52G| | 20M (1)| 68:25:16 |
|* 2 | WINDOW SORT PUSHED RANK| | 1000M| 58G| 74G| 20M (1)| 68:25:16 |
| 3 | NESTED LOOPS | | 1000M| 58G| | 1156 (1)| 00:00:14 |
| 4 | MERGE JOIN CARTESIAN | | 1000 | 42000 | | 155 (0)| 00:00:02 |
| 5 | MERGE JOIN CARTESIAN| | 100 | 2800 | | 18 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | ATTR1_TBL | 10 | 140 | | 3 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 10 | 140 | | 15 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | ATTR2_TBL | 10 | 140 | | 2 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 10 | 140 | | 154 (0)| 00:00:02 |
| 10 | TABLE ACCESS FULL | ATTR3_TBL | 10 | 140 | | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | MAIN_REC_DTE_IDX2 | 1000K| 20M| | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "MR"."REC_DATE")<=5)
11 - access("MR"."ATTR1_ID"="A1"."ATTR1_ID" AND "MR"."ATTR2_ID"="A2"."ATTR2_ID" AND
"MR"."ATTR3_ID"="A3"."ATTR3_ID")
SCOTT@orcl_11gR2> SET AUTOTRACE OFF
|
|
|
|
Re: STOPKEY & Index Use [message #481849 is a reply to message #481848] |
Sun, 07 November 2010 16:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
In the following, I used your original indexes and query. All I changed was the hints.
SCOTT@orcl_11gR2> CREATE TABLE attr1_tbl(attr1_id NUMBER NOT NULL, attr1 VARCHAR2(10) NOT NULL,
2 CONSTRAINT attr1_pk PRIMARY KEY (attr1_id));
Table created.
SCOTT@orcl_11gR2> CREATE TABLE attr2_tbl(attr2_id NUMBER NOT NULL, attr2 VARCHAR2(10) NOT NULL,
2 CONSTRAINT attr2_pk PRIMARY KEY (attr2_id));
Table created.
SCOTT@orcl_11gR2> CREATE TABLE attr3_tbl(attr3_id NUMBER NOT NULL, attr3 VARCHAR2(10) NOT NULL,
2 CONSTRAINT attr3_pk PRIMARY KEY (attr3_id));
Table created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> CREATE TABLE main_rec
2 (rec_id NUMBER NOT NULL,
3 rec_date DATE NOT NULL,
4 attr1_id NUMBER NOT NULL,
5 attr2_id NUMBER NOT NULL,
6 attr3_id NUMBER NOT NULL,
7 CONSTRAINT main_rec_pk PRIMARY KEY (rec_id),
8 CONSTRAINT attr1_fk FOREIGN KEY (attr1_id) REFERENCES attr1_tbl(attr1_id),
9 CONSTRAINT attr2_fk FOREIGN KEY (attr2_id) REFERENCES attr2_tbl(attr2_id),
10 CONSTRAINT attr3_fk FOREIGN KEY (attr3_id) REFERENCES attr3_tbl(attr3_id)
11 );
Table created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> CREATE INDEX main_rec_dte_idx ON main_rec(rec_date);
Index created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO attr1_tbl
2 SELECT ROWNUM, DBMS_RANDOM.STRING ('u', 10)
3 FROM DUAL
4 CONNECT BY LEVEL <= 10;
10 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO attr2_tbl
2 SELECT ROWNUM, DBMS_RANDOM.STRING ('u', 10)
3 FROM DUAL
4 CONNECT BY LEVEL <= 10;
10 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO attr3_tbl
2 SELECT ROWNUM, DBMS_RANDOM.STRING ('u', 10)
3 FROM DUAL
4 CONNECT BY LEVEL <= 10;
10 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> INSERT INTO main_rec
2 SELECT ROWNUM, SYSDATE - DBMS_RANDOM.VALUE (1, 1000),
3 ROUND (DBMS_RANDOM.VALUE (1, 10)),
4 ROUND (DBMS_RANDOM.VALUE (1, 10)),
5 ROUND (DBMS_RANDOM.VALUE (1, 10))
6 FROM DUAL
7 CONNECT BY LEVEL <= 4000;
4000 rows created.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> BEGIN
2 DBMS_STATS.gather_table_stats (USER, 'attr1_tbl');
3 DBMS_STATS.gather_table_stats (USER, 'attr2_tbl');
4 DBMS_STATS.gather_table_stats (USER, 'attr3_tbl');
5 DBMS_STATS.gather_table_stats (USER, 'main_rec');
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT /*+cardinality(mr 1000000000) ordered use_nl (mr a1 a2 a3)*/
3 mr.rec_id, mr.rec_date, a1.attr1, a2.attr2, a3.attr3
4 FROM main_rec mr, attr1_tbl a1, attr2_tbl a2, attr3_tbl a3
5 WHERE mr.attr1_id = a1.attr1_id
6 AND mr.attr2_id = a2.attr2_id
7 AND mr.attr3_id = a3.attr3_id
8 ORDER BY mr.rec_date)
9 WHERE ROWNUM <= 5;
REC_ID REC_DATE ATTR1 ATTR2 ATTR3
---------- --------- ---------- ---------- ----------
413 12-FEB-08 ZXDIGNQXVK WQIFQSFEBC EFDCJXNXHR
3013 12-FEB-08 FMVQFKWIUZ SXXAECHKKX PXWXXPCYWJ
1743 12-FEB-08 IHJZOOSZWS XRXFQJGKVZ YTZTEQAGNH
1696 12-FEB-08 TUXHGMKVJA SXXAECHKKX QNOBYSWVNQ
2796 12-FEB-08 SWUBNMSATY WQIFQSFEBC EFDCJXNXHR
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2243423532
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 215 | 3001M (1)|999:59:59 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1000M| 40G| 3001M (1)|999:59:59 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1000M| 58G| 3001M (1)|999:59:59 |
| 5 | NESTED LOOPS | | 1000M| 45G| 2001M (1)|999:59:59 |
| 6 | NESTED LOOPS | | 1000M| 32G| 1000M (1)|999:59:59 |
| 7 | TABLE ACCESS BY INDEX ROWID| MAIN_REC | 1000M| 19G| 3 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | MAIN_REC_DTE_IDX | 1 | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| ATTR1_TBL | 1 | 14 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | ATTR1_PK | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | ATTR2_TBL | 1 | 14 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | ATTR2_PK | 1 | | 0 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | ATTR3_PK | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | ATTR3_TBL | 1 | 14 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
10 - access("MR"."ATTR1_ID"="A1"."ATTR1_ID")
12 - access("MR"."ATTR2_ID"="A2"."ATTR2_ID")
13 - access("MR"."ATTR3_ID"="A3"."ATTR3_ID")
SCOTT@orcl_11gR2>
[Updated on: Sun, 07 November 2010 16:46] Report message to a moderator
|
|
|
Re: STOPKEY & Index Use [message #481853 is a reply to message #481849] |
Sun, 07 November 2010 17:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Thanks for all your help on this question Barbara. These hints are forcing a good execution path and I think looking at the explain plan it is lending some more weight to the idea that Oracle thinks the joins might affect the number of rows returned in some way. In the plan the STOPKEY is applied only after the main table has been joined to each of the attribute tables. Since the hints pushed the query into using nested loops, using the index still makes sense, but the CBO is still not getting that 5 rows from the main table will always translate to 5 rows once the attribute tables have been joined.
|
|
|
Barking up the wrong tree [message #481971 is a reply to message #481807] |
Mon, 08 November 2010 14:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
So I ended up posting my question to Ask Tom and turns out I was barking up the wrong tree in terms of why it was executing the way it was. It was a stats issue kind of, I was setting the cardinality really high, but stats on the table still showed the whole size as small, so the number of rows was irrelevant.
So using the set_table_stats method I can get a more "realistic" fake example:
NOTE: I also threw out attribute 2 and 3 tables, they were just cluttering things up and in no way altered the query.
BEGIN
DBMS_STATS.set_table_stats (USER,
'main_rec',
numrows => 4000,
numblks => 4000
);
END;
Now my basic query with no hints gives a good execution plan
SELECT *
FROM (SELECT 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;
Now my question is why didn't my real query behave like my example since it is actually a big table and doesn't need fake stats to make it so. I think I have discovered a reason at least though not yet a why or a work around. My actual query is dynamic code that allows for some filtering, I wanted a way of dynamically changing my where clause while still being able to us a single using statement, so I passed all my parameters into the query as a sub-select from dual. Then if I needed them in the where I would add a line dynamically, if not then they shouldn't effect the execution plan the way "NVL(param, colVal) = colval" would and I still get to use binds. It looks like this access of data from DUAL is what is causing my issue:
--the set stats statement from my first code section
--should be run before this
SELECT *
FROM (SELECT mr.rec_id, mr.rec_date, a1.attr1, param1
FROM main_rec mr,
attr1_tbl a1,
(SELECT :p1 param1
FROM DUAL)
WHERE mr.attr1_id = a1.attr1_id
ORDER BY mr.rec_date)
WHERE ROWNUM <= 5;
If I drop the DUAL I get the plan I want, if I leave it in I don't. Any ideas on why?
|
|
|
Re: Barking up the wrong tree [message #481972 is a reply to message #481971] |
Mon, 08 November 2010 14:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
As a workaround adding the hint 'leading(mr)' to the query will get the plan I want...still don't know why it is needed though.
SELECT *
FROM (SELECT /*+leading(mr)*/
mr.rec_id, mr.rec_date, a1.attr1, param1
FROM main_rec mr,
attr1_tbl a1,
(SELECT :p1 param1
FROM DUAL)
WHERE mr.attr1_id = a1.attr1_id
ORDER BY mr.rec_date)
WHERE ROWNUM <= 5;
|
|
|
Re: Barking up the wrong tree [message #481973 is a reply to message #481972] |
Mon, 08 November 2010 15:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Alternatively, with no hints I can throw the parameters into my attribute table as follows and now the leading hint is not required;
SELECT *
FROM (SELECT mr.rec_id, mr.rec_date, a1.attr1, param1
FROM main_rec mr,
(SELECT :p1 param1, a1.*
FROM attr1_tbl a1) a1
WHERE mr.attr1_id = a1.attr1_id
ORDER BY mr.rec_date)
WHERE ROWNUM <= 5;
still no idea why the dual option does not work, but this is a pretty good alternative as it places everything in the hands of the CBO.
|
|
|
Re: Barking up the wrong tree [message #482771 is a reply to message #481973] |
Mon, 15 November 2010 13:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
I ended up posting the question on AskTom and a second alternative is to supply the binds in something having the form
when you don't want to use the value and
when you do.
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:26:50 CDT 2024
|